WE can collect more than one record from a table by using sql query. For example list of all states of USA from the country list. Our table have two columns one is country and other is associated states.
Displaying records from sample tables using SELECT query in PHP MySQL using PDO functions
We have already connected to database by using PDO. After successful connection to MySQL database we can use this script to display records from the table.
require "config.php";// database connection
$sql="SELECT * FROM student ORDER BY mark DESC ";
echo "<table><tr><th>ID</th><th>Name</th>
<th>Class</th><th>Mark</th></tr>";
foreach($dbo->query($sql) as $row){
echo "<tr><td>$row[id]</td><td>$row[name]</td>
<td>$row[class]</td><td>$row[mark]</td></tr>";
}
echo "</table>";
$sql="select state from table_name WHERE state='USA'";
echo "<table class='table table-striped'>
<tr class='info'><th>State</th> </tr>";
foreach ($dbo->query($sql) as $row) {
echo "<tr ><td>$row[state]</td></tr>";
}
echo "</table>";
Displaying Multiple columns of a table
We can display more than one column of the database table. Here we will keep column name in table header and keep the record data in normal table cell.
require "config.php";// database connection
$count="select * from pdo_admin where name='$name'";
echo "<table>";
echo "<tr><th>id</th><th>userid</th>
<th>password</th><th>name</th><th>status</th></tr>";
foreach ($dbo->query($count) as $row) {
echo "<tr><td>$row[id]</td><td>$row[userid]</td>
<td>$row[password]</td><td>$row[name]</td><td>$row[status]</td></tr>";
}
echo "</table>";
Displaying date in format
SELECT date_format( dt, '%m/%d/%Y %T' ) as my_date FROM dt_tb
We have displayed limited number of records here , but there may be hundred or more records in a table. Displaying all in a single page will not give better user experience. We can break the total records and display limited records and then give user choice to browse to different section of the records.