Web Programming TutorialsPDO and Prepared Statements

PDO and Prepared Statements

In the last sessions we learned to use PHP with MYSQL using mysqli methods as well as object oriented features. In this session of PDO and Prepared Statements tutorial we will learn to use one more method called PDO.
PDO stands for PHP Data Object. It is a PHP extension that was created to access databases and performs database tasks. It is very lightweight and extremely secure because you can use prepared statements that prevent SQL injections and other security threats. It is easier even more than mysqli methods.

So let’s start learning step by step.

  1. Create a new folder called PDO in htdocs folder which is in xampp folder located in C drive.
  2. Open a new notepad++ document and save it as index.php in the newly created PDO folder.
  3. The first step to deal with database is to connect with it. So write the following code in index.php.
  4. try
    {
    	//Connect to DB
    	$dbh=new PDO('mysql:host=localhost;dbname=company;charset=utf8','root','12345');
    }
    catch(PDOException $e)
    {
    	//Display Errors
    	echo $e->getMessage();
    }
    • Here, we are trying to connect the PHP page to MYSQL database.
    • Everything is done in a try catch block. A try catch block catches an error if any and accordingly does the task.
    • The code that can cause an error is called as risky code. This code is written inside try block. If an error occurs, it is said to thrown and is caught and handled by the catch block. The error message is shown in the catch block.
    • In the above code the statement
      $dbh=new PDO('mysql:host=localhost;dbname=company;charset=utf8','root','12345'); 

      establishes a connection between the php page and the MYSQL database.

    • In this statement we have made a PDO object that tells about the database used i.e. mysql, the host name i.e. localhost, database name i.e. company, character set which is optional, the username i.e. root and the database password i.e. 12345.
    • The catch block displays the error using echo statement. PDOException is an exception class that handles errors or exceptions related to PDO. And $e is the object of PDOException class.
    • The pre-defined function getMessage() returns the error message written in the PDOException class.
    • Now open the browser and write localhost/PDO in the address bar.
    • initial_output_after_DB_connection
      fig 1

    • The page will show nothing if connection is error-less and it will show the error message if any error occurs during the connection.
  5. Now write the following code to read the data from database. Please write the code inside try block so that it will throw the error if any:
  6. //SELECT DATA
    	
    	//create a query
    	$sth=$dbh->query("select * from employees");
    	
    	//Set fetch mode
    	$sth->setFetchMode(PDO::FETCH_OBJ);
    	
    	//show data in table
    	<h1>Employees</h1>
    	<table width="500" cellpadding="5" cellspacing="5" border="1">
    	<tr>
    	<th>ID#</th>
    	<th>First Name</th>
    	<th>Last Name</th>
    	<th>Department</th>
    	<th>Email</th>
    	</tr>
    	<?php while($row=$sth->fetch()):?>
    	<tr>
    	<td><?php echo $row->id;?></td>
    	<td><?php echo $row->first_name;?></td>
    	<td><?php echo $row->last_name;?></td>
    	<td><?php echo $row->department;?></td>
    	<td><?php echo $row->email;?></td>
    	</tr>
    	<?php endwhile;?>
    	</table>
    
    • We have written the above code to access or select data from employees table present in company database.
    • The code is written in 3 steps.
      1. Create a query
      2. Set fetch mode
      3. Display data in table
    • The data fetched by the select query is stored in the variable $sth.
    • The statement
      $sth->setFetchMode(PDO::FETCH_OBJ);

      sets the fetch mode using the function setFetchMode().

    • The value FETCH_OBJ of PDO allows the variable $sth to fetch data in the form of object.
    • An HTML table is used to display the selected data.
    • The while loop has slight change than that used with mysqli.
    • The while loop in the statement
      <?php while($row=$sth->fetch()):?>

      uses fetch() function to fetch data objects from $sth variable which is pointed by $row variable.

    • The variable $row then returns the id, first_name, last_name, department and email of each object pointed by it in a row.
    • The statement
      <?php endwhile;?>

      ends the while loop.

    • Now just reload the page. The table contents are shown below:
    • select_query_result
      fig 2

  7. We now know to fetch data from the table using PDO, now let us learn to insert data into the database.
    • Write the following code to insert data into the database using PDO below the code for selecting data:
    •   //INSERT DATA
      	
      	//Dummy Variables
      	$first_name='David';
      	$last_name='Jackson';
      	$department='Design';
      	$email='[email protected]';
      	
      	//Create Statement
      	$sth=$dbh->prepare("insert into employees(first_name,last_name,department,email) values(:first_name,:last_name,:department,:email)");
      	
      	//Bind Values
      	$sth->bindParam(':first_name',$first_name);
      	$sth->bindParam(':last_name',$last_name);
      	$sth->bindParam(':department',$department);
      	$sth->bindParam(':email',$email);
      	
      	//Execute
      	$sth->execute();
      
    • We have written the above code to insert an employee’s record in the employees table present in company database.
    • The code is written in 3 steps.
      1. Create a query
      2. Bind Values
      3. Execute the query
    • We are going to use prepared statement to insert data. Prepared statement is a statement where you declare parameters or place holders instead of actual values to be inserted in the table.
    • These place holders are then bound to the actual values. This simplifies the query and avoids any confusion.
    • In the above code we have created dummy variables that store value for each field of employees table of company database.
    • Next we have created the prepared statement in which we have fired the insert query.
    • The prepared statement is created using prepare() function.
    • The parameters inside the parenthesis after values keyword in the query are the placeholders. These are just the variables. They are written as :variable_name, for example: :first_name.
    • Later in the bind values step the values are bound to the placeholders using the bindParam() method.
    • Parameters in the bindParam() method are first the placeholder and then the actual value both separated by comma.
    • The next and last step is to execute the query. The execute() method does this work.
    • Now just reload the PDO page. Then open the phpmyadmin page in another tab by writing localhost/phpmyadmin in the address bar. Login and open the employees table in company database. The record inserted through the query above is added in the employees table as shown below by the arrow:
    • record_added_when_insert_query_is_fired
      fig 3

  8. Now if we want to change or update any field or fields in a row, we will use update query.
    • It has same steps like insert query.
    • The code is given below:
    • //UPDATE DATA
      	
      	//Dummy Variables
      	$id=4;
      	$last_name='Logan';
      	$department='Design';
      		
      	//Create Statement
      	$sth=$dbh->prepare("update employees set department=:department,last_name=:last_name where id=:id");
      	
      	//Bind Values
      	$sth->bindParam(':id',$id);
      	$sth->bindParam(':last_name',$last_name);
      	$sth->bindParam(':department',$department);
      		
      	//Execute
      	$sth->execute();
      
    • In the above code we are going to update the last name and department of the employee having id 4.
    • We have stored the id, last name and department in the dummy variables.
    • We have used prepared statement to fire the update query.
    • The bindParam() method is used to bind the placeholders in the update query to the actual values or variables containing the values.
    • Next step is just executing the prepared statement using the execute() method.
    • Now reload the PDO page and then open the employees table in the phpmyadmin database tool. You will see that the department and last name fields of employee with id = 4 has been changed. The output is shown below:
    • updated_record_with_id_4
      fig 4

    • You can compare record with id 4 in fig 3 and fig 4, you will notice the lastname changed from ‘White’ to ‘Logan’ and the department changed from ‘Marketing’ to ‘Design’.
  9. Now let us learn to delete a record from table using PDO and prepared statement.
    • Procedure to delete a record is similar to insert and update.
    • In this case we will try to delete a record with id = 6 from employees table.
    • The code is shown below:
    • //DELETE DATA
      	
      	//Dummy Variables
      	$id=6;
      			
      	//Create Statement
      	$sth=$dbh->prepare("delete from employees where id=:id");
      	
      	//Bind Values
      	$sth->bindParam(':id',$id);
      			
      	//Execute
      	$sth->execute();
      
    • Here, we want to delete the record with id = 6, so we have created a dummy variable $id that holds 6.
    • The prepared statements used to fire the delete query. If you forget to write the where clause in the delete query, all the records will be deleted.
    • Using bindParam () method the id 6 is bound to placeholder :id.
    • Next the prepared statement is executed using the execute() method.
    • Now reload the PDO page and open the employees table in the phpmyadmin database tool. You will see there is no record with id 6 in it. It is shown in the figure below:
    • deleted_record_with_id_6
      fig 5

Thus we finished studying PDO method to connect PHP and MYSQL and prepared statements in this PDO and Prepared Statements tutorial.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Exclusive content

- Advertisement -

Latest article

21,501FansLike
4,106FollowersFollow
106,000SubscribersSubscribe

More article

- Advertisement -