PHP MySQL Selecting Multiple Tables Based on Column/Row Value

I am just doing some testing with a few tables that I’m trying to relate together, based on the values of my ‘students’ table.

Here are the total tables that I have:

students TABLE

id, name, course_num, msg_num

---

biology TABLE

id, details, msg_num

chemistry TABLE

id, details, msg_num

physics TABLE

id, details, msg_num

---

So I’m trying to select the ‘students’ table first, and based on the ‘course_num’ & ‘msg_num’ values in the ‘students’ table, it will determine which table is selected out of an array of tables that I have (as shown above, the ‘biology’, ‘chemistry’, & ‘physics’ tables).

The ‘students’ table looks like this:

The ‘biology’ table has data like this:

The ‘chemistry’ table looks like this:

The ‘physics’ table looks like this:

So, once again, I’m trying to have it to where if the number in ‘course_num’ in the ‘students’ table is set to either ‘0,1,2’ , then it will dynamically select the related table from the array list that I have set up in advance. Here is the exact code that I’ve tried below, but it is NOT syncing up properly. Please let me know what I’m doing wrong here:

<div style="position:relative; margin:auto; width:400px; font-family:Poppins; font-size:16px; border:2px solid black; padding:10px; margin-top:100px;">


<?php

include 'db_connect.php';


$selected_courses = array("biology", "chemistry", "physics");



$sql1 = "SELECT * FROM students";
$result1 = $conn->query($sql1);

while($row1 = $result1->fetch_assoc()){
    $course = $selected_courses[$row1['course_num']];

    $sql2 = "SELECT students.*, $course.*, FROM students, $course
    WHERE students.msg_num = $course.msg_num";

    $result2 = $conn->query($sql2);
    $row2 = $result2->fetch_assoc();

    echo "STUDENT NAME: ". $row2['name'] . "<br>";
    echo "DETAILS: ". $row2['details'] . "<br>";
    echo "MSG NUM: ". $row2['msg_num'] . "<br>";
    echo "<br>";
}


$conn->close();

?>

</div>

Answer

Your schema is basically wrong, and your code includes a nested query which is almost always the wrong way to do things.

Instead of three identical tables with different course data, set up one table and add a course_num column. This allows you to JOIN your students directly to their details rows, and you can use a single SELECT with a JOIN to extract the data.

So using your student table and this courseDetails table:

CREATE TABLE `courseDetails` (
  `id` int NOT NULL AUTO_INCREMENT,
  `msg_num` int NOT NULL,
  `course_num` int NOT NULL,
  `details` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;

You can JOIN on the common columns between the two tables like this:

select name, details, s.msg_num 
  from students s 
  join courseDetails d 
    on (s.course_num = d.course_num AND s.msg_num = d.msg_num);

To get this output in a single query:

name	details	msg_num
Adam Smith	Biology Details #0	0
John Wright	Chemistry Details #1	1

From that you can vary the JOIN conditions and use WHERE clauses to create different output as you need, all from the two basic tables.

This simplifies your PHP code to

include 'db_connect.php';

$sql1 = "select name, details, s.msg_num from students s join courseDetails d on (s.course_num = d.course_num AND s.msg_num = d.msg_num);";

$result1 = $conn->query($sql1);

while($row1 = $result1->fetch_assoc()){
    echo "STUDENT NAME: ". $row1['name'] . "<br>";
    echo "DETAILS: ". $row1['details'] . "<br>";
    echo "MSG NUM: ". $row1['msg_num'] . "<br>";
    echo "<br>";
}

$conn->close();

?>

Demo:https://www.db-fiddle.com/f/3cmTkGhEgiBEUZS7dFNvnz/0