CodeIgniter Forums
Inserting post data into joining table, any help! - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Inserting post data into joining table, any help! (/thread-53751.html)



Inserting post data into joining table, any help! - El Forum - 08-07-2012

[eluser]ppwalks[/eluser]
I have a form which allows the user to create products and add them to category, the categories are linked through a joining table and many products can belong to many categories.

I have extracted the post data into an array, but what I'm struggling with is updating the database with multiple entries pertaining to one product_id ie..

product_id = 1 -> category_id = 4
product_id = 1 -> category_id = 6
product_id = 1 -> category_id = 7
product_id = 1 -> category_id = 34

My logic is to run a foreach loop into the database but don't know how, can someone point me in the right direction please!


Inserting post data into joining table, any help! - El Forum - 08-07-2012

[eluser]jakelehner[/eluser]
Looping in the DB is likely not the best approach. Build the insert logic in to your PHP then send to the DB.

You can either do a single query:

Code:
INSERT INTO prod_cat
('product_id', 'category_id')
VALUES
(1,4),
(1,6),
(1,7),
(1,34)

That will insert 4 records in one query. I'm not sure how that would work with Active Record.

Alternatively, wrap multiple DB calls in a transaction so you can roll back if something fails.


Inserting post data into joining table, any help! - El Forum - 08-08-2012

[eluser]ppwalks[/eluser]
yes my question is how to prepare the data for insert, I have array implode which formats them into single string can I drop that straight into the query