[SOLVED] Getting syntax error

[almightyegg]

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

 

my code is simply this:

$upfarm = mysql_query("UPDATE land SET type = 'Farm' WHERE userid = '{$mem['id']}' and type = 'none' LIMIT 0, $farm") or die(mysql_error());

 

in this case $farm = 1

 

Is there a problem with using the LIMIT in updates?

[mafia13]

try this am a noob at php btw

 

$upfarm = mysql_query("UPDATE land SET type = 'Farm' WHERE userid = '{$mem['id']}' and type = 'none' LIMIT 0, $farm") or die(mysql_error();

 

 

[revraz]

There is no reason to use LIMIT with UPDATE, since you are specifiing which row to be updated.

[premiso]

Remove the 0, that is only needed if you want a range or a starting point (hence only used with select not update).

 

$upfarm = mysql_query("UPDATE land SET type = 'Farm' WHERE userid = '{$mem['id']}' and type = 'none' LIMIT $farm") or die(mysql_error());

 

And mafia created a syntax error, so do not use that one.

 

There is no reason to use LIMIT with UPDATE, since you are specifiing which row to be updated.

 

I would disagree. If you only want to update 1 record and know 1 record should be updated it is good to specify so. This prevents some mis-haps from happening.

[revraz]

Then set your WHERE clause to be more unique.  How do you know which row you are updating?  If you have 3 rows with the same data, then the database is not correct to start with.

[almightyegg]

Problem is, I have 4 updates like this:

if($farm > 0){
$upfarm = mysql_query("UPDATE land SET type = 'Farm' WHERE userid = '{$mem['id']}' and type = 'none' LIMIT 0, $farm") or die(mysql_error());
}
if($water > 0){
$upwater = mysql_query("UPDATE land SET type = 'Water' WHERE userid = '{$mem['id']}' and type = 'none' LIMIT $farm, $water") or die(mysql_error());
}
if($train > 0){
$uptrain = mysql_query("UPDATE land SET type = 'Train' WHERE userid = '{$mem['id']}' and type = 'none' LIMIT $three, $train") or die(mysql_error());
}
if($casino > 0){
$upcasino = mysql_query("UPDATE land SET type = 'Casino' WHERE userid = '{$mem['id']}' and type = 'none' LIMIT $four, $casino") or die(mysql_error());
}

 

I need to start from certain rows so I don't rewrite over the rows I've onlky just updated

[Mchl]

The rows you update no longer have type = 'none' so further queries won't update them...

[almightyegg]

How foolish of me :S I feel like a right n00b now! Ha

 

Cheers

[soak]

I just wrote out a lengthy way to work around this and realised that you don't need to at all.

 

When you perform update number 2 the `type` field for update number 1 will already have been updated from 'none' to 'Farm' so it won't be possible to overwrite your new rows.