[SOLVED] error in mysql syntax, take a look at my syntax is it wrong?

[dsaba]

i'm running this query, I'm unsure from which query the error is calling from but all the queries are structured pretty much the same

<?php
$updatequery = mysql_query("UPDATE `dictionary` SET `english`='$sourceline', `he_flagged`='Yes', `es_flagged`='Yes', `pt_flagged`='Yes', `pl_flagged`='Yes' LIMIT $i, 1") or die(mysql_error());

$updatequery = mysql_query("UPDATE `dictionary` SET `english`='$sourceline', `he_flagged`='Yes', `es_flagged`='Yes', `pt_flagged`='Yes', `pl_flagged`='Yes' LIMIT $i, 1") or die(mysql_error());
?>

 

$i will be looping from 0 to 299

 

this is the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 1' at line 1

 

How do I fix it?

 

-thanks

[MadTechie]

On update the LIMIT clause places a limit on the number of rows that can be updated.

 

You can use LIMIT row_count to restrict the scope of the UPDATE. A LIMIT clause is a rows-matched restriction. The statement stops as soon as it has found row_count rows that satisfy the WHERE clause, whether or not they actually were changed.

 

 

On SELECT

The LIMIT clause can be used to constrain the number of rows returned by the SELECT  statement. LIMIT takes one or two numeric arguments, which must both be non-negative integer constants (except when using prepared statements).

 

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

 

EDIT:

to sum up, Update can only  have 1 number

[dsaba]

here's my full code with context of my query:

<?php

$i = -1;
while ($queryrow = mysql_fetch_array($query))    {
$i++;
$oldenglish = $queryrow['english'];
$sourceline = trim($fileArray[$i]);

if ($oldenglish != $sourceline) {
$updatequery = mysql_query("UPDATE `dictionary` SET `english`='$sourceline', `he_flagged`='Yes', `es_flagged`='Yes', `pt_flagged`='Yes', `pl_flagged`='Yes' LIMIT $i, 1") or die(mysql_error());

?>

 

I was trying to update only a certain row which i specify, now I see that this cannot be done

 

But what if I added a WHERE clause like this:

$updatequery = mysql_query("UPDATE `dictionary` SET `english`='$sourceline', `he_flagged`='Yes', `es_flagged`='Yes', `pt_flagged`='Yes', `pl_flagged`='Yes' WHERE fieldname='value' LIMIT $i, 1") or die(mysql_error());

 

then can I do that? I really need to update by certain rows, can anyone tell me how to do this?

 

-thank you

[MadTechie]

Nope can't do that..

 

 

do you have a Unique ID ?

if not create a autonumber field called ID or UID (whatever)

 

when you do a find also grab the ID, then when you update use WHERE ID = $id

$id being the one you pulled!

[dsaba]

i'm fully aware of the unique id advantages, but i'm in a situation where the entries in my database must correlate to the line count in a .txt file

 

i was doing it with ids, but sometimes i delete rows in my database and therefore the ids are off, so i want to do it by row#

 

not being able to update a record by a particular row seems very odd, for such capabilities in mysql, you can use LIMIT to select a single row with SELECT , why not with UPDATE

 

that just seems odd, there must be a way...  any ideas????

[MadTechie]

what about

<?php
$updatequery = mysql_query("UPDATE `dictionary` SET `english`='$sourceline', `he_flagged`='Yes', `es_flagged`='Yes', `pt_flagged`='Yes', `pl_flagged`='Yes' WHERE ID = (SELECT ID FROM `dictionary` WHERE fieldname='value' LIMIT $i, 1)") or die(mysql_error());

?>

 

 

May wanna backup the database first, thats 101% untested

[dsaba]

i'm continually learning new things

...interesting

 

i'll try it out, (backup first )

[dsaba]

I ran this in sql in phpmyadmin:

UPDATE `dictionary` SET `english`='hello', `he_flagged`='Yes', `es_flagged`='Yes', `pt_flagged`='Yes', `pl_flagged`='Yes' WHERE ID = (SELECT ID FROM `dictionary` LIMIT 6, 1)

 

it said this:

Error

SQL query:

 

UPDATE `dictionary` SET `english` = 'hello',

`he_flagged` = 'Yes',

`es_flagged` = 'Yes',

`pt_flagged` = 'Yes',

`pl_flagged` = 'Yes' WHERE ID = ( SELECT ID

FROM `dictionary`

LIMIT 6 , 1 )

MySQL said: 

 

#1093 - You can't specify target table 'dictionary' for update in FROM clause

 

what does that mean?

[MadTechie]

Humm, basically you can run an update on a table you have used a select for in the same statement.. what verions of MySQL are you using?

 

 

[dsaba]

4

[MadTechie]

not sure if you can do it in 5.. erm..

 

what the script do again

 

whats the input and whats the expected result

[dsaba]

<?php
//update and/or add new words to english from source
$query = mysql_query("SELECT * FROM dictionary") or die(mysql_error());
$totalentries = mysql_num_rows($query);
$something = $totalentries - 1;
echo 'There are <b>'.$totalentries.'</b> entries in the database<br>';

$fileArray = file('dictionary/source.txt') or die("cannot make array out of file lines");
$totalsource = count($fileArray);
$difference = $totalsource - $totalentries;
echo 'There are <b>'.$totalsource.'</b> entries in the source<br>';
echo 'There are <b>'.$difference.'</b> NEW entries in the source<br><br>';
echo 'This means that rows <b>0-'.$something.'</b> are going to be UPDATED in the database<br>';
echo 'and <b>'.$difference.'</b> entries are going to be INSERTED in the database<br><br>';

if ($totalsource > $totalentries)					{
//update and add entries
//update first
$i = -1;
while ($queryrow = mysql_fetch_array($query))	{
$i++;
$rownum = $i + 1;
$oldenglish = $queryrow['english'];
$sourceline = trim($fileArray[$i]);

if ($oldenglish != $sourceline) {
$updatequery = mysql_query("UPDATE `dictionary` SET `english`='$sourceline', `he_flagged`='Yes', `es_flagged`='Yes', `pt_flagged`='Yes', `pl_flagged`='Yes' LIMIT $i, 1") or die(mysql_error());
if ($updatequery == "1") {
echo '<b>'.$oldenglish.'</b> <em>replaced with</em> <b>'.$sourceline.'</b><br>';
} else {
echo 'failure nothing replaced<br>';
}

}
				    //end of while statement
											}
?>

 

the brackets look better here:

http://phpfi.com/229752

[MadTechie]

i got to shoot home so will look at it then their 2hours

might be work looking at this mySQL Merge

 

Might be useful

[dsaba]

thanks a lot for the help, but I was originally looking for a simple solution, turns out its getting more and more complicated

 

i have thought of a simple solution though if i want to correlate lines from a .txt file and my table

 

i will have to use ids, but instead of deleting the rows if i want to remove an entry in the table, i will just have a fieldname named 'status' which will have active or inactive

 

i will just mark it inactive, and not query it, so that way the ids will still correlate

 

I appreciate you trying