How can I Display the Results of an MySql Query in PHP in Rows in a HTML Table?

PHP
1

I need some help with displaying the results correctly from the MySql query in rows in a HTML table. Can anyone offer some code, some corrections, and why I need to edit the code, and why the corrections where offered so that I can better learn and understand how to use MySql queries and display results so that I can do this next time without any help.

Here’s what the end result needs to be:

image
image1303×731 96.8 KB

Here’s my code so far:

<?php 

	include 'connection.php'; // includes the connection.php file to connect to the database

	// query to database and prepares and executes
	$query = "SELECT id, first_name, last_name FROM customers ORDER BY last_name asc";
	$result = $db->prepare($query);
	$result->execute();
	$result->store_result();
	$result->bind_result($query);

	// count the number of customers
	$total_customers = $result->num_rows;

?>

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Granger Customers</title>
<link href="assets/bootstrap.min.css" rel="stylesheet" type="text/css">
</head>

<body>

<!--start container to center design in browser-->
<div class="container">
    
    <div class="row" style="margin-bottom:30px">
        <div class="col-xs-12">
            <ul class="nav nav-pills">
              <li class="nav-item">
                <a class="nav-link" href="index.php">Home</a>
              </li>
              <li class="nav-item">
                <a class="nav-link" href="customers.php">Customers</a>
              </li>
              <li class="nav-item">
                <a class="nav-link" href="">Search</a>
              </li>
              <li class="nav-item">
                <a class="nav-link" href="">Add New</a>
              </li>
            </ul>
        </div>
    </div>


<div class="row">

  	<div class="col-xs-12">
      <table class="table table-bordered table-striped table-hover">
		   <p><strong>There are a total of <?PHP echo $total_customers; ?> customers.</strong></p> <!-- output total number of customers -->
              <thead>
                <tr class="success">
                  <th class="text-center">#</th>
                  <th>Customer ID</th>
                  <th>Last Name</th>
                  <th>First name</th>
                  <th class="text-center">Details</th>
                </tr>
              </thead>
              <tbody>
        
			
				  
				  <!--start of row that you need to include for each item returned from the query-->
				  
			
				   <?php
				  
				   while($result = $result->fetch_assoc($query)) {
				   '<tr>';
                   echo '<td>'.$row["id"].'</td>';
                   echo '<td>'.$row["first_name"].'</td>'; 
                   echo '<td>'.$row["last_name"].'</td>';
 
                   '</tr>';
				   }
				  
				   
				   ?>

			

                		
			
				 
                
              		<tr>
                      <td class="text-center active">1</td>
                      <td>20597</td>
                      <td>Aadil</td>
                      <td>Kareem</td>
                      <td class="text-center"><a class="btn btn-xs btn-primary" href="details.php?id=20597">Details</a></td>
                    </tr>
                
                
                
              </tbody>
            </table>
    </div>
    
</div>

</div>
<!--end container to center design in browser--> 

</body>

</html>
2

Just one comment regarding PHP. Where you prepare and execute the query, you don’t need to in this case, it could be a straight query() function.
Though it’s good you know about prepared statements, many people don’t use them when they should do.
Generally you will use prepared statements when you need to pass variables into the query, to help prevent injection, or when you plan to repeat the same query with differing parameters to help performance and reduce repetition.

The other comments are about HTML.
I see you are using Bootstrap, which suggests a responsive site, but you don’t have the viewport metatag in the head which is needed.

<meta name=viewport content="width=device-width, initial-scale=1">

I think Bootstrap needs a whole load of other stuff too.

The other html point is your table structure.

It’s invalid to have a <p> element in the table like this. Though you could use a <caption> element instead.
Next you have 5 header cells, making 5 columns.
But then in the while loop you only populate 3 columns with data. This doesn’t make much sense.

Try validating the resulting html code.

1 Like
3

In this section of code:

while($result = $result->fetch_assoc($query)) {
				   '<tr>';
                   echo '<td>'.$row["id"].'</td>';
                   echo '<td>'.$row["first_name"].'</td>'; 
                   echo '<td>'.$row["last_name"].'</td>';
 
                   '</tr>';
				   }

you have the opening and closing table-row tags, but you haven’t preceded them with an echo statement. You need to echo those strings, as you do in the three lines of code between them.

1 Like
4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.