CodeIgniter Forums

Full Version: Inserting post data into joining table, any help!
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[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!

El Forum

[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.

El Forum

[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