This topic is locked
[SOLVED]

 Update multiple fields in After Add event

1/30/2011 9:02:57 AM
PHPRunner General questions
B
btman author

Hi,

I have a table in which minutes and seconds are added to total time using the concat SQL statement. It works with the SQL tool. But this only updates the required fields in the frontend. It doesn't update the database.
I've tried the query in PHPmyadmin and it does the job:

UPDATE evaluation SET dist_total=concat(dist_minutes,'.',dist_seconds);

UPDATE evaluation SET plank_total=concat(plank_minutes,'.',plank_seconds);

UPDATE evaluation SET squat_total=concat(squat_minutes,'.',squat_seconds)
I have added following in the After Record Added event:

global $conn;

$strSQLUpdate = "UPDATE evaluation SET dist_total=concat(dist_minutes,'.',dist_seconds);

UPDATE evaluation SET plank_total=concat(plank_minutes,'.',plank_seconds);

UPDATE evaluation SET squat_total=concat(squat_minutes,'.',squat_seconds)";

db_exec($strSQLUpdate,$conn);
But when adding a record an SQL error is displayed:

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 '; UPDATE evaluation SET plank_total=concat(plank_minutes,'.',plank_seconds); UPD' at line 1
I have tried to add double quotes at the beginning and end of each update, but that only updates the last update statement.
Hopefully you will be able to point me into the right direction. It is probably something simple, but I have only limited SQL/PHP knowledge.
Thanks for your help.

Ben

B
btman author 1/30/2011



Hi,

I have a table in which minutes and seconds are added to total time using the concat SQL statement. It works with the SQL tool. But this only updates the required fields in the frontend. It doesn't update the database.
I've tried the query in PHPmyadmin and it does the job:

UPDATE evaluation SET dist_total=concat(dist_minutes,'.',dist_seconds);

UPDATE evaluation SET plank_total=concat(plank_minutes,'.',plank_seconds);

UPDATE evaluation SET squat_total=concat(squat_minutes,'.',squat_seconds)
I have added following in the After Record Added event:

global $conn;

$strSQLUpdate = "UPDATE evaluation SET dist_total=concat(dist_minutes,'.',dist_seconds);

UPDATE evaluation SET plank_total=concat(plank_minutes,'.',plank_seconds);

UPDATE evaluation SET squat_total=concat(squat_minutes,'.',squat_seconds)";

db_exec($strSQLUpdate,$conn);
But when adding a record an SQL error is displayed:
I have tried to add double quotes at the beginning and end of each update, but that only updates the last update statement.
Hopefully you will be able to point me into the right direction. It is probably something simple, but I have only limited SQL/PHP knowledge.
Thanks for your help.

Ben


I found it. Correct code is:
[color="#008000"]global $conn;

$strSQLUpdate = "UPDATE evaluation SET dist_total=concat(dist_minutes,'.',dist_seconds),

plank_total=concat(plank_minutes,'.',plank_seconds),

squat_total=concat(squat_minutes,'.',squat_seconds)";

db_exec($strSQLUpdate,$conn);