While loop updates every row instead of one-CodePudding

I know that MySQL is depreciated but I am working on a very old code. Once its fixed I will Switch it to mysqli.

I have numbers of user, when I click on edit button for that particular user it will take me to another page where it will show me the courses/topics user is assigned. If they have completed that topic it will show complete and if not there will be date field and button to update it to complete. When I like on the button to button it for one topic it will update it to complete to all the other topic for that user.

I tried to change the value of button to learnID to CoureID but it doesn't work. Remove if from the loop but still no luck

while ($row = mysql_fetch_array($result) ) {
    $firstName = $row["a01FirstName"];
    $lastName = $row["a01LastName"];
    if ($row["a04Status"] == 0) {
        $iactive = "InActive";
    }else{
        $iactive = "Active";
    };
    
    if ($row["a04Completion"] == 0) {
        $CourseId =  $row["a04CourseId"];
        $isCompletion = "Not Completed</td>
        <td><input class='form-control' type='date' value=<?php echo  date('Y-m-d') id='pt_date' name='pt_date'></td>
                   <td><button type='submit' name='scomp' value='**{$CourseId}**'>Set Completion</button>";
        $progress = 140;
        
    }else{
        $isCompletion = "Completed";
    };
    
    $courses .= '<tr><td align="right" style="padding: 2px 20px 2px 10px;">'.$row["a03CourseName"].': </td>
                  <td>'.$iactive.'</td>';
    $courses .= '<td>'.$isCompletion.'</td>
                  </tr>';

    if (isset($_POST['scomp']) && intval($_POST['scomp'])) {
        $user_id = (int) $_POST['scomp'];
        $pt_date = $_POST["pt_date"];
        $CourseId =  $row["a04CourseId"];
        $progress = 47;
        
        $str1Update = "UPDATE tbl04usercourses SET a04WhmisProgress=$progress,a04note='completed manually',a04Completion=1,a04CompletionDate='$pt_date' WHERE a04CourseId=$CourseId AND a04UserId=$learnerID AND a04Status=1";

        $resultUpdate1 = mysql_query($str1Update,$db) or die('Error:'.mysql_error());
    }
}

CodePudding user response：

You shouldn't be doing the UPDATE in the same loop that's displaying the form. You're not updating the course associated with $_POST['scomp']; each iteration of the loop is updating the current row of the loop.

Take that update code out of the loop and just do it once.

IF (!empty($_POST['scomp'])) {
    $CourseId = intval($_POST['scomp']);
    $pt_date = mysql_real_escape_string($_POST["pt_date"]);
    $progress = 47;
    $str1Update = "UPDATE tbl04usercourses SET a04WhmisProgress=$progress,a04note='completed manually',a04Completion=1,a04CompletionDate='$pt_date' WHERE a04CourseId=$CourseId AND a04UserId=$learnerID AND a04Status=1";
    $resultUpdate1 = mysql_query($str1Update,$db) or die('Error:'.mysql_error());
}