user3051460 user3051460 - 4 months ago 11
MySQL Question

How to show result from sql to list when click a tab?

I have an SQL file that has a table

student_tb
. In the table, we have three columns:
student_id, student_name, and class_name
. we can create the SQL by the following a script

CREATE TABLE `student_tb` (
`student_id` int(11) NOT NULL,
`student_name` varchar(128) NOT NULL,
`class_name` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `student_tb` (`student_id`, `student_name`, `class_name`) VALUES
(1, 'John', 1),
(2, 'Herry', 2),
(3, 'Peter ', 1),
(4, 'Tom', 2);
ALTER TABLE `student_tb`
ADD PRIMARY KEY (`student_id`);
ALTER TABLE `student_tb`
MODIFY `student_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;


We can show the student name where class_name =1,2 or all by the script

SELECT * FROM `student_tb` WHERE `class_name`=1
SELECT * FROM `student_tb` WHERE `class_name`=2
SELECT * FROM `student_tb`


I have a code to connect the SQL database and located it at the top of a html file

<?php include "connect.php"; ?>
<?php
$sql = "SELECT * FROM `student_tb` WHERE `class_name`=1";
$result = mysqli_query($conn, $sql);
?>


In the HTML file, I have three tabs which are used to display student result from sql, such as students in class 1, class 2, and so on.

I use bellow code to show the result in a list

<ul>
<?php
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
?>
<a href="#"><li class="clearfix">
<div class="w3-row-padding">
<div class="w3-half">
<p><?php echo $row["student_name"]; ?></p>
</div>
<div class="w3-half">
<p><?php echo $row["class_name"]; ?></p>
</div>
</div>
</li></a>
<?php }
} ?>
</ul>


I want to make a code show the result when I click on each tab. For example, when I click the tab "All", the result will show all students in class 1 and 2; when I click the tab "Student in class 1", it will show all student in class 1. How can I do it? When do I need close sql connection? Thank you so much.



<!DOCTYPE html>
<html lang="en">
<head>
<title>Bootstrap Case</title>
<meta charset="utf-8">
<link rel="stylesheet" href="http://www.w3schools.com/lib/w3.css">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
</head>
<body>

<div class="container">
<ul class="nav nav-tabs">
<li class="active"><a data-toggle="tab" href="#home">All students</a></li>
<li><a data-toggle="tab" href="#menu1">Students in class 1</a></li>
<li><a data-toggle="tab" href="#menu2">Students in class 2</a></li>
</ul>

<div class="tab-content">
<div id="home" class="tab-pane fade in active">
<h3>All</h3>
<p>List all students</p>
</div>
<div id="menu1" class="tab-pane fade">
<h3>Class 1</h3>
<p>List all students in class 1</p>
</div>
<div id="menu2" class="tab-pane fade">
<h3>Class 2</h3>
<p>List all students in class 1</p>
</div>
</div>

</div>

</body>
</html>




Answer

Hi You can put the sql code to fetch in all three tabs with their conditions like this

<?php   
   <?php include "connect.php"; ?>

?>

<!DOCTYPE html>
    <html lang="en">
    <head>
      <title>Bootstrap Case</title>
      <meta charset="utf-8">
      <link rel="stylesheet" href="http://www.w3schools.com/lib/w3.css">
      <meta name="viewport" content="width=device-width, initial-scale=1">
      <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
      <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
      <script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
    </head>
    <body>

    <div class="container">
      <ul class="nav nav-tabs">
        <li class="active"><a data-toggle="tab" href="#home">All students</a></li>
        <li><a data-toggle="tab" href="#menu1">Students in class 1</a></li>
        <li><a data-toggle="tab" href="#menu2">Students in class 2</a></li>    
      </ul>

      <div class="tab-content">
        <div id="home" class="tab-pane fade in active">
          <h3>All</h3>
          <p>List all students</p>
          <ul>
    <?php
        $sql  = "SELECT * FROM student_tb";
    $result = mysqli_query($conn, $sql);   
        if (mysqli_num_rows($result) > 0) {
        while($row = mysqli_fetch_assoc($result)) {
    ?>
        <a href="#"><li class="clearfix">
            <div class="w3-row-padding">                                                      
                <div class="w3-half">
                <p><?php echo $row["student_name"]; ?></p>              
                </div> 
                <div class="w3-half"> 
                <p><?php echo $row["class_name"]; ?></p>    
                </div>
            </div>    
        </li></a>
    <?php } 
    } ?>
</ul>
        </div>
        <div id="menu1" class="tab-pane fade">
          <h3>Class 1</h3>
          <p>List all students in class 1</p>
           <?php
        $sql  = "SELECT * FROM student_tb WHERE class_name=1";
    $result = mysqli_query($conn, $sql);   
        if (mysqli_num_rows($result) > 0) {
        while($row = mysqli_fetch_assoc($result)) {
    ?>
        <a href="#"><li class="clearfix">
            <div class="w3-row-padding">                                                      
                <div class="w3-half">
                <p><?php echo $row["student_name"]; ?></p>              
                </div> 
                <div class="w3-half"> 
                <p><?php echo $row["class_name"]; ?></p>    
                </div>
            </div>    
        </li></a>
    <?php } 
    } ?>
        </div>
        <div id="menu2" class="tab-pane fade">
          <h3>Class 2</h3>
          <p>List all students in class 2</p> 
           <?php
        $sql  = "SELECT * FROM student_tb WHERE class_name=2";
    $result = mysqli_query($conn, $sql);   
        if (mysqli_num_rows($result) > 0) {
        while($row = mysqli_fetch_assoc($result)) {
    ?>
        <a href="#"><li class="clearfix">
            <div class="w3-row-padding">                                                      
                <div class="w3-half">
                <p><?php echo $row["student_name"]; ?></p>              
                </div> 
                <div class="w3-half"> 
                <p><?php echo $row["class_name"]; ?></p>    
                </div>
            </div>    
        </li></a>
    <?php } 
    } ?>     
        </div>
      </div>

      </div>

    </body>
    </html>
Comments