Update SQL database table from PHP array values using foreach()

Question

I am trying to update a SQL table from a PHP array using foreach() . I noticed that only the last array value is written to all table fields. I went over many examples here but none of them solved it to me. eg (Simple update sql table from PHP array and some others) here is the array $product_qttys: Array ( [qty0] => 1 [qty1] => 4 [qty2] => 2 [qty3] => 3 [qty4] => 6 )

and here is the code:

foreach ($product_qttys as $key => $pr_qtys){

    $qttys = mysqli_real_escape_string($con, $pr_qtys);
    // print_r ($qttys);
    $sql = "UPDATE table-a SET qty = '$qttys' WHERE ip_add = '$ip'";
    $result = mysqli_query($con,$sql);

}

    echo "<br/>check SQL table<br/>";           

    $sqla = "SELECT * FROM table-a WHERE ip_add='$ip'";
    $querya = mysqli_query($con,$sqla);
    if  (mysqli_num_rows($querya) > 0) {
        while ($producta = mysqli_fetch_assoc($querya)) {
             echo ("item_qty=" . ($item_qty = $producta ['qty']) . "<br/>");
        }
    }

This is the output i am getting:

check SQL table
item_qty=6
item_qty=6
item_qty=6
item_qty=6
item_qty=6

PhpMyAdmin table is also showing the same. did I miss something or had sth wrong? Appreciate your feedback/input & hoping to close it. thank you.

So it's working the way I'd expect from looking at the code provided, i.e. you're doing 5 UPDATE queries, with each one updating all rows with $ip, with the quantity, so sure, on the last loop, all those rows become the same. That leaves the question of what you were expecting to happen that isn't. — Jonnix, Oct 04 '18 at 22:54
So, you have 5 rows with the same $ip... if you want to update them individually, you need to use anothe field to identify each one, and add that condition to your update. Lets do an example using cats ('cause everyone loves cats), you go to the pet store and ask for all their cats (they give you 5), then you say "Name them Jim", "Name them Frank", "Name them Jules", "Name them Cat", "Name them Purr"... in the end, they all are named "Purr". — Octavio Galindo, Oct 04 '18 at 23:17
To simplify it more, I've removed the condition `WHERE ip_add = "$ip"` so the update statement become `$sql = "UPDATE table-a SET qty = '$qttys'"; `. Shifting this update statement out of foreach() to run the update one time as follows: `foreach ($product_qttys as $key => $pr_qtys){ $qttys = mysqli_real_escape_string($con, $pr_qtys); } $sql = "UPDATE table-a SET qty = '$qttys'"; $result = mysqli_query($con,$sql);`. I'am having the same result: the last value of the array is written to all table fields. any solution @OctavioGalindo ? what do you think? — AbdelKarim Baarini, Oct 05 '18 at 12:54
@JonStirling what do you think? could you provide a working syntax pleaase? — AbdelKarim Baarini, Oct 05 '18 at 18:03

score 0 · Answer 1 · answered Oct 04 '18 at 23:41

0

you are doing the foreach in the values array and passing a same ip, so the where is saying forever to change the same element.

It puts the rest of the code that I see where it is wrong and I help to correct it.

But you'll have to do something like that.

$product_qttys = Array ( ["127.0.0.1"] => 1 ["127.0.0.1"] => 4 ["127.0.0.1"] => 2 ["127.0.0.1"] => 3 ["127.0.0.1"] => 6);

foreach ($product_qttys as $key => $pr_qtys){
  $qttys = mysqli_real_escape_string($con, $pr_qtys);

  $sql = "UPDATE table-a SET qty = '$qttys' WHERE ip_add = '$key'";
  $result = mysqli_query($con,$sql);
}


echo "<br/>check SQL table<br/>";           
$sqla = "SELECT * FROM table-a WHERE ip_add='$key'";
$querya = mysqli_query($con,$sqla);
if  (mysqli_num_rows($querya) > 0){
while ($producta = mysqli_fetch_assoc($querya)){
echo ("item_qty=" . ($item_qty = $producta  ['qty']) . "<br/>");
    }
}

to improve logic as well

answered Oct 04 '18 at 23:41

Marcos Henrique da Silva

1

Hello Marcus, I have removed "WHERE ip_add = '$ip'" so the update statement became: $sql = "UPDATE cart SET qty = '$qttys'"; and still having the same answer, nothing changed!! what do you think? – AbdelKarim Baarini Oct 04 '18 at 23:52
is that you have several elements and when it takes the where it always gives the update in all. – Marcos Henrique da Silva Oct 05 '18 at 00:33
You need to have the where, but reference the right element – Marcos Henrique da Silva Oct 05 '18 at 00:33
(Example: Update cart SET qty = 1 Where ip = '127.0.0.1') that he then changed the amount to 1 when the ip is 127.0.0.1 – Marcos Henrique da Silva Oct 05 '18 at 00:35
I think removing the `where ip_add="$ip"` may make it work. But i think the problem is in some place else in the code because when removing the where is leading to the same result. I tried to remove the update statement `$sql = "UPDATE cart SET qty = '$qttys'"` out of the `foreach()` loop to update onetime, but the problem still persists. What do you think @Marcus ? – AbdelKarim Baarini Oct 05 '18 at 20:47

score 0 · Answer 2 · answered Oct 05 '18 at 06:39

0

You are setting the 'qty' column 5 times to a different number so when the first loop passes once it sets the 'qty' column to 1 then as it moves forward it is going to rewrite the last number to the new number. You cannot save different numbers in the same column and row so if $ip = 127.0.0.1 then you are rewriting all the rows that has 127.0.0.1 in the ip row. Maybe something like $sql = "UPDATE table-a SET $key = '$qttys' WHERE ip_add = '$ip'"; would solve the problem but this is not tested so might not work. If the statement is right then you need to have all array keys as a different column.

answered Oct 05 '18 at 06:39

M4ST3RX

23
7

To simplify it more, I've removed the condition WHERE ip_add = "$ip" so the update statement became `$sql = "UPDATE table-a SET qty = '$qttys'";` . Shifting this update statement out of foreach() loop to run the update one time as follows: `foreach ($product_qttys as $key => $pr_qtys){ $qttys = mysqli_real_escape_string($con, $pr_qtys); } $sql = "UPDATE table-a SET qty = '$qttys'"; $result = mysqli_query($con,$sql);`. I'am having the same result: the last value of the array is written to all table fields. @M4ST3RX could you provide a working solution please? thanks – AbdelKarim Baarini Oct 05 '18 at 20:42
There is noting wrong with the ip address but even if you take out the ip address you still have to have different columns otherwise it will just rewrite the data. How do you want i tto be in the database? Have it in 1 row or multiple rows or even as a string or array? – M4ST3RX Oct 05 '18 at 21:31
The practice I found is to transform the array to string to be able to use it in an SQL statement. So $qttys is the string which is "escaped" from the $product_qttys single row array. $qttys need to be filled in the "qty" column of table-a. Am I right? I am seeking to update the "qty" values with the $product_qttys array where i am stuck :( – AbdelKarim Baarini Oct 05 '18 at 22:23

Update SQL database table from PHP array values using foreach()

2 Answers2