What I am doing wrong?

Scriptcase 9 General
#1

Hi, trying to update another table after saving a record in a form. I am receiving this error:

[TABLE=“width: 0”]
[TR]
[TD=“class: scFormErrorTitleFont”]Error[/TD]
[TD]Close [/TD]
[/TR]
[/TABLE]
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 ‘Date = 2013-02-20 , Encounter = ‘234234234’, Patient Name = ‘Test Any One’ WHERE’ at line 1

The code is as follows in onAfterUpdate:

/**

  • Update a record on another table
    */

// SQL statement parameters
$update_table = ‘patient_history’; // Table name
$update_where = “PatientID = {PatientID}”; // Where clause
$update_fields = array( // Field list, add as many as needed
“Entry Date = {Entry Date}”,
“Encounter = ‘{Encounter}’”,
“Patient Name = ‘{Patient Name}’”,
);

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

Any help? Thanks,

#2

What I useually do is to break down the sql into one statement. This way the statement is more easy to read. If you set the debugoption in application menu then each sql statement is printed so you can see what sql statement is generated and why it’s probabely wrong. I suspect that in your case (if you are using mysql) you have formatted the date wrong, afaik it needs quotes. And entry date with a space in the middle might give trouble. I always advise to use an underscore like entry_date. If you insist on using speces you need ‘backquotes’ to enclose the fieldname.

#3

Hi. One of the first things I would do is to get the sql statement you are creating with your code - the one that is getting created with your syntax and variables - in this case $update_sql, and test it.

This is what I do. Create a log file and dump the $update_sql to it before you actually use it.

Created a suubfolder under your project called /mylog/ - the code below can be inserted into your SC events as many times as you want for logging test info. very handy when you can’t get a clear place to echo or output variables to the screen.

 // log it ---------------------------------------------------------------------
      $file = '../mylog/testlog.txt';
      $when = date("d/m/y : H:i:s", time());
      $entry= "Starting Log Entry".$when." -- update_sql=".$update_sql."

"; // change this line to show any text or variables you want
file_put_contents($file, $entry, FILE_APPEND | LOCK_EX);
// end log ---------------------------------------------------------------------

I use this and find it helps isolate when it is a simple SQL problem, like a syntax or maybe you need to put field_name or ‘some_value’ or some other problem.

Run your SC app and then cut and paste the SQL into phpmyadmin’s select box, and it should get the result you are expecting. If not, it is usually easy to debug.

Good luck,
Jamie

#4

Many thanks for help!