cannot update table.

Question

I have 3 tables:

1. Vehicle_record
2. Insurance
3. Roadtax

While I'm succesful in updating the vehicle_record table, the other two tables cannot be updated.

When I run the query, the following error message appears:

1064 - 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 'WHERE regno='TAC 2123'' at line 1

Answer 1

From the error message, I suspect you have the key with date type a string. You should escape all values befor put it into the query.

I suggest you to try AdoDB library. This library is multi-database and will help you to easily do insert and update. Example of update using AdoDB:

$regno = $adodb->qstr('TAC 2123');
$data = array(
 'col1' => 'val1',
 'col2' => 'val2',
 'col3' => 'val3'
);
$result = $adodb->AutoExecute($tablename, $data, 'UPDATE', "regno=$regno");

By escaping the value before put it into a query, you can prevent the SQL injection attact. Doing it using AdoDB Autoexecute, then the escape of values is doing automatically by AdoDB.

For the key with type string, you must do it manually. If the key is an integer, using intval is preferable than $adodb->qstr().

Answer 2

In order for us to help you here, you need to provide the full text of the SQL statement that is getting sent to the server. Paste it as a part of your question.