UPDATE statement acting crazy

[marvelous_mogli]

Hi,

There are a few UPDATE statements which are not executing or executing erronously with mysql_query().

When tried using SQLYog or other client applications the queries works fine.

 

Environment 1:

MySQL: 4.1.9-max

PHP: 4.3.10

Client API version:  3.23.49

 

Environment 2:

MySQL: 4.1.21

PHP: 4.4.4

Client API version:  4.1.21

 

 

Query 1:

update order_master set order_status = '2', order_state_date_change = now() where order_id = 18

 

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 '' at line 1

 

 

Query 2:

Trying to increment a field by 1 but it increments by 3

[marvelous_mogli]

Can someone help?

[soycharliente]

What's the full query line in your code?

[marvelous_mogli]

$SQL = "update order_master set order_status = '2', order_state_date_change = now() where order_id = 18";

$rs_update = mysql_query($SQL);

[soycharliente]

Not a SQL guru, but try a combination of:

- putting single quotes around 18

- using AND instead of ,

[marvelous_mogli]

The same query works in SQLYog.

[Illusion]

post the full error message u r getting.

[marvelous_mogli]

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 '' at line 1

[Illusion]

so u have error in the starting itself, why don't u try with all the keywords and built in functions with capital letters, its a very good practice.I don't really writing UPDATE or Update as 'update' might cause a issue.

[marvelous_mogli]

It's an SQL-2000 complaint query :-)

I suspect there should be some issue with the MySQL Client libraries.

[Iceman512]

Hi!

 

Try this:

<?php
$sql = ("UPDATE order_master SET order_status='2', order_state_date_change=now() WHERE order_id='18'");
$rs_update = mysql_query($sql);
?>

 

By the way, (I'm sure you already know this) if the auto-increment is set in the database, you don't have to specify it in this query, it will increment by 1 each time a new row of data is added to the database. So if order_status is your incrementing field, you don't have to specify it here.

 

That '2' might be the reason it increases by 3 instead of 1 as you said originally.

 

Hope it helps,

Regards,

Iceman