Last row value is added to all the existing rows with UPDATE QUERY

Question

I want to update a table with with Columns

BEFORE UPDATE

 ROLLNO    NAME     ATTENDANCE     DAY   MONTH    YEAR
  1        Name1    PRESENT        16    04       2016
  2        Name2    PRESENT        16    04       2016
  3        Name3    PRESENT        16    04       2016

After update i am trying to change the attendance to "ABSENT" for everyone

But what happens after updating is

 ROLLNO    NAME     ATTENDANCE     DAY   MONTH    YEAR
  3        Name3    ABSENT         16    04       2016
  3        Name3    ABSENT         16    04       2016
  3        Name3    ABSENT         16    04       2016

here is the code that updates the database. But the problem here is the whole table is filled with the values of the last row when updated. I have echo-ed the values inside foreach to check whether values stored and passed are correct and it is perfect. The problem is while updating it to the DB

foreach ($name_array as $key => $name_values) {

    $name_values = mysqli_real_escape_string($connection,$name_values);
    $roll_values = mysqli_real_escape_string($connection,$roll_array[$key]);
    $att_values = mysqli_real_escape_string($connection,$att_array[$key]);
    echo $name_values."<br>";
    echo $att_values."<br>";
    echo $roll_values."<br>";
    $sql= "UPDATE `aclass10` SET 
                             Name='".$name_values.
                             "',attendance='".$att_values.
                             "',RollNo='".$roll_values.
                             "',day='".$day.
                             "',month='".$month.
                             "',year='".$year.
                             "'WHERE day='".$day."'";
    $result = mysqli_query($connection,$sql);
}

What is the mistake i am making and how to fix it ?

**WARNING**: When using `mysqli` you should be using parameterized queries and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you can very easily create a severe [SQL injection bug](http://bobby-tables.com/) if you forget to escape something. — tadman, Apr 17 '16 at 12:41
You should show the entire logic. Putting `update` in a loop is often preventable. — Gordon Linoff, Apr 17 '16 at 12:43

score 1 · Answer 1 · edited May 23 '17 at 12:31

Your where clause is wrong!

Change it to use the roll number, which I assume is unique, like this, you can also simplify your query string to make it easier to read.

foreach ($name_array as $key => $name_values) {

    $name_values = mysqli_real_escape_string($connection,$name_values);
    $roll_values = mysqli_real_escape_string($connection,$roll_array[$key]);
    $att_values = mysqli_real_escape_string($connection,$att_array[$key]);
    echo $name_values."<br>";
    echo $att_values."<br>";
    echo $roll_values."<br>";
    $sql= "UPDATE `aclass10` SET 
                             Name='$name_values',attendance='$att_values',
                             RollNo='$roll_values',day='$day`,
                             month='$month,year='$year
                             WHERE RollNo ='$roll_values'";
    $result = mysqli_query($connection,$sql);
}

Your script is potentially open to SQL Injection depending on how you create your input array. You should check this post out and try and use parameterized queries.

If it is possible for your previous query to amend all the roll_number column to be the same ID, you might want to look at making that a unique key, but that delends upon so many other things I dont knwo about your database design I cannot be sure

score 0 · Answer 2 · answered Apr 17 '16 at 12:46

I suspect that you can just update the rows you want to update with a single loop:

update aclass10
    set attendance = 'ABSENT'
    where ??;

I'm not sure what the condition is. If it is for everyone on all days, then you don't need a where clause. If for a particular day, then:

update aclass10
    set attendance = 'ABSENT'
    where year = $year and month = $month and day = $day;

(Note: this should be a parameterized value, but I'm leaving it in the original form.)

Another question is why you are using three columns for a date, when MySQL offers the really convenient data type called date to store such information.

Last row value is added to all the existing rows with UPDATE QUERY

2 Answers2