Display data based on dropdown selection in PHP & MySQL

In this tutorial, You will learn to display data based on the dropdown selection in PHP & MySQL with some simple steps. These steps are very easy to understand and implement in web applications.

Let’s know how to work it, When you select an option of the dropdown and submit the form, then data will be displayed in a table based on the selected option.

Here, I have displayed some students’ records in an HTML table based on their courses. Once you learn it, you will easily customize it according to your project requirement.

Display Data From Database Based on Dropdown Selection Using PHP

Before getting started its coding, you should create the following folder structure –

codingstatus/
     |__ database.php
     |__ edit-button.php
     |__ fetch-script.php
     |__ edit-form.php
     |__ edit-script.php
     |__ update-script.php

1. Create a Database & Table

First of all, Create a MySQL database with the name of ‘codingstatus’

Database Name – codingstatus

CREATE DATABSE codingstatus;

Create a table with the name of ‘course’ to insert the course name

Table Name – courses

CREATE TABLE `courses` (
`id` int(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
`courseName` varchar(255) DEFAULT NULL
);

Also, create another table with the name of ‘students’

Table Name – students

CREATE TABLE `students` (
`id` int(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
`fullName` varchar(255) DEFAULT NULL,
`courseName` varchar(255) DEFAULT NULL,
);

3. Connect PHP to  Database

Now, Connect the database to display data based on dropdown selection

File Name – database.php

<?php

$hostName = "localhost";
$userName = "root";
$password = "";
$databaseName = "codingstatus";

 $conn = new mysqli($hostName, $userName, $password, $databaseName);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

?>

2. Insert Data into a Table

After creating the database & table, you have to insert data into both tables. If you don’t know, you can do it with the help of the following article –

Insert Select Option Value with another input value using PHP & MySQL

3. Display Data in the Dropdown

To display data from the “courses” table in the database, you have to follow the following steps –

Step -1: Include the database.php file

Step-2: Create a select option input field with the name=”courseName”

Step-3: Also, create a submit input field with the name=”submit”

Step-4:  Fetch  ‘courseName’ from ‘courses and display it in the select option

Step-5: Include the display-data.php file to display data while you select an option and submit the form.

File Name – data.php

<?php
include("database.php");
?>
<form action="" method="post">
<select name="courseName">
    <option value="">Select Course</option>
    <?php 
    $query ="SELECT id, courseName FROM courses";
    $result = $conn->query($query);
    if($result->num_rows> 0){
        while($optionData=$result->fetch_assoc()){
        $option =$optionData['courseName'];
        $id =$optionData['id'];
    ?>
    <option value="<?php echo $id; ?>" ><?php echo $option; ?> </option>
   <?php
    }}
    ?>
</select>
<input type="submit" name="submit">
</form>
<?php
include("display-data.php");
?>

4. Fetch Data From the database

In this step, you have to fetch data from the database based on dropdown option id. This file will execute when you select an option and submit the form.

File Name – fetch-data.php

<?php
/// edit data
if(isset($_POST['courseName']) && isset($_POST['submit'])){
    $courseId= $_POST['courseName'];
    $query ="SELECT fullName, emailAddress FROM students WHERE courseId='$courseId'";
    $result = $conn->query($query);
    if($result->num_rows> 0){
      $students= mysqli_fetch_all($result, MYSQLI_ASSOC);
    }else{
     $students=[];
    }
}
?>

5. Display Data in HTML Table

To display data in HTML table, you will have to follow these steps –

Step-1: Include the ‘database.php’

Step-2: Create a HTML table with the three columns like s.n, full name, & course name

Step-3: display data in HTML table using foreach loop

File Name – display-data.php

<?php
include('database.php');
?>
!-----display data-->
    <?php
    if(isset($students)>0)
    {
    ?>
    <table border="1" cellspacing="0" cellpadding="5">
    <tr>
        <th>S.N</th>
        <th>Full Name</th>
        <th>Email Address</th>
    </tr>
    <?php
       if(count($students)>0)
       {
    $sn=1;
    foreach ($students as $student) {
     ?>
<tr>
    <td><?php echo $sn; ?></td>
    <td><?php echo $student['fullName']; ?></td>
    <td><?php echo $student['emailAddress']; ?></td>
</tr>
     <?php
   $sn++; 
   }
}else{
    echo "<tr><td colspan='3'>No Data Found</td></tr>";
}
?>
</table>
<?php
}
?>