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.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>";
$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>";
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>";
SELECT date_format( dt, '%m/%d/%Y %T' ) as my_date FROM dt_tb
$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. 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>";
$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
$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
: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>";
<?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>";
?>
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 |
Download Zip file to test your PHP PDO script