PDO Collecting multiple records from mysql table PHP

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.

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.


$sql="select state from table_name WHERE state='USA'";

echo "<table>";
foreach ($dbo->query($sql) as $row) {
echo "<tr ><td>$row[state]</td></tr>";
}
echo "</table>";

Let us add some style command to this code like this.


$i=1;
echo "<table class='t1'>";
foreach ($dbo->query($sql) as $row) {$m=$i%2;
echo "<tr class='r$m'><td>$row[state]</td></tr>";
$i=$i+1;}
echo "</table>";

Adding Bootstrap style

Learn more about Bootstrap here


$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

Precaution while passing variables to a query

When we receive a variable from a web-form or from query string or from other un-known sources , it is better to use bindParam to check and use the variable inside query. Here is an example with added checks to above code.

<?Php
require "config.php";// database connection
$name='Admin';
$query="select * from pdo_admin where name=:name";
$step = $dbo->prepare($query);
$step->bindParam(':name', $name,PDO::PARAM_STR,50);
$step->execute();
$step = $step->fetchAll();

echo "<table>";

echo "<tr><th>id</th><th>userid</th><th>password</th><th>name</th><th>status</th></tr>";

foreach ($step 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>";
?>

If you are using numbers ( integers ) variables by taking from query string then you have to change it to integer first before using with bindPram
$start=(int)$start; // Now $start is an integer variable

Output with limited records are displayed here.

id	userid	password	name	status
1	admin1	test1	Admin	T
2	admin2	test2	Admin	F
3	admin3	test3	Admin	T
6	admin4	test4	Admin	T
7	admin5	test5	Admin	A
8	adm#in6	test6	Admin	A
9	admin7	test7	Admin	A
10	admi'n8	test8	Admin	A
12	admin9	test9	Admin	A
13	Admin10	test10	Admin	A

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.

This is called paging of records.

Download Zip file to test your PHP PDO script

Collecting more than one record from table

Adding Bootstrap style

Displaying Multiple columns of a table

Displaying date in format

Precaution while passing variables to a query