The basic unit of structure in SQL is called a table because it’s usually displayed in a tabular format. HTML5 also has a table structure, which is ideal for outputting SQL data. ContactTable.php, displays the contact information inside an HTML table.
Tables are a very common way to output SQL results. There’s one big difference between table output and the basic version of a table. In a table, you have a separate row containing field names. Here’s the code:
<!DOCTYPE html>
<html lang = "en-US">
<head>
<meta charset = "UTF-8">
<title>contact.php</title>
<style type = "text/css">
table, th, td {border: 1px solid black};
</style>
</head>
<body>
<p>
<?php
try {
$con= new PDO('mysql:host=localhost;dbname=dbName', "user", "pwd");
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$query = "SELECT * FROM contact";
//first pass just gets the column names
print "<table> n";
$result = $con->query($query);
//return only the first row (we only need field names)
$row = $result->fetch(PDO::FETCH_ASSOC);
print " <tr> n";
foreach ($row as $field => $value){
print " <th>$field</th> n";
} // end foreach
print " </tr> n";
//second query gets the data
$data = $con->query($query);
$data->setFetchMode(PDO::FETCH_ASSOC);
foreach($data as $row){
print " <tr> n";
foreach ($row as $name=>$value){
print " <td>$value</td> n";
} // end field loop
print " </tr> n";
} // end record loop
print "</table> n";
} catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
} // end try
?>
</p>
</body>
</html>
You might be confused that a table is being used here. Tables aren’t evil: They just aren’t designed to be a page layout mechanism. Tables, however, are designed to display tabular data, and the result of a data query is pretty much the definition of tabular data. You can (and should) still use CSS for specific layout details of the table. Tables are fine when used to present data.
This code is still very similar to the basic contact.php program. It extracts data from the database exactly the same way. The main difference is how field names are treated. The field names will go in table headings, and only the values are printed from each row. To make this work, follow these steps:
Build a normal MySQL connection.
Begin with the standard connection. Don’t worry about formatting until you’re reasonably certain that you can read data from the database.
$con = new PDO('mysql:host=localhost;dbname=dbName', "user", "pwd"); $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);Determine your query.
Create a query that will produce a table, view, or search result. Store it in a variable so you can use it. (You'll use the same query twice in this exercise.)
$query = "SELECT * FROM contact";
Print the table tag before extracting any results.
All the query data will be displayed inside the table, so print the table tag before you start printing anything that should go inside the table.
print "<table> n";
Make a first pass to extract field names.
You're actually going to query the database twice. The first time, you simply want the field names, which you'll use to build the table headers, so it only needs one row.
$result = $con->query($query); //return only the first row (we only need field names) $row = $result->fetch(PDO::FETCH_ASSOC);
The fetch method pulls the next available record from the $result variable. You want the record data in associative array format, so pass the PDO::FETCH_ASSOC constant to indicate this.
Print the field names as table headers.
Now that you have a single record, walk through that record as an associative array and use the $field values to print out field names.
print " <tr> n"; foreach ($row as $field => $value){ print " <th>$field</th> n"; } // end foreach print " </tr> n";Make a second query.
Now execute the query again with the $con->query() method. This time, you're doing an ordinary query with multiple results. Don't forget to set the fetch mode to associative array.
//second query gets the data $data = $con->query($query); $data->setFetchMode(PDO::FETCH_ASSOC);
Use nested loops to print out data elements.
Use the ordinary nested-loops trick to print out all of the data elements with each record taking up one row of the HTML table.
foreach($data as $row){ print " <tr> n"; foreach ($row as $name=>$value){ print " <td>$value</td> n"; } // end field loop print " </tr> n"; } // end record loop