MrBool
You must be logged in to give feedback. Click here to login
[Close]

You must be logged to download.

Click here to login

[Close]

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

[Close]

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

How to make database operations with PDO in PHP

In this article we will learn how to make some database operations with PDO in PHP. We will see how to connect to the database and also perform a CRUD.

[close]

You didn't like the quality of this content?

Would you like to comment what you didn't like?

The PDO (PHP Data Objects) defines an interface for connecting to database light and consistent for PHP. There is the possibility to use different drivers which implement the connection PDO for various types of databases.

The PDO is an abstraction layer to access data, the same functions used to manipulate data or retrieve information from the database will be the same regardless of the database that is being used.

PDO in PHP

Figure 1: PDO in PHP

There are three API's to connect to the database in PHP, they are:

  • mysql
  • mysqli - MySQL improved
  • pdo - PHP Data Objects

In several articles we have seen how to connect using the mysql API, but for those who do not know how to use, we will see an example of connection with this API.

Listing 1: Connection API mysql

<?php
	# Connection
	mysql_connect('localhost', 'username', 'password') or die('Unable to connect to database: ' . mysql_error());
	# Choosing database
	mysql_select_db('yourDatabase') or die('Unable to select database');
	# Performs a query on table
	$query = "SELECT * from yourTable";
	$result = mysql_query($query) or die('Query Failed: ' . mysql_error());
	# Filter through the lines query
	while ($row = mysql_fetch_object($result)) {
	    echo $row->name;
	}  

?>

As we can see, the above code is very simple and easy to use, but the API was discontinued.

The PDO is a more robust API that can be used regardless of the driver you are using.

Many people are a little intimidated to use the PDO, not because it is difficult to use an API, because it is not, but because the mysql API is very easy to use and people end up getting used to it.

But let's see how it really is easy to use it.

Connecting to database

First we will see how to connect using the PDO. We will create a new class instance and specify the driver that we use, in case mysql, database name, username and password.

Listing 2: Creating connection with PDO

<?php
$conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
?>

As we saw in Listing 2, is also very simple to connect using the PDO, but as with every connection, you need to handle errors, if it happens to some connection error, show it to the user.

To do this we use the try .. catch, used in object-oriented languages​​.

Listing 3: Handling Errors in connection

<?php
try {
	$conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
?>
The standard error of the PDO is PDO:: ERRMODE_SILENT, but in our code we used the PDO:: ERRMODE_EXCEPTION and below I will list the options we have: · PDO::ERRMODE_SILENT · PDO::ERRMODE_WARNING · PDO::ERRMODE_EXCEPTION

Fetch

Now we do start using select with PDO, searching for some results. There are two basic ways of doing this: Querying and Executing and it is these two that we will look further ahead.

Listing 4: Connecting to database using PDO

/*
 * Anti-Pattern methods
 */
 
# Connect
mysql_connect('localhost', 'username', 'password') or die('Could not connect: ' . mysql_error());
 
# Choose a database
mysql_select_db('someDatabase') or die('Could not select database');
 
# Perform database query
$query = "SELECT * from someTable";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
 
# Filter through rows and echo desired information
while ($row = mysql_fetch_object($result)) {
    echo $row->name;
}

Although this works, note that we're still escaping user data manually with PDO::quote method. Think of it as a method, more or less, equivalent to mysql_real_escape_string, in PDO.

In situations where you are linked to user-supplied data to an SQL query, it is strongly recommended that you instead use this, use prepared statements.

If your SQL queries are not dependent on form data, the method of consultation is a useful choice, and makes the process loop through the results as easy as a foreach statement.

Listing 5: Using Prepared Statements

<?php 
/*
 * Best practice using Prepared Statements
 * 
 */
 
$id = 5;
try {
    $conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    
     
    $stmt = $conn->prepare('SELECT * FROM myTable WHERE id = :id');
    $stmt->execute(array('id' => $id));
 
    while($row = $stmt->fetch()) {
        print_r($row);
    }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

 ?>

In this example, we are using the method of preparation to literally prepare the query before the user data are attached. With this technique, SQL injection is virtually impossible because no data are never inserted in the SQL query itself.

Instead, we named parameters (:id) to specify placeholders.

We executed the query, passing an array that contains the data to be linked to these placeholders.

 $stmt->execute(array('id' => $id)); 

Another approach, but perfectly acceptable, would be to use the method bindParam just like that:

Listing 6: Using the bindParam method

$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();

After calling the execute method, there are different ways to get the data: a matrix (the default), an object, etc.. In the above example, the default answer is: PDO::FETCH_ASSOC, which can be easily replaced if necessary.

Listing 7: Specifying the interaction

while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
    print_r($row);
}

In the above code, we specify that we want to interact with the result set of the best object-oriented mode. Below I will list some of the options available for this interaction.

  • PDO::FETCH_ASSOC: Returns an array.
  • PDO::FETCH_BOTH: Returns an array, indexed by both column-name, and 0-indexed.
  • PDO::FETCH_BOUND: Returns TRUE and assigns the values of the columns in your result set to the PHP variables to which they were bound.
  • PDO::FETCH_CLASS: Returns a new instance of the specified class.
  • PDO::FETCH_OBJ: Returns an anonymous object, with property names that correspond to the columns.

But there is still a problem with what we've done until now in the code, we are not giving any feedback to the user, for example, when no results are returned.

In the code below will fix this lack of feedback and give feedback to the user.

Listing 8: Giving feedback to user

<?php
$stmt->execute(array('id' => $id));
 
# Get array containing all of the result rows
$result = $stmt->fetchAll();
 
# If one or more rows were returned...
if ( count($result) ) {
    foreach($result as $row) {
        print_r($row);
    }
} else {
    echo "No rows returned.";
}

?>

So our complete code can be seen in Listing 9.

Listing 9: Full source code of our example

$id = 5;
try {
  $conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
  $stmt = $conn->prepare('SELECT * FROM myTable WHERE id = :id');
  $stmt->execute(array('id' => $id));
 
  $result = $stmt->fetchAll();
 
  if ( count($result) ) { 
    foreach($result as $row) {
      print_r($row);
    }   
  } else {
    echo "No rows returned.";
  }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

Codes for performing a CRUD.

In addition to connecting to the database and return records from the database is extremely important to know how to perform the main operations, they are Insert, Edit and Delete, also known as CRUD.

First let's see how can we insert data into the database with PDO.

Listing 10: Inserting data with PDO

try {
  $pdo = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
  $stmt = $pdo->prepare('INSERT INTO myTable VALUES(:name)');
  $stmt->execute(array(
    ':name' => 'John Carter'
  ));
 
  # Affected Rows?
  echo $stmt->rowCount(); // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();

If our need is update an existing record, we will use the code in Listing 11.

Listing 11: Updating Data

$id = 5;
$name = "Joseph Climber";
 
try {
  $pdo = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
  $stmt = $pdo->prepare('UPDATE myTable SET name = :name WHERE id = :id');
  $stmt->execute(array(
    ':id'   => $id,
    ':name' => $name
  ));
   
  echo $stmt->rowCount(); // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}

Until now we have seen how to connect, list, insert and update, the only thing that is missing to get our full CRUD is deleting records, which we will see in Listing 12.

Listing 12: Deleting Records

$id = 5; // Id Number of a record
 
try {
  $pdo = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
  $stmt = $pdo->prepare('DELETE FROM myTable WHERE id = :id');
  $stmt->bindParam(':id', $id); // this time, we'll use the bindParam method
  $stmt->execute();
   
  echo $stmt->rowCount(); // 1
} catch(PDOException $e) {
  echo 'Error: ' . $e->getMessage();
}

We finished this article here about PDO, hope has been clear and you have learned how to work with PDO.



Front-end developer and WebEditor of MrBool.com. I work in the web design area for over 4 years. For freelance work visit my portfolio: www.ricardoarrigoni.com.br or if you prefer send me an email.

What did you think of this post?

Did you like the post?

Help us to keep publishing good contents like this.

SUPPORT US

funded

remaining

[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually � in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits � in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login