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
– Jonnix, Commented Oct 4, 2018 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
– Octavio Galindo, Commented Oct 4, 2018 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
– AbdelKarim Baarini, Commented Oct 5, 2018 at 12:54
@JonStirling what do you think? could you provide a working syntax pleaase? — AbdelKarim Baarini
– AbdelKarim Baarini, Commented Oct 5, 2018 at 18:03

Marcos Henrique da Silva · Accepted Answer · 2018-10-04 23:41:50Z

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 4, 2018 at 23:41

Marcos Henrique da Silva

1

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

5 Comments

AbdelKarim Baarini Over a year ago

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?

Marcos Henrique da Silva Over a year ago

is that you have several elements and when it takes the where it always gives the update in all.

Marcos Henrique da Silva Over a year ago

You need to have the where, but reference the right element

Marcos Henrique da Silva Over a year ago

(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

AbdelKarim Baarini Over a year ago

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 ?

M4ST3RX · Accepted Answer · 2018-10-05 06:39:08Z

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 5, 2018 at 6:39

M4ST3RX

317 bronze badges

3 Comments

AbdelKarim Baarini Over a year ago

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

M4ST3RX Over a year ago

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?

AbdelKarim Baarini Over a year ago

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 :(

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related