Syntax for UPDATE of a record in MYSQL

Question

Is the following update query a legal statement? It replaces the existing value with an empty value instead of the word gossamer. It does not fail as far as I can tell. It changes the value in the database from whatever it was before to empty.

 $sqld = "UPDATE mynotes SET notes = 'GOSSAMER' WHERE id = '2039'";
 $resupdate = mysql_query($sqld) or die(mysql_error()); 
 if ($resupdate) {
    $success=1;
    $message .="success with update";
 }

The query is part of an an API and it returns a result in JSON. While this makes debugging more time consuming, this should be besides the point. If the above is an entirely legal update statement, then at least I can rule out a syntax issue and search for the problem elsewhere.

I have verified that the above code does work in a standalone php file. Something else in code is causing the issue.

Yes, mysql is deprecated in favor of mysqli and PDO. But upgrading legacy site is not in job scope.

You should use `mysqli_` instead of `mysql_` as it's been deprecated. — Edward, Mar 19 '18 at 22:04
Have you tried updating the field in the DB manually? It could be something with the field configuration. If so, doing that should spit out an error. — Samsquanch, Mar 19 '18 at 22:07
I ran the query in the sql query window and it works fine there. The message reports success with update in the JSON but the field value is getting changed to empty instead of GOSSAMER. I also tried putting the table name in ticks — user6631314, Mar 19 '18 at 22:10
I put it after the query and the query still does the same thing. Changes any value to empty. — user6631314, Mar 19 '18 at 22:18
Did you update to a version of PHP >= PHP7? The `mysql_` api has been removed as of PHP7.0 never to be seen again — RiggsFolly, Mar 19 '18 at 23:37
That should be done but this is a legacy site running earlier version of php — user6631314, Mar 20 '18 at 00:47

score 1 · Accepted Answer · answered Mar 20 '18 at 13:41

It replaces the existing value with an empty value instead of the word gossamer

Assuming this statement is accurate then either:

1) the attribute 'notes' is of type ENUM whose values do not include 'Gossamer'. But you didn't share the DDL for the table.

2) Your code is not executing the query you've shown us here - the query it is executing should be in your MySQL logs

Syntax for UPDATE of a record in MYSQL

1 Answers1