CODEWITHSUNDEEP
phpmysql
Naveen
Naveen

Reputation: 27

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

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 ?

Upvotes: 0

Views: 108

Answers (2)

RiggsFolly
RiggsFolly

Reputation: 94652

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269673

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.

Upvotes: 0

Related Questions