update another table

Scriptcase 9 General
#1

[SIZE=13px] [h=2][/h]
[/SIZE]
[SIZE=13px] Am trying to update another table with this code below…

/**

  • Update a record on another table
    */

// SQL statement parameters
$update_table = ‘advance’; // Table name
$update_where = “advance_id = ‘{advance_id}’”; // Where clause
$update_fields = array( // Field list, add as many as needed
“amount_advanced = {advance_balance}”,
//“field_2 = ‘new_value_field_2’”,
);

// Update record
$update_sql = ‘UPDATE ’ . $update_table
. ’ SET ’ . implode(’, ', $update_fields)
. ’ WHERE ’ . $update_where;
sc_exec_sql($update_sql);

But I get this error below when I this code in OnAfterInsert area:

Error while accessing the database:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘WHERE advance_id = ‘3’’ at line 1
[/SIZE]

#2

Go to application section of your menu and set sql errors debug to on. Then every sql statement will be displayed in full on each access. That will make it easy to see what goes wrong in your saql statement. In general I don’t like the upper code although I’m aware that SC shows this as a snippet. For easy statements like this I would use:

$ sql = “update mytable set myfield=’{myfield}’ where mykey=’{mykey}’”’; construction. It’s less code and better readable.