0

I have PHP form, which is read from multiple tables. After editing it (adding new rows or just update) I want to INSERT INTO ... UPDATE both of them.

For now I found solution HERE based on temporary tables, but is that a good approach and practice in 2020? I want to avoid stored procedures, because it's easier to maintain one application instead of two of them (just php, not php & mysql).

I use mysqli in my project and created custom DB class.

My two tables are:

table_tasks (`task_id`, `task_name`)
table_tasks_assigned_users (`task_id`, `user_id`)

When I add new task the process logic is:

Step 1. Insert into update in table_tasks and get task_id

Step 2. Insert into update in table_tasks_assigned_users with given task_id

Krukosz
  • 31
  • 5
  • I think the best practice would be left join –  Jan 19 '20 at 12:43
  • Sorry, but I don't quite get what you want. Do you mean `INSERT INTO ... ON DUPLICATE KEY UPDATE ...`? What is the data you are inserting? What are the keys in that case? How many rows? – Paul Spiegel Jan 19 '20 at 12:49
  • Yes, i mean ´INSERT INTO ... ON DUPLICATE KEY UPDATE´. First INSERT is to insert task name and then basing on task_id i INSERT assigned users to that task in different table. Row number depends on the form. User can insert only one row or make bulk insert of even 50 rows. – Krukosz Jan 19 '20 at 13:19
  • 50 rows isn't too much for a loop in a single transaction with prepared statements. Insert one task per loop iteration and assign the users using `LAST_INSERT_ID()`. – Paul Spiegel Jan 19 '20 at 13:37

0 Answers0