Insert into two tables with ClientID

Questions › Insert into two tables with ClientID

Gary Brett asked 9 years ago

Hello all, I recall seeing a demo relating to inserting data into two tables but cannot find it now?

I have Clients & Orders tables with a grid that displays fields from both. When I add a new record from grid I need it to add the clientID to the Orders table but as the ClientID does not exist until record is created in Clients it keeps throwing an error?

Im sure this is possible as I saw the demo once I think and the demo database table invheader stores the client_ID 😉

Thank you

11 Answers

0 Vote Up Vote Down

Abu Ghufran answered 9 years ago

For this case, you will need to connect on_insert event handler.
Insert in 2 Tables Code: http://hastebin.com/etayarigac.php

To test run, you can save this code in demos/misc/test.php and run with demo app.

0 Vote Up Vote Down

Gary Brett answered 9 years ago

Ahh thats great thank you Abu should make things much easier.. Can I ask using the demo it only inserts the client name into clients so I added company to the query and field list to insert but still doesn't add, is the below correct?

I changed

// first insert in clients table
mysql_query("insert into clients (name, gender,company) values ('{$data[params][name]}','','')");

// first insert in clients table

mysql_query("insert into clients (name, gender,company)
values ('{$data[params][name]}',
'{$data[params][company]}')");

Thank you

0 Vote Up Vote Down

Abu Ghufran answered 9 years ago

There could be 2 issues.

1st, number of fields and number of values passed must be same.
2nd, company data is not passed while inserting. There is no column of company in my demo.

0 Vote Up Vote Down

Gary Brett answered 9 years ago

Hi, I added company field in mysql and into field list [cols] it displays in grid fine so I edited the above code to include company on insert but no joy, just wondered if that was correct or I needed to edit elsewhere?

0 Vote Up Vote Down

Abu Ghufran answered 9 years ago

I can't tell unless you share the code.
You can use hastebin.com or pastebin.com to share.

0 Vote Up Vote Down

Gary Brett answered 9 years ago

Hi, I worked it out now so no worries, if it helps anyone going forward here is the finished code from demo that inserts into 2 tables and allows update of clients table

Added company col line 37
Added company to query line 115
Added update line 135
Added values to insert line 154

http://hastebin.com/nolewaxabo.php

Thank you

0 Vote Up Vote Down

Gary Brett answered 9 years ago

Hi again, my mistake, the update function I added to allow me to edit the grid duplicates the row, I hadn't noticed as I was using the same info for each record.. Is the code on line 135 inserting rather than updating?

Thank you

0 Vote Up Vote Down

Gary Brett answered 9 years ago

Changed code on hastebin http://hastebin.com/suqevahuyu.php, this now doesnt duplicate but also doesnt update, cant see where I am going wrong..

0 Vote Up Vote Down

Abu Ghufran answered 9 years ago

https://phpgrid.desk.com/customer/portal/questions/11560708-two-tables-update-function

0 Vote Up Vote Down