PHP Data Object: How to

With reference to previous post on PDO. Here is a small tutorial and explanation for connecting to database using PDO object. I recommend using PDO as it is more structured way to connect to database rather than doing it by self plus the general functions like mysql_connect are to be deprecated in upcoming versions of PHP. There is another class called mysqli, if you are using mysql you can use it but again if in case you have to change your database, PDO is best.

Required: PHP 5.0+

Using:

<?php
$user = 'user';
$pass = 'mypass';

$color = 'red';

/** PDO is a class for php data object in php. First argument takes the 
  "typeofdatabase:host=localhost;dbname=db"
* typeofdatabase here is mysql, which can postgre, mssql etc.
* host is in general localhost or you use user with which you connect to 
  database.
* dbname is the name of database you want to connect with.
* return the object of the database connection.
**/
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

/** Begin transaction, which is very general term in database. Actually 
   any of CRUD is called transaction.
**/
$dbh->beginTransaction();

$query = "SELECT name FROM fruit WHERE color = ?";    // Query

/** Prepares the query/statement for execution and return the 
   object of statement.
**/
$sth = $dbh->prepare($query);

/** Binds the variables to the prepared query
* first argument takes the position of question mark to which 
  the variable has to be bound.
* second argument is the variable which is to be bound.
* type of variable in the query. Like here the color is string, 
  it can be int etc. and for it there are predefined constants. 
  Its optional.
**/
$sth->bindParam(1, $color, PDO::PARAM_STR);

$sth->execute();     //Query execution.

/** Fetch method is used to fetch each row. There are other methods 
  also like fetchAll
* it takes predefined constant in PDO as argument, FETCH_BOTH returns 
  the row with number and name indexes. Like $result['name'] and $result[0].
* it returns the row array.
**/
while($result = $sth->fetch(PDO::FETCH_BOTH)){
     echo $result['name'];
}

?>

Explanation:

Most of the things which are used in general are explained in the code snippet. But I would still recommend PHP: PDO Manual. Look at the other methods and predefined constants, specially understand the difference between bindParam method and bindValue method. For the record bindParam is better way because it binds the variable at the time of execution of query.

Look at mysqli also for a method called bind_result. Which is quite good, you can extend this PDO class and include this method in it.

Hope you will find this post useful.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.