Issues inserting data on another table

[danielp]

I have a calendar application and the table that keeps all events is named as ProjCalend

I want to have a function what will make a copy of all recent added events added to the calendar into another table, named ProjCalendHistory that is a perfect clone of ProjCalend.

Then, I went to onAfterInsert event into the calendar and added the following functions:

sc_exec_sql(INSERT INTO ProjCalendHistory VALUES (id,title,description,event_color,category,start_date,end_date,start_time,end_time,calend_all_day,creator,creation_date_time))

sc_exec_sql(SELECT id,title,description,event_color,category,start_date,end_date,start_time,end_time,calend_all_day,creator,creation_date_time FROM ProjCalend)

sc_exec_sql(WHERE id=(SELECT max(id) FROM ProjCalend))

However, as I run the calendar application I get the following error:

Parse error : syntax error, unexpected ‘INTO’ (T_STRING), expecting ‘)’ in /opt/NetMake/v9-php73/wwwroot/scriptcase/…

I tried a lot of things, but had no success.

Any suggestion is welcome.

Thanks.

Daniel.

[bdl]

Hi Daniel. Try putting quotes around your SQL statements:

sc_exec_sql(“INSERT INTO ProjCalendHistory VALUES (id,title,description,event_color,category,start_date,end_date,start_time,end_time,calend_all_day ,creator,creation_date_time)”);

[nwdbs]

I do this:
$sql=" sql here";
sc_exec_sql($sql);

[danielp]

Hello. I tried yours and bdl’s suggestions and entered the code below into the event “on
AfterInsert” of my calendar application. When I try to add a new event into the calendar, right after typing all event details and pressing ADD, the window in which I was typing the event details became white and the whole calendar went to frozen state. As a result, no event was added neither to the calendar nor to the ProjCalendHistory grid.

I am completely lost on this issue.

$sql1=“INSERT INTO ProjCalendHistory VALUES (id,title,description,event_color,category,start_date,end_date,start_time,end_time,calend_all_day,creator,creation_date_time)”;

sc_exec_sql($sql1);

$sql2=“SELECT id,title,description,event_color,category,start_date,end_date,start_time,end_time,calend_all_day,creator,creation_date_time FROM ProjCalend”;

sc_exec_sql($sql2);

$sql3=“WHERE id=(SELECT max(id) FROM ProjCalend)”;

sc_exec_sql($sql3);

[nwdbs]

That sql will not work the way it is written.
I would go back to basics and learn how to do a proper SQL insert

[gbillot3]

First test $sql1 in your database sql query if it works

Try this:
$sql1=“INSERT INTO ProjCalendHistory VALUES (id,title,description,event_color,category,start_date,end_date,start_time,end_time,calend_all_day,creator,creation_date_time) SELECT id,title,description,event_color,category,start_date,end_date,start_time,end_time,calend_all_day,creator,creation_date_time FROM ProjCalend order by id desc limit 1 ”;
sc_exec_sql($sql1);

[danielp]

Dear nwdbs,

The concept of a forum is experience exchange and assistance to those who need. I’ve spent hours implementing routines in C, assembly, LabVIEW, microcontrollers programming and etc over the last years and I am not the kind who ask for everything without trying exhaustively first.

So, taking your words as a template, I would recommend you to go back to the basics of kindness and cooperativism, steping out of your podium of knowledge.

[nwdbs]

You are correct. I apologize.

[danielp]

Thanks a lot. Now I have everything working well.

[danielp]

gbillot3

The code was working properly but couple days ago I tried to implement a very simple version of it on another project. I use a form to add data to “table1” and everytime a new record is added, or updated, I want to make a copy of the record to “table2”. Then, on events “onAfterInsert” and “onAfterUpdate” of “table1” I added the following code:

$test=“INSERT INTO table2 VALUES (id,name,age) SELECT id,name,age FROM table1 ORDER BY id desc limit 1”;

sc_exec_sql($test);

The type of fields are all the same in SQL.

After adding the record to “table1”, when this script runs I get a blank screen with no messages and data is not added to neither “table1” nor “table2”.

Any clue on where the issue is?

Thanks,

Daniel.

[gbillot3]

The $test code of the example it’s ok. Send your real code

try using sc_commit_trans(); before $test="INSERT…

[danielp]

gbillot3

This is my real code. I just want to have the “name” and “age” data to be duplicated (go to table1 and table2 at the same time) every time a new record is added into table1.

I tried your suggestion and tested the code below into the event “onAfterInsert” of a form that add data to table1.

sc_commit_trans();

$test=“INSERT INTO table2 VALUES (id,name,age) SELECT id,name,age FROM table1 ORDER BY id desc limit 1”;

sc_exec_sql($test);

Following your suggestion (sc_commit_trans();), data was added to table1 this time, but no data was added to table2 and a blank screen was shown after data was added.

I tried to recreate the tables into the database, using UTF-8_bin in both tables, but the problem was not yet solved.

Find bellow information regarding fields of table 1:

Find bellow information regarding fields of table 2:

I can’t figure out the problem.

Thanks,

Daniel.

[robydago]

Turn on debugging and script error in the app setting.
It should then show you what’s going wrong instead of a blank screen

[gbillot3]

if id is auto numeric try this

$test=“INSERT INTO table2 VALUES (name,age) SELECT name,age FROM table1 ORDER BY id desc limit 1”;

[danielp]

robydago and gbillot3

I tried the code suggested by gbillot3:

sc_commit_trans();

$test=“INSERT INTO table2 VALUES (name,age) SELECT name,age FROM table1 ORDER BY id desc limit 1”;

sc_exec_sql($test);

and I got the following error message with debug mode on:

(pdo-mysql): INSERT INTO table2 VALUES (name,age) SELECT name,age FROM table1 ORDER BY id desc limit 1

1064: 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 ‘SELECT name,age FROM table1 ORDER BY id desc limit 1’ at line 1

Seems like a syntax error. By database is working great for all other functions of my application.

Thanks,

Daniel.

[gbillot3]

The correct sentence is without VALUES. The values are in the select

$test=“INSERT INTO table2 (name,age) SELECT name,age FROM table1 ORDER BY id desc limit 1”;