How to Retrieve Data from Database Table using PDO Query

Submitted by argie on Wednesday, November 20, 2013 - 14:32.
SQL Attack is rampant now a days, this is the way of hacking a site using SQL injection. Reading this tutorial will teach you on how to protect your queries from SQL injection attack using PDO Query. PHP Data Objects (PDO) Query executes an SQL statement in a single function call, returning the result set (if any) returned by the statement as a PDOStatement object. This is code will help you on how to retrieve data from Database table using PDO query. To start this tutorial lets follow the steps bellow:

Creating Our Database

First we are going to create our database which stores our data. To create a database: 1. Open phpmyadmin 2. Then create database and name it as "pdo_ret". 3. After creating a database name, click the SQL and paste the following code. 
  1. CREATE TABLE IF NOT EXISTS `members` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `fname` varchar(100) NOT NULL,
  4.   `lname` varchar(100) NOT NULL,
  5.   `age` int(5) NOT NULL,
  6.   PRIMARY KEY (`id`)
  7. ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Creating our Database Connection

Next step is to create a database connection and save it as "connect.php". In this Step, we will write our connection script in PDO format. 
  1. <?php
  2. /* Database config */
  3. $db_host		= 'localhost';
  4. $db_user		= 'root';
  5. $db_pass		= '';
  6. $db_database	= 'pdo_ret'; 
  7.  
  8. /* End config */
  9.  
  10. $db = new PDO('mysql:host='.$db_host.';dbname='.$db_database, $db_user, $db_pass);
  11. $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  12. ?>

Creting our Display Page

In this steps we will write our script that retrieve our data from database table, we will do that by using the pdo query. Save this page as "index.php". 
  1. <table border="1" cellspacing="0" cellpadding="2" >
  2. <thead>
  3. 	<tr>
  4. 		<th> First Name </th>
  5. 		<th> Last Name </th>
  6. 		<th> Age </th>
  7. 	</tr>
  8. </thead>
  9. <tbody>
  10. 	<?php
  11. 		include('connect.php');
  12. 		$result = $db->prepare("SELECT * FROM members ORDER BY id DESC");
  13. 		$result->execute();
  14. 		for($i=0; $row = $result->fetch(); $i++){
  15. 	?>
  16. 	<tr class="record">
  17. 		<td><?php echo $row['fname']; ?></td>
  18. 		<td><?php echo $row['lname']; ?></td>
  19. 		<td><?php echo $row['age']; ?></td>
  20. 	</tr>
  21. 	<?php
  22. 		}
  23. 	?>
  24. </tbody>
  25. </table>
Thats the steps oin how to retrieve data from database table. Hope this code will help you to prevent sql attack. For thus programmer that is not using PDO query, upgrade your query now don't wait that someone will attack your site.

Tags

Add new comment

About text formats
  • 728 views