Home > front end >  PHP MySQL Selecting Multiple Tables Based on Column/Row Value
PHP MySQL Selecting Multiple Tables Based on Column/Row Value

Time:11-10

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: enter image description here

The 'biology' table has data like this: enter image description here

The 'chemistry' table looks like this: enter image description here

The 'physics' table looks like this: enter image description here

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>

CodePudding user response:

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

  • Related