UPDATE RECORDS USING PHP LOOP

Question

I want to update data in my database using php loop.

I have tried updating data, but it only updates the last record in my list and returns all the records as null/zero.

// attempting to update data
$rcount_student_lists = mysqli_query($mysqli, $count_student_lists);
while($row2 = mysqli_fetch_row($rcount_student_lists))
    $student_count_count = $row2['0'];

for ($id = 1; $id <=$student_count_count; $id++)
{  
    $sql = "UPDATE exam_data SET score_s = '".${'dd_'.$id}."' WHERE exam_name_s = '".$one."'";
}

if (mysqli_query($mysqli, $sql)) {
    echo juuhead("DETAILS UPDATED SUCCESFULLY");
} else {
    echo "Error updating record: " . mysqli_error($mysqli);
}

I would want it to update all the records in the column score_s

Alex Howansky · Accepted Answer · 2019-05-30T14:33:59.040

0

You're generating the SQL string in a loop:

for ($id = 1; $id <=$student_count_count; $id++)
{  
    $sql = ...;
}

But you're only executing it once, because this is outside the loop:

if (mysqli_query($mysqli, $sql)) {

Move the query command inside the loop:

for ($id = 1; $id <=$student_count_count; $id++)
{  
    $sql = ...
    if (mysqli_query($mysqli, $sql)) {
        ...
    } else {
        ...
    }
}

You're also missing braces on your while loop:

while($row2 = mysqli_fetch_row($rcount_student_lists))
$student_count_count = $row2['0'];

Without braces, the while only loops the one line following it. To loop over more than one line, you need to wrap the lines in braces:

while($row2 = mysqli_fetch_row($rcount_student_lists))
{
    $student_count_count = $row2['0'];
    for ($id = 1; $id <=$student_count_count; $id++)
    {
        ...
    }
}

Also, please read about SQL injection. Instead of building queries with string concatenation, use prepared statements with bound parameters. See this page and this post for some good examples.

edited May 30 '19 at 14:33

answered May 30 '19 at 14:00

Alex Howansky

50,515
8
78
98

This loops the response and only updates the last record. – Vincent Omondi May 30 '19 at 14:24
Ah, noticed another error and updated the response. – Alex Howansky May 30 '19 at 14:34
Note, you'll benefit greatly from proper indentation. It's much harder to see where loops are broken if every line starts on the first column. – Alex Howansky May 30 '19 at 14:35
I figured it out – Vincent Omondi May 30 '19 at 15:37

UPDATE RECORDS USING PHP LOOP

1 Answers1