CODEWITHSUNDEEP
phpmysql
AndrewS
AndrewS

Reputation: 1565

How to UPDATE all rows in SQL with one query?

I have an existing DATABASE of Prestashop, and I have made small modifications to it. I made a separate PHP page which gets data from table with products (only the data i need) and shows me all products that ARE IN THAT TABLE, one after another. I also have a function which updates a products, on which i press EDIT.

So, in a simple words i can update a single product per click. But now I have about 220 products, and in order to update a value (price) for each product, now, i must click EDIT for each product, 220 times.

Is there a way to make a query in order to update all rows after i click a BUTTON?

Bellow I will show you some basic parts from my script:

GET function:

<?php

$strSQL = "SELECT * FROM ps_product";
$objQuery = mysql_query($strSQL) or die ("Error Query [".$strSQL."]");

while($objResult = mysql_fetch_array($objQuery))
{
?>
<!-- HTML CODE -->
<? } ?>

UPDATE function for each product

if($_POST["hdnCmd"] == "Update")
{
    $strSQL = "UPDATE ps_product_shop SET ";
    $strSQL .="id_product = '".$_POST["txtEditid_product"]."' ";
    $strSQL .=",price = '".$_POST["txtEditprice"]."' ";
    $strSQL .=",active = '".$_POST["txtEditactive"]."' ";
    $strSQL .="WHERE id_product = '".$_POST["hdnEditid_product"]."' ";
    $objQuery = mysql_query($strSQL);
    if(!$objQuery)
    {
        echo "Error Update [".mysql_error()."]";
    }

}

$_POST["hdnEditid_product] is the product id value that is taken from table.

Please help me to understand how to make a similar **UPDATE function** **which will update all rows (product_id) at once?

As an example i must work around this code:

if($_POST["UPDATEALLPRODUCTS"] == "Update")
{
    $strSQL = "UPDATE ps_product_shop SET ";
    $strSQL .=",price = '".$_POST["txtEditprice"]."' ";
    $strSQL .="WHERE id_product = '".$_POST["hdnEditid_product"]."' ";
    $objQuery = mysql_query($strSQL);
    if(!$objQuery)
    {
        echo "Error Update [".mysql_error()."]";
    }
}

but what I must add/change here:

$strSQL .="WHERE id_product = '".$_POST["hdnEditid_product"]."' ";

Upvotes: 0

Views: 6050

Answers (4)

Zenonline
Zenonline

Reputation: 102

I am assuming that the page already has the updated price / info that you need and therefore it is just plucking the php variables out

UPDATE ps_product_shop SET price
 = CASE 
 WHEN id_product= $id_product_1
 THEN '$edited_price_1'
 WHEN id_product = $id_product_2
 THEN '$edited_price_2'

Without seeing the form structure it is hard to see what would be the correct variable names but hopefully you can take the code and go with it, i have to do something similar and have PhP autobuild the query using this method

Upvotes: 1

Alireza Fallah
Alireza Fallah

Reputation: 4607

How about this ?

Make an array like this : id => price, and then :

        $prices = array(
            1=>300,
            5=>180,
            ...
        );
        foreach ($prices as $id => $price) {
            $query = "UPDATE ps_product_shop SET price='".$price."' WHERE 
            id_product='".$id."' ";
            mysql_query($query);
        }

Upvotes: 1

Lauri Elias
Lauri Elias

Reputation: 1299

One way to do it, if you insist on using a single query:

<?php
    $product_updates = array("1" => "500", "2" => "1299.99");
    $sql_query = "UPDATE my_database.products SET price = CASE id ";
    foreach ($product_updates as $key => $value) {
        $sql_query .= "WHEN '$key' THEN '$value' ";
    }
    $sql_query .= "END;";
    mysql_query($sql_query);
?>

Upvotes: 1

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

To update all rows to the same value run this query

UPDATE ps_product_shop SET price='".$_POST["txtEditprice"]."';

NOTE: Every records in ps_products_shop will get the same value in price.

Upvotes: 2

Related Questions