How do I update mysql database WHERE value = value from PHP array?

Question

I am trying to use the keys of an array as values to update a column in my database. The column in question is the sort order for the rows.

Lets say the sort column values for each row are 1, 3, 5, 8, 9, 10, 12.

When the code is executed this is to be updated to read 1, 2, 3, 4, 5, 6, 7.

My code below does what I am trying to achieve when I echo the values however I am having a problem with the update query.

$sql = "SELECT asset, sort FROM portfolio ORDER BY sort ASC";
$query = mysqli_query($conn, $sql);

$i = 0;

while ($row = mysqli_fetch_assoc($query)){

    $asset[$i] = $row['asset'];
    $sort[$i] = $row['sort'];

    $new_sort = $i+1;

    $assets = implode(',', $asset);

    $update_sql = "UPDATE portfolio SET sort='$new_sort' WHERE asset IN ('$assets')";
    mysqli_query($conn, $update_sql);

$i++;
}

Using the following only the first row is being updated:

$assets = implode(',', $asset);

Using the following all rows are being updated, however they are all being set to the last value, in this example it would be 7.

$assets = join("','",$asset);

UPDATE: $assets is a string, an example would be google,yahoo,apple etc..

Any ideas where I am going wrong? Many Thanks.

$assets = implode("','", $asset); $update_sql = "UPDATE portfolio SET sort='$new_sort' WHERE asset IN ('$assets')"; try this — devpro
– devpro, Commented Oct 19, 2016 at 12:39
@devpro, using what you have just given me results in the same outcome as when i used $assets = join("','",$asset);, all rows are updated and being set to the last value, i.e 7 in this case. I am using the update in the while loop to update multiple rows. Thanks — Lee Perkins
– Lee Perkins, Commented Oct 19, 2016 at 12:45

Fjarlaegur · Accepted Answer · 2016-10-19 12:54:31Z

1

You are setting the new_sort for every asset every time you loop through.

First loop:

$assets[0] => "asset1"
$new_sort => 2
Update new_sort = 2 where asset in $assets. (So only asset1 will be updated.)

Second loop:

$assets[0] => "asset1"
       [1] => "asset2"
$new_sort => 3
Update new_sort = 3 where asset in $assets (So asset1 and asset 2 will be updated)

This will result into always setting the new_sort for every asset to the latest new_sort value;

The code for your output could be:

while ($row = mysqli_fetch_assoc($query)){

    $new_sort = $i+1;

    $update_sql = "UPDATE portfolio SET sort='" . $new_sort . "' WHERE asset='" . $row['asset'] . "';";
    mysqli_query($conn, $update_sql);

    $i++;
}

answered Oct 19, 2016 at 12:54

Fjarlaegur

1,4431 gold badge16 silver badges34 bronze badges

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

1 Comment

Lee Perkins Over a year ago

Answer accepted, thanks for showing it to me I see now.

Hamza Dairywala · Accepted Answer · 2016-10-19 13:23:24Z

1

This the solution for your desired output.

 $sql = "SELECT asset, sort FROM portfolio ORDER BY sort ASC";
 $query = mysqli_query($conn, $sql);
 $i = 0;
 while ($row = mysqli_fetch_assoc($query)){
       $asset[$i] = $row['asset'];
       $sort[$i] = $row['sort'];
       $new_sort = $i+1;
       $assets = implode(',', $asset);
       $update_sql = "UPDATE portfolio SET sort='$new_sort' WHERE asset like '".$row['asset']."'";
       mysqli_query($conn, $update_sql);
       $i++;
  }

edited Oct 19, 2016 at 13:23

answered Oct 19, 2016 at 12:47

Hamza Dairywala

4822 silver badges11 bronze badges

3 Comments

Lee Perkins Over a year ago

sorry mate it doesnt work, nothing at all is being updated. Thanks

Fjarlaegur Over a year ago

The WHERE asset IN () clause has no use here, as you will only have one asset in the IN clause.

devpro Over a year ago

asset is string

devpro · Accepted Answer · 2016-10-19 13:00:39Z

First of all you need to move your UPDATE query outside the while loop than you can use like that:

$assets = implode("','", $asset); // split $asset in comma and single quote.
$update_sql = "UPDATE portfolio SET sort='$new_sort' WHERE asset IN ('$assets')";

You are already store your values in an array $asset[$i] = $row['asset']; so no need to use query inside the while loop.

In your code, you are update the last value of $i in sort column.

The other solution is very simple, update single record as:

<?php
$sql = "SELECT asset, sort FROM portfolio ORDER BY sort ASC";
$query = mysqli_query($conn, $sql);

$i = 0;
while ($row = mysqli_fetch_assoc($query)){
    $new_sort = $i+1;
    $assets = $row['asset'];
    $update_sql = "UPDATE portfolio SET sort = '$new_sort' WHERE asset = '$assets'";
    mysqli_query($conn, $update_sql);
$i++;
}
?>

Collectives™ on Stack Overflow

How do I update mysql database WHERE value = value from PHP array?

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related