Collecting multiple records from mysql table by using PHP PDO Parameterized Query

Adding Bootstrap style
Displaying Multiple columns of a table
Displaying date in format
Parameterized Query using PDO
Using String as Parameter
Using Multiple String parameters
Using integer as parameter
Dynamic Query Execution for Multiple Students Using Prepared Statements
Insert Query with Parameters
SQL Injection
Example using pdo_admin table

We will explore different ways to fetch and display data, such as using simple queries, applying styles with Bootstrap, and handling larger datasets with pagination.

For example list of all states of USA from the country list.

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>
	<td>$row[gender]</td></tr>";
}
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

Parameterized Query using PDO 🔝

While executing any query, we can pass the parameters separately to prevent unwanted data or malicious code to be part of our query. This is required to prevent injection attack through Query.

In all real life situation we collect data from different sources like forms, query string, sessions , cookies etc and they became part of the SQL we use. In such situations it is not advisable to use such data directly in our queries.

Parameterized query in PHP PDO using bindParam() in sending data separately to MySQL database

Here is one example where we have directly used data in our query ( through one variable $class ) which is not a good practice.

$sql="SELECT * FROM student WHERE class='$class'";

The value stored in the variable $class could be vulnerable to code injection attacks if directly included in the query. To prevent this, we use parameterized queries with named placeholders to bind the data. This ensures that the database treats the input strictly as data, not part of the SQL query. This approach effectively mitigates SQL injection risks by separating the query structure from user input.
Example of SQL Injection of code through Parameter

Using String as Parameter 🔝

We will use one variable $class and pass it as parameter to our query.

require "config.php";// database connection

$class='Three'; // Data can be changed.
$query="SELECT * FROM student WHERE class=:class";
$step = $dbo->prepare($query);
$step->bindParam(':class', $class,PDO::PARAM_STR,10);
$step->execute();
$step = $step->fetchAll();

echo "<table><tr><th>ID</th><th>Name</th>
  <th>Class</th><th>Mark</th></tr>";

  foreach($step as $row){
echo "<tr><td>$row[id]</td><td>$row[name]</td>
  <td>$row[class]</td><td>$row[mark]</td></tr>";
}
echo "</table>";

Using Multiple String parameters 🔝

We will use LIKE Query along with the above query.

$class='Three'; // Data can be changed.
$name='%Max%';
$query="SELECT * FROM student WHERE class=:class AND name LIKE :name";
$step = $dbo->prepare($query);
$step->bindParam(':class', $class,PDO::PARAM_STR,10);
$step->bindParam(':name', $name,PDO::PARAM_STR,10);
$step->execute();
$step = $step->fetchAll();

echo "<table><tr><th>ID</th><th>Name</th>
  <th>Class</th><th>Mark</th></tr>";

  foreach($step as $row){
echo "<tr><td>$row[id]</td><td>$row[name]</td>
  <td>$row[class]</td><td>$row[mark]</td></tr>";
}
echo "</table>";

Output

ID	Name	Class	Mark
2	Max Ruin Three	85

Using integer as parameter 🔝

We will use one variable id and pass the data to query to display details of that record only.

$id=3;
$query="SELECT * FROM student WHERE id=:id";
$count=$dbo->prepare($query);
$count->bindParam(":id",$id,PDO::PARAM_INT,3);

if($count->execute()){
$row = $count->fetch(PDO::FETCH_OBJ);
//print_r($row);
echo "<hr><br>id = $row->id";
echo "<br> name =$row->name<br>";
echo "<br> class =$row->class<br>";
}

Output

id = 3
name =Arnold

class =Three

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

Dynamic Query Execution for Multiple Students Using Prepared Statements 🔝

Inside the foreach loop, the prepared statement is executed for each student by binding the student's name to the :name placeholder using bindParam(). The query is then executed, and the resulting row is fetched and displayed in the table.

//$sql = "SELECT * FROM student WHERE id=:id";
 $sql = "SELECT * FROM student WHERE name=:name";
 $stmt = $dbo->prepare($sql); // Prepare the SQL statement   

 // Array of student records to be collected based on id OR name 
 $students = [ ['id'=>1,'name'=>'John Deo'],['id'=>2,'name'=>'Max Ruin'],
		['id'=>3,'name'=>'Arnold'] ];
 echo "<table>";
 // Loop through each student and execute the prepared statement
 foreach ($students as $student) {
  //$stmt->bindParam(':id', $student['id'], PDO::PARAM_INT, 3);
  $stmt->bindParam(':name', $student['name'], PDO::PARAM_STR,10);
  $stmt->execute();
  $row = $stmt->fetch(PDO::FETCH_OBJ);
  //print_r($row);
  echo "<tr ><td>$row->id</td><td>$row->name</td><td>$row->class</td>
     <td>$row->mark</td><td>$row->gender</td></tr>";
 }
echo "</table>";

Insert Query with Parameters 🔝

Data as entered by users in a form is to be stored in a database table. Here we have to use parameterized query to add records to table.
Check how Parametrized query used while inserting data to table

SQL Injection 🔝

While receiving data (from unknown sources ) and using the same inside query, we have to take care of injection attacks.
Example of SQL code Injection through Parameter

Example using pdo_admin table 🔝

Here is an example with added checks( by using parameterized query ) 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>";
?>

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

Paging of records

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.
Paging of records

Podcast on MySQL database management using PHP PDO

Download Zip file to test your PHP PDO script

Sample Student table SQL dump
PDO References Collecting single record from table Adding record

Subscribe to our YouTube Channel here

Multiple records by using Parameterized Query