PHP SQL updating product in multiple category

Question

I want to add product in multiple categories in php. After lot of search/research on this I am able to add products in multiple categories but its not working while updating/changing product categories on update page .

MY DB structure is as:

PRODUCTTABLE
 _____________________________________________
|    pid    |      pname   | price | pdetails |
-----------+---------------+-------+----------+

CATEGORYTABLE
 ______________________________________
|    id    |      catname   | catslug |
-----------+---------------+----------+

PRODUCT2CAT
 ____________________________
|  product_id   |   cat_id  |
-----------+----------------+

Code used on ADD PRODUCT page:(only relevent to question)

<form method="post">
...........
echo "<input type=\"checkbox\" name=\"pcateg[]\" value=\"$catid\" /> $catnm<br />";
...........

if(isset($_POST["submit"])) {
.........
.............
$pc2=$_POST["pcateg"];

$query1 = "insert into producttable(pname,price,image,pdetails) values('$pn','$p','$img','$pd')";
mysql_query($query1);
$prdid = mysql_insert_id();



foreach($pc2 as $key=>$values)
{
$query2 = "INSERT INTO product2cat(product_id,cat_id) VALUES('$prdid','$values') ";
$result2 = mysql_query($query2);
}

}

When I Add Product, code is working well and insert selected categories ID in PRODUCT2CAT table.

Code used on UPDATE PRODUCT Page is:

<form method="post">
...........
    while($ans=mysql_fetch_array($cresult))
    {
        $selected = "";
        $catId = $ans['id'];
        $catNm = $ans['catname'];

        if ($catId == $categ) {
        $selected = "checked";
        }

    echo "<input type=\"checkbox\" name=\"pcateg[]\" value=\"$catId\" $selected /> $catNm<br />";
    }
........
    if(isset($_POST["submit"])) {
    .........
    .............
    $query1="UPDATE producttable SET pname='$pn', price='$p', image='$img', pdetails='$pd' WHERE pid=$prid";
    mysql_query($query1);

    foreach($pc2 as $key=>$values)
    {
    $query2 = "UPDATE product2cat SET cat_id='$values' WHERE product_id=$prid";
    $result2 = mysql_query($query2);
    }
  }

Update $query2 is not working.
Or I have to use 2 queries 1st to delete old records and then insert new record. How to use query in such situation so as to del/update selected categories on product update.

I also want to display old categories as preselected checkbox on update page. $selected = "checked" It works on single category But how to do this in multiple check.

I'm not seeing the $pc2 query

Samuel Cook
– Samuel Cook

2012-11-24 18:27:13 +00:00
Commented Nov 24, 2012 at 18:27 — Samuel Cook
– Samuel Cook, Commented Nov 24, 2012 at 18:27

BenMorel · Accepted Answer · 2014-05-21 22:30:11Z

1

Do not use mysql_* it is deprecated
INSERT INTO table_name ('col1', 'col2') VALUES (1, 2), (2, 3), (3, 4) would insert 3 rows

Something like that might work:

$queryu = "INSERT INTO product2cat(product_id,cat_id) VALUES ";
$insertValues = array();
foreach($pc2 as $key=>$values) {
    $insertValues[]= "('$prid','$values')"; 
} 

$queryu .= implode(',', $insertValues);

edited May 21, 2014 at 22:30

BenMorel

37k52 gold badges208 silver badges339 bronze badges

answered Nov 24, 2012 at 18:33

E_p

3,14419 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vehlad Over a year ago

I tried your insert statement but some problem in COMMA placement in query how to remove COMMA from the last Loop.

$queryu = "INSERT INTO product2cat(product_id,cat_id) VALUES";  	foreach($pc2 as $key=>$values) 	{ 	$queryu .= " ('$prid','$values'), "; 	}

result of this query: INSERT INTO product2cat(product_id,cat_id) VALUES ('21','2'), ('21','5'), ('21','1'), ('21','6'), I want to skip the last COMMA in this query.

Collectives™ on Stack Overflow

PHP SQL updating product in multiple category

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related