Multiple records by using Parameterized Query

PHP PDO & MYsQL

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

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.
iduseridpasswordnamestatus
1admin1test1AdminT
2admin2test2AdminF
3admin3test3AdminT
6admin4test4AdminT
7admin5test5AdminA
8adm#in6test6AdminA
9admin7test7AdminA
10admi'n8test8AdminA
12admin9test9AdminA
13Admin10test10AdminA

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


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com











    PHP video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles Contact us
    ©2000-2025   plus2net.com   All rights reserved worldwide Privacy Policy Disclaimer