How we can display records using PDO MySQL Connection in PHP

display records using PDO Connection


Display records using PDO:  Hi readers, here in this tutorial we will give you the details regarding how to retrieve or display records from MySQL using PDO base class. Here we will provide you the Step-by-Step guide to display records using PDO MySQL Connection in PHP Script. Further, Screen Shot view makes your concept more clear.

 

Lets begin the Step-By-Step guide to display records using PDO MySQL Connection :

 

Step 1: Create a connection using PDO class:


In this step first we create a connection using PDO base class by creating an instance of PDO and later on checking condition if any error then it is handled by catch block otherwise established connection Successfully.

You can view the tutorial by clicking on the link below on how we can create a connection using PDO MySQL :

Create a PDO Connection

Step 2: Write a query to display records:


Here in this step we simply write the SQL query to display records from MySQL table using PDO just as we do in the SQL command prompt.

SELECT statement is generally used to select records. Below is the brief description of SELECT statement to make your concept clear.

Select Statement:

A Select statement is used to retrieve data (get data)from a table in the database and where clause is used to select the specific records which have matched the condition under where clause:

select column_name(s) from table_name
where column=value;

 

Below is the code snippet of select query : 

//query to select records
$sql="select * from customer";

Step 3: Execute the Query:


Here in this step we simply execute the above query by using the query() PDO method. 

query() method  of PHP Data Object (PDO) is used to execute an SQL Statement that returns a result set as a PDOStatement Object or fails on failure.

Below is the code to execute the above query and fetch the records of result set :

//use exec() beacuse no result is returned
 $result= $conn->query($sql);

//fetch each result
$result->setFetchMode(PDO::FETCH_ASSOC);
echo "List of records of customer";

Step 3: Close the Connection:


It’s always a good practice to close the connection after completing the task. Simply assign “null” value to the initiated object created by PDO base class.

//close the connection
$conn=null;

Full Source code to display records using PDO MySQL Connection:


<?php
$host="localhost";
$username="root";
$password="";
$dbname="pdo";
try
{
//create a connection
$conn=new PDO("mysql:host=$host;dbname=$dbname",$username,$password);

//set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

//query to select records
$sql="select * from customer";

//execute the above query using query()method
$result= $conn->query($sql);
//fetch each result
$result->setFetchMode(PDO::FETCH_ASSOC);
echo "List of records of customer";
}
catch(PDOException $e)
{
 echo "Connection Failure". $e->getMessage();
}
?>
<!Doctype html>
<html>
<head>
<title>Display Records using PDO</title>
<link rel="stylesheet" href="css/bootstrap.min.css">
</head>
<body>
<div id="container">
<h1>Customer Details</h1>
<table class="table table-bordered table-condensed">
<thead>
<tr>
<th>ID</th>
<th>Customer Name</th>
<th>customer Email-Id</th>
<th>CustomerPassword</th>
<th>Mobile No</th>
</tr>
</thead>
<tbody>

<?php while($row=$result->fetch()):?>
<tr>
 /* getting values by removing special characters using "htmlspecicalchars()" function
 from each field of table as well as display in each cell under while loop */

<td><?php echo htmlspecialchars($row['id'])?></td>                     
<td><?php echo htmlspecialchars($row['custName'])?></td>
<td><?php echo htmlspecialchars($row['custemail'])?></td>
<td><?php echo htmlspecialchars($row['password'])?></td>
<td><?php echo htmlspecialchars($row['mobileNo'])?></td>
</tr>
<?php endwhile; ?>

</tbody>
</table>
</body>
</html>

Screen Shot View:


screenshot view of MySQL database “pdo” :
display records using PDO
display records using PDO
Result of display records using PDO MySQL Connection:

display records using PDO
display records using PDO

 

                                                                     display records using PDO in PHP


I hope you would like this tutorial on how we can retrieve or display records using PDO MySQL Connection in PHP !

Leave a Comment