Same output in in MySQL update with foreach statement

Question

I am trying to figure out how to update MySQL table with array.

The Tables has 4 fields. REGNO, BATCHNO, NAMES and ATTEN_SUM. The REGNO has the unique value.

$i = 0;

while($row_recordset = mysql_fetch_array($query_run)) {

echo "<tr>";

echo "  <td>{$row_recordset['REGNO']}</td>
        <td>{$row_recordset['NAME']}</td>

        <td><input type='text' name='atten_ave".$i."'></td>

     ";

echo "</tr>";

$i++;

Here's my html code for the previous page after the update page.

foreach($_POST as $textbox => $values) {

$query_update = "UPDATE `grades` SET `ATTEN_SUM` = '$values' WHERE `BATCHNO` = '$sessionbatch'";
if(mysql_query($query_update)) {
     echo 'SUCCESS';
       } else{
       die(mysql_error());
              }
}

$_POST is a array from dynamic inputs from the previous page. Here's the example of my output in the table.

REGNO  |  BATCHNO       |   NAME       |    ATTEN_SUM
====================================================
  1    |  ARPA 00-055    |   Jason      |      99 
  2    |  ARPA 00-055    |   Mark       |      99 
  3    |  ARPA 00-055    |   Edgar      |      99

It updates all the rows with the last value that I input.

could you please also post the HTML you're using to send the post data? There are so many errors in your code that I'm feeling goosebumps! — Saturnix, Apr 29 '13 at 02:48

JOE LEE · Answer 1 · 2013-04-29T03:06:57.280

1

html

 //<input type='text' name='atten_ave".$i."' 
 <input type='text' name='atten_ave[]'...

php

//foreach($_POST as $textbox => $values) {
foreach($_POST['atten_ave'] as $textbox => $values) {

BUT this update is useless. it just update all record use last textbox.

i think you need to pass name or id to php, then sql query add something like 'where id="$_POST['ID']"...

*CHECK mysql injections.

edited Apr 29 '13 at 03:06

answered Apr 29 '13 at 03:01

JOE LEE

1,058
1
6
6

btw, shouldn't it be name='atten_ave[".$i."]' – Saturnix Apr 29 '13 at 03:04
hi thanks for the reply but yeah it will update all record tha I input on the last textbox. Do I need to crete another query that select the unique value? – zeus2026 Apr 29 '13 at 03:10
add hidden input name like name='ABC[]' to target row you want to edit..then query add LIKE id='$_POST['ABC'][$textbox]',you try try then tick this answer haha – JOE LEE Apr 29 '13 at 03:17
$JOE LEE lemme try sir hehe – zeus2026 Apr 29 '13 at 03:27

score 0 · Answer 2 · answered Apr 29 '13 at 02:43

0

Well as you said, REGNO is the unique key, but you are updating on BATCHNO, which is not unique, according to your output.

answered Apr 29 '13 at 02:43

keaton_fu

444
2
9

thanks for the reply but sir, Because I have many different BATCHNO in my table, and all I just want is to update all of the ATEN_SUM of same BATCHNO. – zeus2026 Apr 29 '13 at 02:48

score 0 · Answer 3 · answered Apr 29 '13 at 02:51

If you look at the WHERE clause carefully, (WHERE BATCHNO = '$sessionbatch') you'll understand that it DOES update the SAME row each time and thus you see the last update.

Print the query statement, It'll be much clear to you.

if(mysql_query($query_update)) {
  echo $query_update.'</br>';

score 0 · Answer 4 · edited May 23 '17 at 11:57

1)

You should not use mysql_* functions anymore. They're deprecated and may potentially leave holes for mysql injections. You should learn to use either Mysqli or PDO.

2)

Your code is totally open to any possible mysql injection. You should use mysql_real_escape_string or see 1) above.

3)

With foreach($_POST) you're iterating through every $_POST item. I'm assuming (maybe I'm wrong) that you're submitting data from an HTML form. Try var_dump($_POST) and you'll see there are many values that are not related to what you want to do. For example:

<input type=”submit” value=”Some value” name="update">

will result in PHP

echo $_POST["update"];
// result: Some value

if you could post the output of var_dump($_POST) we could all see what you're passing inside of the foreach loop and perhaps give more detailed solution.

Same output in in MySQL update with foreach statement

4 Answers4