PHP SQL Update array

Question

I originally was satisfied with the following in order to update row 1 and row 2 to the same value (status=1)

if ($_POST){
    $sql ="UPDATE table SET  status = 1,
            WHERE id IN (1,2 );";
    db()->query($sql);

    if(db()->query($sql)){
        echo "<b>Good</b>";
    } 
    else{
        echo "<b>No Good</b>";
    }
}

But now I want to update with different values, ie- row 1 to status 1, row 2 to status 2, and row 3 to status 3.

Off the bat, I know I need to 1. Use an array and loop through it three times. 2. pass in the array value into the $sql

I figure it would be something like this but I am still learning PHP..

$array_id = array(1, 2, 3);
$array_status = array(1, 2, 3);

if ($_POST){
    $sql ="UPDATE table SET  status = $array_status
            WHERE id = $array_id;";
    db()->query($sql);

    if(db()->query($sql)){
        echo "<b>Update Successful</b>";
    } 
    else{
        echo "<b>Update Unsuccessful</b>";
    }
}

How would I go about making this happen?

Answer 1

You didn't really explain why you need that, so

try1(childish): set status = id

"UPDATE table SET status = id"

It's a bad practice, and only you could understand what those numbers are. Plus if id is auto-increment, status will be auto-increment too, you will have a duplicate column. If status has only 3 values posible, you should not do this.

try2(basic): do 3 updates, or actually, do as many as you need with a for

if ($_POST){

$status = 1;

for ($i = 1; $i <= 3; $i++){
$sql ="UPDATE table
       SET status = $status
       WHERE id = $i;";
db()->query($sql);

$status++;
}

A better way bacause you have more control over the status. Of course the second try is irrelevant if you have only that 3 values. This one assumes you will change the $status variable inside the for loop, in concordance with the $i (id)

try3(mature): set one or 2 arrays with the concordance between id and status, so that either $arr[$id] will have the value of status and the key will be the id, or $arr1[$i] will have the value of id, and $arr2[$i] will have the value of status

the example will have only one array(also called map, because you map a value based on another value)

if ($_POST){

$status_array = array(1 => 1,2 => 2,3 => 3);


for ($i = 1; $i <= 3; $i++){
$sql ="UPDATE table
       SET status = $status_array[$i]
       WHERE id = $i;";
db()->query($sql);
}

Also, this works because the array is consistent. If you do not have an consistent array you should either work with 2 arrays, or try a foreach with key->value instead of for

Answer 2

I would suggest you to use the following code:

$theArray = array("1" => "1","2" => "2","3" => "3"); // The scheme is ID => Status

$errorMsg = false; // Our default assumption is that no error occured

foreach($theArray as $key => $value) {
    $sql = "UPDATE table SET status =".$value." WHERE id = ".$key;

    if(!db() -> query($sql)) { // Execute the query and check whether it failed
        $errorMsg = "Query for ID ".$key." failed.";
        break; // When the query failed we exit the loop and echo the error message
    }
}

if($errorMsg) { // If an error occured (errorMsg is no longer false) we echo it here
    echo $errorMsg;
}

Basically you do just create one array $theArray, which contains key => value pairs of the IDs and the statuses you want to give them. Afterwards, you loop through this array, execute the db() -> query() for each key => value pair and check whether it failed or not. If a query failed, you break the loop and output the error message.

---

Advantages:

• Instead of using two arrays ($array_id, $array_status) I do use only one associative array $theArray. The advantage here is that you only have one instead of two arrays and that you can extend the number of rows you'd like to change without changing your code. Just extend the array.
• The array $theArray does not need to be in a chronological order and you can give each ID independently of the other IDs a status.
• You are executing the db() -> query($sql) in your code two times. This is not very efficient and redundant. Instead you can execute the command only once and immediately check whether it failed or not based on its return value inside the if().
• The errorMsg I am creating in the code let you know which query failed so it gives you a more detailed information for debugging.

Answer 3

You can loop through the arrays using a for loop and exec a query for each one (Radu Vlad answer), or you can build a long query and execute it once, something like this:

if ($_POST){
    $sql = ""; // Blank string
    $len = count($array_id); // Number of iterations
    for ($i = 0; $i < $l; $i++) { // Enter the loop
        $sql .= "UPDATE 
                  table
                SET
                  status = {$array_status[$i]}
                WHERE id = {$array_id[$i]};"; // Append the query
    }
    db()->query($sql);

    if(db()->query($sql)){
        echo "<b>Update Successful</b>";
    } 
    else{
        echo "<b>Update Unsuccessful</b>";
    }
}

When the val of $i is 0, then $array_id[$i] will print the first element, when $i is 1, $array_id[$i] will print the second element, and so on.

Using .= you append text to a string. By the end of the loop, $sql will be a string with 3 queries ('UPDATE ... SET ...; UPDATE ... SET ...; UPDATE ... SET ...;').

Not sure if it's the best way, though. But you get the idea.

Answer 4

If yow want the status to be equal to the id, do this (single query):

UPDATE table SET status=id WHERE id IN (1,2,3);

Of course you can use some math, like:

UPDATE table SET status=(id+1)*2 WHERE id IN (1,2,3);

Answer 5

If you want to update multiple rows (in single query) using the INSERT syntax, you can do this:

REPLACE table(id,status) VALUES(1,1),(2,2),(3,3)

Notice that id must be Primary Key or Unique, otherwise the REPLACE will insert a new row. Notice also that REPLACE isn't SQL standard, and works only in MySQL.