No announcement yet.

Update second table after inserting for data into a table

  • Filter
  • Time
  • Show
Clear All
new posts

  • Update second table after inserting for data into a table

    I get the following error when I try to run this code in a OnAfterInsert event.
    I am trying to update a second database table after inserting form data into another database table.
    Can someone tell me what I am doing wrong.

    Error while accessing the database:
    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 ''occupant' = 25 WHERE 'property_id' = 5 AND 'unit_id' = 41' at line 1

    {} are form fields
    'property_id' and 'unit_id' are database fields.

    * Update a record on another table

    // SQL statement parameters
    $update_table = 'units'; // Table name
    $update_where = "'property_id' = {property_id} AND 'unit_id' = {unit_id}"; // Where clause
    $update_fields = array( // Field list, add as many as needed
    " 'occupant' = {client_id}"


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

    Thank you.

  • #2
    Go to your application section and scroll down until you find the debug options. Set sql debug options to on, regenerate and run. Then every sql statement will be printed before execution allowing you to see what sql is generated (and you are able to see where it goes wrong).
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development /