Updating Company_Id In Cscart_Discussion Table

General General Questions
1

With regard to comments and reviews...

I have a large number of products imported into my store. The comments and reviews are not automatically active and I have to set each product to allow reviews. I am using the following code:

// get all the active product_id's from the cscart_products table
$query = "SELECT product_id FROM cscart_products WHERE status='A'";
$productsResult = mysql_query($query);
$numRows = mysql_numrows($productsResult);
echo "Processing $numRows product id's...\n";
while ($row = mysql_fetch_assoc($productsResult)) {
    $cscart_product_id = $row['product_id'];
    // see if there is a row already for this cscart_product_id
    $query="select object_id from cscart_discussion where object_id=$cscart_product_id AND object_type = 'P'";
    $result=mysql_query($query);
    // if not, add a row to cscart_discussion
    if (mysql_num_rows($result) == 0) {
        echo "Inserting record into cscart_discussion for $cscart_product_id\n";
        $query="INSERT INTO cscart_discussion (object_id, object_type, type) VALUES ($cscart_product_id, 'P', 'B')";
        $result=mysql_query($query);
        if (!$result)
           echo "Query failed: ($query): " . mysql_error(). "\n";
}
}
echo "All Done";

?>

This code worked fine in my old V2.x.x cart, but in V4 there is an additional field called "company_id". This field is defaulting to '0' and I need it to default or change to '1'.

I changed the $query to:

$query="INSERT INTO cscart_discussion (object_id, object_type, type, company_id) VALUES ($cscart_product_id, 'P', 'B', '1')";

But this only updates new rows. How do I change existing rows company_id from '0' to '1'?

Thanks in advance for any help you may provide.

2 
UPDATE `cscart_discussion` SET `company_id` = '1';
3

Try this query:

$query="UPDATE cscart_discussion SET company_id = 1 WHERE company_id = 0";

or

db_query("UPDATE ?:discussion SET company_id = ?i WHERE company_id = ?i", 1, 0);
4

Thank you both, but where in the script would I insert this code?

5

If you just want to change existing rows, you don't need to add it to the script. Just run the query directly.

6

I would rather not have to manually run the query in the MySQL database every time a product is added. I have my products imported from distributor(s), but it isn't setting the company id on the discussions table. Thus, I was trying to figure out where to add that query in the script to automate the process.

My thought was somewhere in the loop where it would act only on the row that its looping through. I tried putting it above the while loop to update the entire table, but it looks like it either didn't work, or only worked on about 2500 of the 18000 products I have.

7

Run the query before the following line of code:

echo “All Done”;
e.g.
$update_query=“UPDATE cscart_discussion SET company_id = 1 WHERE company_id = 0”;

$result=mysql_query($update_query);

echo “All Done”;

8

Thank you. I have tried that. Oddly, it isn't updating the table for some reason. No idea why it isn't working.

9

Turns out I've got some duplication errors:

#1062 - Duplicate entry '20317-P-1' for key 'object_id'

I don't know how entries could be duplicated, but once I deleted them out of the database the query started working.