How can I loop through mysql query results and display table rows?

sean4fsu777 · November 18, 2018, 7:54pm

I have a homework assignment that is asking me to loop through a mysql query and display the results in a table row. I have a snippet of what the finished assignment is supposed to look like down below. And I also need to I go inside the href for the “Details” line and echo out the id of each row. I also need to “total” # of customers as the screenshot down below shows.

I have spent 20-30 minutes Googling and reading the posts on various sites to try to get some help with this assignment but have not been able to get the help I need. I guess I need someone to help me walk through the rest of this assignment. My professor works in the private sector, teaches as an adjunct professor, and has a very big family to take care of, so he is not available a lot of time.

Can someone help me with some tips, suggestions, and/or point me in the right direction?

[code]<?php

include 'connection.php';

$query = "SELECT id, first_name, last_name FROM customers ORDER BY last_name asc";
$stmt = $db->prepare($query);
$stmt->execute();

?>

<!doctype html>

Granger Customers

<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="col-xs-12">
<p><strong>There are a total of XXX customers.</strong></p>
  <table class="table table-bordered table-striped table-hover">
          <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-->
            
          		<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>

[/code]

ErnieAlex · November 18, 2018, 8:07pm

  if ($stmt->num_rows > 0) {
         // output data of each row
         while($row = $stmt->fetch_assoc()) {
             echo "id: " . $row["CustomerID"]. " - Name: " . $row["FirstName"]. " " . $row["LastName"]. "<br>";
         }
     } else {
         echo "0 results";
     }

Just to get you started. You will need to add in the table code to create the table, rows and td’s.

sean4fsu777 · November 18, 2018, 8:16pm

Thank you @ErnieAlex!

Can you walk me through the if else statement briefly? Because I know that this not a site to do tutorials So I wouldn’t expect you to go real in depth to explain what is going on in that if else statement.

If I am understanding your suggestion, as far as the table that you are asking for is already created? See here in the code on line 48, the opening table tag, and on line 73, the closing table tag?

ErnieAlex · November 18, 2018, 8:51pm

Sean, we have tutorials on this site if you search for them. But, here are some comments on it.
First, the query is set up in your code.
You execute the query in MySQL and it returns a handle to the query.
Then, you can check if there are values or not found in your database. Normally you want to display a message something like “No data found for your query!” or “No matching data located!” … Just something to tell the user that the query found no results. I just said zero found…
Next, the “WHILE” loop fetches data from each row of data in the query’s results. I used fetch_assoc which pulls out each field in the row of data and places it into an array with a key of the field-name and data of the value in the database row for that field name. I just used “CustomerID”, “FirstName” and “LastName” as an example. I assumed you could fix that. The ->fetch_assoc() function will go thru the database table and load an array with each field name and it’s data. In this case I called it $row. This $row array is a full list of the data pulled from the entire database table.
Lastly, as you loop thru each row of data, you can create a table to display the data in. To do that, you would create one

… for each row of data. Before the loop you would need to create the with headers and after the loop create the end of the table to close it.

Hope that helps. I did NOT give you an exact solution for this. Since it was for class. You need to learn it yourself. Now, to help a little further, change the example I posted and use the actual field names in your table.

To use data from PHP inside your forms, you can do this in many ways. Since you already have the layout for the HTML part including your tables, you just need to alter that to include the data from your database. the first thing is the total number of customers found. This can be used in this way:
$total_customers = $stmt->num_rows; OR $total_customers = $stmt->count();
Then where you need to display this value, you would do it by replacing your test number with this:

<?PHP echo $total_customers; ?>

This is basically just printing the value of the total number for you. Therefore, you count line would be:

There are a total of <?PHP echo $total_customers; ?> customers.

The loop to display the data rows is a little more complicated. Before the one line you showed in your sample output post, you have to create the loop I posted before.

<?PHP
 if ($stmt->num_rows > 0) {
     // output data of each row
     while($row = $stmt->fetch_assoc()) {
?>

Note this is inserted just before your first

in the html code where you show the line of data.

Then, use <?PHP echo $row["id"]; ?> where you need the id to show and the same for names.
You would need to use this same code to replace the index link in the to details.php so the id is attached to the link button.

Lastly, you will need to end the while loop after the

for the row of data, just before the line.
Like this: <?PHP } ?>

Well, that should do it for you. Gave you the solution… Up to you to piece it together and if it does not work, ask again… Good luck…

sean4fsu777 · November 19, 2018, 2:11am

Thanks, again @ErnieAlex!

ErnieAlex · November 19, 2018, 3:44am

Been gone for a few hours… Let me know if this helps or if you have other questions on this…
Good luck!

sean4fsu777 · November 19, 2018, 10:39am

You definitely have been helping me a lot. I just need some more help in working out how to properly insert the needed code to make this assignment work.

Here’s the part of the assignment that I left out I should have included in the beginning of this post:

Create the needed statements that will use this query “SELECT id, first_name, last_name FROM customers ORDER BY last_name asc” as a prepared statement with no parameters. Note: since we have no parameters in the query, you do not need to use $stmt->bind_param(); at all in this page.
Loop through the query results:
You now need to create a loop around the tags and echo out the appropriate data so it matches the example below, as well as my online example at:
Once you have this working correctly, make sure you go inside the href for the “Details” link and echo out the id of each row as you will need that in the details.php assignment you will do next.
Show the “total” # of customers as the screenshot does.
Your file should look like the screenshot below if done correctly.

I’m having some trouble making the loop work that you helped with. BTW - The database that this loop is working with is called ‘Customers’, and the table names are ‘id’, ‘first_name’, ‘last_name’. Can you help me with inserting that data correctly within this loop? I got confused after I inserted it on how to properly insert it.

I"m not sure how to write all of the code in regards for item numbers 18 & 19 in this assignment.

I’m getting an error on this line of code (unexpected quoted-string T_CONSTANT_ENCAPSED_STRING): echo "id: " . $row[“CustomerID”]. " - Name: " . $row[“first_name”]. " " . $row[“last_name”] "

As far as, where would I insert the total number of customers found?
$total_customers = $stmt->count()

Where would be a good place to insert this code? Should this code be within a loop? And how could I loop it?
<;?PHP echo $row[“id”]; ?>;

ErnieAlex · November 19, 2018, 3:47pm

This is how you get the total number of customers after you execute your query.
That will give you a number in the $total_customers variable.
Then, in your HTML part of the code, you can display it this way:

<p><strong>There are a total of <?PHP echo $total_customers; ?> customers.</strong></p>

I did show you that in my previous post. In that same post, I explained how to alter the HREF part of the (anchor) tag so that the user’s id number is place in the correct place. Reread my last post and follow what is there to help rewrite your code. Then, post your new code and we can see how it looks.

ErnieAlex · November 19, 2018, 4:01pm

More information is always good when learning PHP. Here are some comments on how PHP actually works. It might help you understand the logic of your code.

PHP is handled “SERVER-SIDE”. This means that it runs on the server. ALL PHP code is executed BEFORE it is sent to the browser to be rendered and displayed.

HTML and CSS code is done on “CLIENT-SIDE”. This means it is done in the local computer that you are working on. Usually this is all handled in a browser.

Now, when that explained, you can imagine all of the PHP code handles various database handling and any computations needed. Then, it combines outputs where needed and places it inside your HTML code and sends it out to the browser. The browser doesn’t care about the database. So, what you read or view on a site is just HTML and CSS code. Nothing more at all. (Well, there can be Javascript or JQuery code in that which runs on the “CLIENT-SIDE” or in the browser, but, that is for another class! LOL )

So remembering all this, your main PHP code would be at the top of the page before any HTML is displayed. After that, PHP code is placed where it is needed throughout the rest of the HTML where data is needed to be displayed. So, normally most of code pulling out data to be displayed would be done inline in the HTML code. Remember this PHP code will NEVER be shown on a finished page that is sent out to the browser. It is all handled on the server and only the echo’ed data will be seen on the browser. Never any code!

To review what your PHP code does once you create a webpage, you can view the resulting page and right-click on the page and select VIEW-SOURCE. This will show your final page’s code without any PHP showing. It is a handy tool so you can understand what your PHP code is inserting into the HTML code. Sometimes it is a minor mistake that will show up that way.

Well, just more info to help you understand this process…

sean4fsu777 · November 20, 2018, 2:03am

Here’s the code I tried to insert from the previous solutions you gave me. I am just guessing. I can’t figure where to place: $total_customers = $stmt->num_rows; and I can’t figure out where to place this code either: There are a total of <?PHP echo $total_customers; ?> customers.

<?php include 'connection.php'; // includes the connection.php 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"; $stmt = $db->prepare($query); $stmt->execute(); $total_customers = $stmt->num_rows; ?>

<!doctype html>

Granger Customers

<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="col-xs-12">
<p><strong>There are a total of <?PHP echo $total_customers; ?> customers.</strong></p>
  <table class="table table-bordered table-striped table-hover">
          <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>
          	
			  <?php
			  
			  	// if else statement to loop through query results and display in rows
          		 if ($stmt->num_rows > 0) {     				
					 
				while($row = $stmt->fetch_assoc()) {
    			echo "id: " . $row["CustomerID"]. " - Name: " . $row["first_name"]. " " . $row["last_name"] "<br>";
				}
				} else {
				echo "0 results";
				}
				?>
            
          		<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>

ErnieAlex · November 20, 2018, 2:24am

Well, you basically do the code like this:

<html>
<body>
<header>...stuff...</header>
<?PHP
   ...   Set up your connection code to the database
  ...    Set up your query
  ...    Execute your query to select your data
  ...  Get your total count of data found (that is where you create the total number)
?>
HTML goes here and place the total count in the correct place.
HTML that starts off the table
<?PHP
   ...   Here is where you do the loop to display 
?>
HTML end of table
</body>
</html>

So, as you can see, you can, you can place PHP code all thru the webpage as needed. The connection part of the code to connect to the database can be put at the very top of the page if you wish. I usually do that, but, in this example I put it after the page is started. Just need to put that before you use the total variable.

Hope that helps! Let us know if you need more help…

sean4fsu777 · November 22, 2018, 11:31pm

I am still getting an error about constant encapsed string with this line of code

echo "id: " . $row[“CustomerID”]. " - Name: " . $row[“first_name”]. " " .

To me, everything looks good with this code, but somehow I am getting an error.

ErnieAlex · November 22, 2018, 11:57pm

[quote=“sean4fsu777, post:12, topic:27619”] . $row[“first_name”]. " " .
[/quote]

The ending should be an end of line not a blank. Also, you did not end this line with a semicolon !

[quote=“sean4fsu777, post:12, topic:27619”]. $row[“first_name”] . " ";
[/quote]
But, you stopped not at the end of the line. Can’t help you if you do not show all of the line…

sean4fsu777 · November 23, 2018, 12:01am

Here’s the whole line:

echo "id: " . $row[“CustomerID”]. " - Name: " . $row[“first_name”]. " " . $row[“last_name”] “
”;
}
Also, I’m having trouble interpreting whatever text showed up in your last reply. It must be a system error.

ErnieAlex · November 23, 2018, 12:14am

Did you read my last post? SEMICOLON???

ErnieAlex · November 23, 2018, 12:18am

What editor do you use? Get and try NetBeans. It is free and it shows these minor errors to you so you can fix them before you try the code…

sean4fsu777 · November 23, 2018, 12:35am

I’m using Dreamweaver 2019 19.0. The latest version. I will try to use NetBeans. What languages of Web Development can I use with NetBeans?

ErnieAlex · November 23, 2018, 12:48am

Well, Dreamweaver is really a HTML or webpage creator, not a programmer’s IDE.
NetBeans will let you do almost everything. I just use the one labelled HTML/PHP…
There are several types there. But, this one seems to show up all the errors like missing semi’s…

BUT, for layout’s like playing with CSS on sites, Dreamweaver is good. It just does little for the programming end of things!

sean4fsu777 · November 23, 2018, 12:53am

Oh, I see. Thank you for letting me know about that. I had no idea that NetBeans was a very good IDE. Thanks again for letting me know about NetBeans!

ErnieAlex · November 23, 2018, 1:05am

You are very welcome! This is what we are here for!

PS: I use both. When designing the front-end, Dreamweaver lets you adjust CSS and layout with ease. And, for the coding NetBeans is what I use most of the time!