How do I use PHP to print out data from SQL table that matches user input?-CodePudding

I have successfully joined 2 tables together using INNER JOIN, however now I need to only echo the rows that match the student_ID that the user inputted at the beginning. I also HAVE to use sessions. Any Help would be greatly appreciated.

This is the code that prints out the WHOLE table.

<?php
        $sql = "SELECT Student.Name, Student.Student_ID, Student.Major, Transcript.Course, Transcript.Grade
        FROM `Student`
        Inner JOIN `Transcript`
        ON Student.Student_ID = Transcript.Student_ID;";
        $result = mysqli_query($con, $sql);
        $resultCheck = mysqli_num_rows($result);

        if ($resultCheck > 0){
            while($row = mysqli_fetch_assoc($result)){
                echo "<tr><td>" . $row['Name'] . "</td><td>" . $row['Student_ID'] . "</td><td>" . $row['Major'] . "</td><td>" . $row['Course'] . "</td><td>" . $row['Grade'] . "</td><tr>";
            }
        }
        else{
            echo "No Results";
        }

CodePudding user response：

You missed a "AND" in your select query.

Try this...

You can use prepared statements to prevent SQL injection. SO your code becomes

   $sql = "SELECT Student.Name, Student.Student_ID, Student.Major, Transcript.Course, Transcript.Grade
   FROM `Student`
   JOIN `Transcript`
   WHERE Student.Student_ID = Transcript.Student_ID AND Student.Student_ID = ?;";

$stmt = $conn->prepare($sql); 
$stmt->bind_param("i", $_SESSION['Student_id']);
$stmt->execute();
$result = $stmt->get_result();

if($result->num_rows() > 0){

   while ($row = $result->fetch_assoc()) {
      echo "<tr><td>" . $row['Name'] . "</td><td>" . $row['Student_ID'] . "</td><td>" . $row['Major'] . "</td><td>" . $row['Course'] . "</td><td>" . $row['Grade'] . "</td><tr>";
   }
}else{
   echo "No Results";
}

CodePudding user response：

Based on this answer but keep the ON sentence in the JOIN, as it could be clearer, especially if we would have several joins. Also, the "else" sentence in many cases is not necessary, so we can clean the code a little:

$sql = "SELECT Student.Name, Student.Student_ID, Student.Major, Transcript.Course, Transcript.Grade
        FROM `Student`
       JOIN `Transcript` ON Student.Student_ID = Transcript.Student_ID
       WHERE Student.Student_ID = ?;";

$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $_SESSION['Student_id']);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows() == 0) {
    echo "No results";
    return;
}

while ($row = $result->fetch_assoc()) {
    echo "<tr><td>" . $row['Name'] . "</td><td>" . $row['Student_ID'] . "</td><td>" . $row['Major'] . "</td><td>" . $row['Course'] . "</td><td>" . $row['Grade'] . "</td><tr>";
}

Note that you should save the student id in the session var $_SESSION['Student_ID']