Joining two table and updating the result

Question

I am joining two table and I want to update all the rows.

<?php
include("connection/mysqlconnect.php");

$sql="  SELECT course.duration, course.id, students.ID 
    FROM course, students 
    where course.id=course_id and course.duration = '2'";

$result = $conn->query($sql);
$count=mysqli_num_rows($result);


if($count>=1)
{
    while($row = mysqli_fetch_array($result)) {
            $id = $row['ID'];
            $stat = 'Active';
            $year = '2nd Year';
            $Graduated = 'Graduated';

    $sql1 = "UPDATE students SET Year='$Graduated', Status='non-Active' 
WHERE ID = '$id' and (status='$stat' and Year='$year')";
    echo "$id</br>";
    }

}
?>

I tried the Select Statement above in "Run SQL query" and it query the result i want. and I want to update all of the query, but I cant. I tried Putting echo under the update and it echo the ID's I need to update, but my update statement is not executing.

so far it's a typo question, since you didn't execute that query like you did the other one. — Funk Forty Niner, Mar 02 '18 at 02:41
bit of a tip: don't store dates and times like that. MySQL has built-in functions made just for this. You should look that up. — Funk Forty Niner, Mar 02 '18 at 02:44
it says successful when i run the update statement in the "Run SQL query" but nothing changes. — Bryan Carlo, Mar 02 '18 at 02:57
I tried running the update with the fix value in ID and all the variable, and it work — Bryan Carlo, Mar 02 '18 at 03:00

John Woo · Answer 1 · 2018-03-02T02:59:57.483

1

Instead of selecting all students then updating one by one, you can actually to this in one shot by joining both tables and updating it.

UPDATE  students s
        INNER JOIN course c ON c.id = s.course_id
SET     s.Year = '$Graduated', 
        s.Status = 'non-Active'
WHERE   c.duration = '2'
        AND s.status = '$stat' 
        AND s.Year = '$year'

It must also be taken into consideration that the query above is vulnerable with sql injection. This article below will guide you how to prevent from it.

How to prevent SQL injection in PHP?

edited Mar 02 '18 at 02:59

answered Mar 02 '18 at 02:56

John Woo

258,903
69
498
492

This answer is better than mine. – cdaiga Mar 02 '18 at 02:58
no errors, but still not updating. I was sure this would run thou. – Bryan Carlo Mar 02 '18 at 03:19

cdaiga · Accepted Answer · 2018-03-02T03:52:25.377

0

The issue with the first query is that there are two columns with the same name; ID. So referencing the ID from the row generates an error. Use alias to fix it as shown below. For a better performance use an inner join instead. You also forgot to run the update query again your database.

<?php
include("connection/mysqlconnect.php");

$sql="  SELECT course.duration, course.id as cID, students.ID as sID
    FROM course JOIN students ON course.id=course_id
    where course.duration = '2'";

$result = $conn->query($sql);
$count=mysqli_num_rows($result);

if($count>=1)
{
    while($row = mysqli_fetch_array($result)) {
            $id = $row['sID'];
            $stat = 'Active';
            $year = '2nd Year';
            $Graduated = 'Graduated';

    echo "Student ID to be updated: $id<br/>";

    $sql1 = "UPDATE students SET Year='$Graduated', Status='non-Active' 
WHERE ID = '$id' and (status='$stat' and Year='$year')";

   //you have to execute the query for the update to be done.
   if ($conn->query($sql1) === TRUE) {
       echo "Record updated successfully ";
   } else {
       echo "Error updating record: " . $conn->error;
   }   
 }
}   
$conn->close();
?>

edited Mar 02 '18 at 03:52

answered Mar 02 '18 at 02:56

cdaiga

4,861
3
22
42

My answer specifically points to the error in your code, a provide a fix. @JohnWoo answer is presents how you should do the update efficiently. There is no need selecting data that is not meant to be displayed but updated if you can just update it simply. – cdaiga Mar 02 '18 at 03:01
it still has the same result thou. I tried the update in "run sql query" with the fix value and it work, the echo i put under the update also echo the ID's i need to update. but still nothing, when i run it. – Bryan Carlo Mar 02 '18 at 03:11
It doesn't change anything in your database because you also forgot to run the update query against your database. I just edited my code. – cdaiga Mar 02 '18 at 03:24
it has this error "unexpected end of file....on line 33" located under the closing bracket of php – Bryan Carlo Mar 02 '18 at 03:30
I forgot to close the `while` loop. I just corrected the code. Try again. – cdaiga Mar 02 '18 at 03:31
it says. "Error updating record:" 6 times since 6 records should be updated. – Bryan Carlo Mar 02 '18 at 03:34
@BryanCarlo try to run again to see if it would print all the student Ids to be updated. – cdaiga Mar 02 '18 at 03:42
it printed this "Student ID to be updated: 9 Error updating record:" and all 5 other ID's – Bryan Carlo Mar 02 '18 at 03:45
It means that the update query did not running smoothly. Try again @BryanCarlo. – cdaiga Mar 02 '18 at 03:50
1

I got the problem. its on your if statement below. $sql should be $sql1. – Bryan Carlo Mar 02 '18 at 03:51

Joining two table and updating the result

2 Answers2