CODEWITHSUNDEEP
phpmysqldatabase
Justin Erswell
Justin Erswell

Reputation: 708

PHP Form Product and Categories

I Have a form to add a product into a database table called 'Products' on this form is also a ul with checkboxes for categories, a product can be in multiple categories and therefore when the form is submitted I need to update the product table and the product_categories table.

The Product table will hold the product ID and the product_categories table will hold the product ID only with the category ID therefore creating a One to Many relationship.

However this is where I am stumped, I can get the product written in fine but I am unsure as to the syntax to write to the product_categories table as well.

Can anyone help me out.

There is no code yet as I don't know what to write!

Thanks

Justin

Upvotes: 2

Views: 1651

Answers (2)

rizalp1
rizalp1

Reputation: 6554

If I understand your question correctly, I think you will need to loop through the categories, and for each category, run an insert script to add that particular category-product combination into the product_category table.

-something like this:

$product = prodId;

foreach ($categoryIds as $categoryId) {

  $query = "insert into ".$categoryTable." values ('".$product."', '".$categoryId."')";

    // Run $query
}

Upvotes: 1

calumbrodie
calumbrodie

Reputation: 4792

You need to write two SQL statements. After the first SQL statement you need the ID of the product you have just inserted - you can do this as follows.

$q = "INSERT INTO product (`name`) VALUES ('my cool thing')";
$r = mysql_query($q);

$productId = mysql_insert_id ();

$q = "INSERT INTO product_category (`product_id`, `category_id`) VALUES ({$productId},  {$categoryId})";
$r = mysql_query($q);

Note: This is a pretty naive way of handling this, but it will get you started. You may want to look into using an MVC framework and PDO or ORM'S (or another form of database abstraction) etc to make your life easier.

Upvotes: 2

Related Questions