Top

Follow me and receive all the latest free scripts:

By Email:

Categories
Most Popular Posts

PDO tutorial - Connecting MySQL with PDO

Published December 10, 2014 by , category Database

PDOPDO TutorialMySQLPHP

Use PHP's PDO for database access

Connecting people!

Here is the connection type to a MySQL server. Note that the data connection to the server is established when the PDO object is instantiated:

// Server connection
$dns = 'mysql:host=localhost;dbname=test';
$user = 'root';
$password = 'pass';
$bdd = new PDO($dns, $user, $password);

The DNS, it is in some way the entry point to access our database, it starts with the database engine code (we call it the engine sometimes). In our case it is mysql:

Then there is the server address: host=localhost

Followed by the name of the database: dbname=test

And sometimes there is also the port: port=3606, when the port is specified, the variable $dns looks like this:

// DNS where the port is specified
$dns = 'mysql:host=localhost;dbname=test;port=3606';

When the port is the one used by default by the database engine, specify it is optional. In our case the database engine is MySQL, therefore specify the port 3606 is useless (this is the default port).

Access to the server is specified in a string (the DNS), settings are expressed under the form parameter=value separated by semicolons. In our case: "mysql:host=localhost;dbname=test"

Then the 2nd and 3rd parameters are simply the MySQL login / password, in this example it is assumed that you have created a user root with the password pass having the access rights to the database test.

Error raised by PDO

PDO is object-oriented; it throws exceptions in case of problems. Lifted errors are PDOException, here are the different types of errors that you may encounter when you instantiate a PDO.

Error "could not find driver"

This error occurs if you have misinformed the database engine in the DNS or if the selected driver is not supported by your server. In the case of MySQL drivers, it is usually enabled by default on the majority of the hosting (and of course enabled by default on W/Mamp).

Error "Unknown MySQL server host"

This error occurs when the server name is misinformed / unavailable (MySQL in our case). In some "big" hosting companies, the server name is generally not localhost.

Error "Can't connect to MySQL server"

In the case of remote access (the MySQL server is not on the same machine as the Web server), you will get this message if the server is down / unavailable, or if the server you try to access is not a MySQL database server.

The error message usually appears after a certain time (long enough) of loading. It may happen that your web server starts a timeout before the error occurs (your page waits for the response from the data server, but it took too long to respond, suddenly your page will crash).

Error "Unknown database <db_name>"

In this case the name of the database is incorrect, or worse, the database does not exist...

Error "Access denied for user"

A classic, you do not have the right to access the server. Either your username or password, or both are misinformed.

Intercept connection errors with a try { catch }

The try / catch block is very convenient to catch this type of error (we call this kind of error Exception):

// Server connection
try {
  $dns = 'mysql:host=localhost;dbname=test';
  $user = 'root';
  $password = 'pass';
  $connection = new PDO( $dns, $user, $password );
} catch ( Exception $e ) {
  echo "Connection to MySQL impossible: " . $e->getMessage();
  die();
}

The die() function is aptly named; it will kill the PHP script (all lines after the die will never be executed).

It is strongly recommended to create a php file (for example connection.php) containing these lines. Indeed, if one of your pages needs an access to the database, you only need to include this file using require_once.

require_once('connection.php');

Send queries

After connecting to the MySQL server with our PDO object, we can start sending queries to the server. There are several ways to send queries with PDO.

There are 2 types of queries:

To test the following queries I use this database:

CREATE TABLE IF NOT EXISTS `countries` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET latin1 NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;


INSERT INTO `countries` (`id`, `name`) VALUES
(1, 'Belgium'),
(2, 'France'),
(3, 'Germany'),
(4, 'Norway'),
(5, 'Portugal'),
(6, 'China');

SELECT statement

To select recordings, we use the method query ($query).

// We establish the connection
require_once('connection.php');
 
// We send the query
$select = $bdd->query("SELECT * FROM countries");

The variable $select now contains the result of the query, but in a somewhat unusual form: a PDOStatement. You can go check the documentation for more information about this class here.

This object contains the data server response from our query. This object will allow us to manage the display of received data, for that there are several methods:

With setFetchMode / fetch

PDO gives us the freedom to use the response to the format we want, we can say that we want to process data received as arrays, or typed objects, etc... As we begin, we will treat the records as simple objects.

So just after sending the query (or before whatever), we will "set" our PDOStatement object that gives us the records as objects:

// return a result set as an object
$select->setFetchMode(PDO::FETCH_OBJ);

Now our variables $select contains an object for each record obtained, to process all the results we will use the loop WHILE.

while( $result = $select->fetch() ) {
  echo '<p>' . $result->id . ' ' . $result->name . '</p>';
}

Normally you will see the 6 countries displayed.

If the answer does not contains results, $select->fetch() will return NULL/FALSE. (And so the execution of the loop will be interrupted).

If you make a query that should return only one row, you do not need to use a loop to process the results, but still use a condition to avoid an unsightly error message when the display:

// One row result
$result = $select->fetch();

// We test here the variable $result, in case it's empty
if ($result) {
  echo '<p>' . $result->id . ' ' . $result->name . '</p>';
}
// If the query doesn't return result
else {
  echo 'No result';
}

Method fetch(PDO::FETCH_OBJ)

Small variation with a line less, in this example, the retrieval format (Format Object) is specified at the moment of the fetch():

while( $result = $select->fetch(PDO::FETCH_OBJ) ) {
  echo '<p>' . $result->id . ' ' . $result->name . '</p>';
}

Method fetchAll

This method will convert our object results in an array of objects, and then we treat the array as a classic table. This application case is mostly used to treat results lists (when we expect many results):

// We transform results in a table of objects
$create = $select->fetchAll(PDO::FETCH_OBJ);
 
// We treat the table $create
while( $result = next($create) ) {
  echo '<p>' . $result->id . ' ' . $result->name . '</p>';
}

I did not do performance testing on these methods, for my part I like the last method because it potentially allows me to manipulate the table... Otherwise I use the previous method when I expect a single result.

What are those weird characters ???

If you put special characters in any of the displayed data, it is the drama.

It is pointless to check the document format; it does not come from it .

Indeed, during an exchange with the web server, encoding transmission is not necessarily UTF8!

To correct / force that we have 2 solutions:

Manage the encoding of the connection

To force the exchange in UTF8 with MySQL, we must send a request to the server to explain that we are going to communicate with him in UTF8, in a standard way, it happens at the time of the connection, and for our happiness, the PDO object is planned for this.

Indeed, the line:

$connection = new PDO( $dns, $user, $password );

...allows us to add a fourth parameter to specify connection options, such as a query to execute at the time the connection is established, this gives:

// Server connection
try {
  $dns = 'mysql:host=localhost;dbname=test';
  $user = 'root';
  $password = 'pass';
 
  // Connection options
  $options = array(
    PDO::MYSQL_ATTR_INIT_COMMAND    => "SET NAMES utf8"
  );
 
  // Initialize the connection
  $connection = new PDO( $dns, $user, $password, $options );
} catch ( Exception $e ) {
  echo 'Connection to MySQL impossible: ' . $e->getMessage();
  die();
}

Note the addition of the variable $options (an array) that will contain an option 'MYSQL_ATTR_INIT_COMMAND', this option allows you to send a query when the connection is established, the query in question "SET NAMES utf8" simply tells MySQL we will exchange our data in UTF8.

The less standard version simply consists to make this request just after the connection:

// Server connection
try {
  $dns = 'mysql:host=localhost;dbname=test';
  $user = 'root';
  $password = 'pass';
  $connection = new PDO( $dns, $user, $password );
  $connection->query("SET NAMES utf8");
} catch ( Exception $e ) {
  echo 'Connection to MySQL impossible: ' . $e->getMessage();
  die();
}

It's the same, but the first version, albeit more verbose, is more "elegant" (and incidentally, we will later add parameters in the options).

No mistakes?

Another problem, you may have (more or less voluntarily) committed errors by entering the query, if that is not the case go ahead, insert a syntax error in the line:

$select = $connection->query("SELECT * FROM wrongtable");

The query shows no error! It's very frustrating...

To solve the problem, the first step will be to tell our connection we want errors to be issued. Let's change our connection file this way:

// Server connection
try {
  $dns = 'mysql:host=localhost;dbname=test';
  $user = 'root';
  $password = 'pass';
 
  // Connection options
  $options = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
  );
  $connection = new PDO( $dns, $user, $password, $options );
} catch ( Exception $e ) {
  echo 'Connection to MySQL impossible: ' . $e->getMessage();
  die();
}

By testing the query, you will see a nice error, of course, queries recovery and processing will now be placed in the try / catch blocks:

// Server connection
require_once('connection.php');
 
// Datas recovery
try {
  // We send the query
  $select = $connection->query("SELECT * FROM countries");
 
  // We use results as object
  $select->setFetchMode(PDO::FETCH_OBJ);
 
  // We loop the results
  while( $result = $select->fetch() ) {
    echo '<p>' . $result->id . ' ' . $result->name . '</p>';
  }
} catch ( Exception $e ) {
  echo 'An error has occurred';
}

UPDATE and DELETE

The query method is used to perform SELECT, to perform UPDATE, INSERT or DELETE, we will use instead the exec method.

// Server connection
require_once('connection.php');
 
// Delete datas
try {
  // We send the query (Arbitrary removal of 2 records)
  $numberOfRemoval = $connection->exec("DELETE FROM countries LIMIT 2");
 
  // We display the number of records deleted
  echo $numberOfRemoval . ' has been deleted.';
 
} catch ( Exception $e ) {
  echo 'An error has occurred';
}

Each time you actualize the page, 2 lines will be deleted, until you have no more lines left in your table.

Prepared statements

This is the omnipotence of PDO ... The famous prepared statements.

The principle is simple, you will not directly execute queries. The SQL is prepared for execution with "options".

For example, rather than saying "delete record number 1", you'll prepare the query first: "delete record number %aNumber%", then as soon as you need to delete a record with the prepared statement, you'll execute the query specifying a value.

Mysteries parameters

There are 2 ways to define a prepared statement, the first using the ? (question mark) to pass values at the time of execution, specifically it looks like this:

// We prepare the statement
$preparedStatement = $connection->prepare('SELECT * FROM countries WHERE id=? LIMIT 1');
try {
  // We send the query
  $preparedStatement->execute(array(1));
   
  // Treatment
  if( $record = $preparedStatement->fetch(PDO::FETCH_OBJ)){
    echo '<p>' . $record->id . ' ' . $record->name .'</p>';
  }
} catch( Exception $e ){
  echo 'Delete error: ' . $e->getMessage();
}

This system allows to reuse the prepared query later by changing the value used for execution by another.

Of course we can define several mysteries parameters:

$preparedStatement = $connection->prepare('SELECT * FROM countries WHERE id=? AND name=?');
try {
  // We send the query
  $preparedStatement->execute(array(2, 'France'));
   
  // Treatment
  while( $record = $preparedStatement->fetch(PDO::FETCH_OBJ)){
    echo '<p>' . $record->id . ' ' . $record->name . '</p>';
  }
   
} catch( Exception $e ){
  echo 'Query error: ' . $e->getMessage();
}

Parameters are used in the order they appear in the query.

Named placeholders

Suppose now that we have a lot of parameters (or parameters that we want to use several times). You can use named placeholders.

First case, a parameter used several times:

$preparedStatement = $connection->prepare('SELECT * FROM countries WHERE name LIKE :search OR id LIKE :search');
try {
  // We send the query
  $preparedStatement->execute(array('search'=>'%an%'));
   
  // Treatment
  while( $record = $preparedStatement->fetch(PDO::FETCH_OBJ)){
    echo '<p>' . $record->id . ' ' . $record->name . '</p>';
  }
   
} catch( Exception $e ){
  echo 'Query error: ' . $e->getMessage();
}

Another case, many parameters:

$insert = $connection->prepare('INSERT INTO users VALUES(
NULL, :lastname, :firstname, :birthdate, :gender, :nationality, :pseudo)');
try {
  // We send the query
  $success = $insert->execute(array(
    'lastname'=>'Smith',
    'firstname'=>'John',
    'birthdate'=>date('Y-m-d'),
    'gender'=>NULL,
    'nationality'=>'French',
    'pseudo'=>'johnSm'
  ));
   
  if( $success ) {
    echo 'Success';
  }
} catch( Exception $e ){
  echo 'Query error: ' . $e->getMessage();
}

At the time of execution, you pass an indexed array with the keys having named placeholders name.

Transmitted data may come from variables.

PDO will automatically take care transmission formats and escapes. But you can go further by using the bindParam method.

bindParam method

The bindParam() method allows to fill the prepared statement with the contents of a variable, it also has the ability to specify a data type and size.

Important: the use of bindParam is very powerful, you can prepare your query before send it and execute it several times, if changes occur in the bind variables, they will be considered at the time of execution.
$insert = $connection->prepare('INSERT INTO users VALUES(
NULL, :lastname, :firstname, :birthdate, :gender, :nationality, :pseudo)');
try {
  // We fill parameters
  $insert->bindParam(':lastname', $lastname, PDO::PARAM_STR, 100);
  $insert->bindParam(':firstname', $firstname, PDO::PARAM_STR, 100);
  $insert->bindParam(':birthdate', date('Y-m-d'));
  $insert->bindParam(':nationality', $nationality, PDO::PARAM_STR, 2);
  $insert->bindParam(':pseudo', $pseudo, PDO::PARAM_STR);
   
  // We execute
  $insert->execute();
 
  if( $success ) {
    echo 'Success';
  }
} catch( Exception $e ){
  echo 'Query error: ' . $e->getMessage();
}

The bindParam() method has a little sister: bindValue(). This method is a light version of bindParam() which has the advantage of providing control data transmitted to the prepared statement.

Conclusion

In this tutorial, you have learned how to query data from MySQL database using PDO objects.

If you need any help, please post below. Don't hesitate to participate, bring tips and advices .

About Simon Laroche
Simon Laroche on Google+
Simon Laroche on Twitter
Simon Laroche on Facebook
Simon Laroche on Pinterest
Simon Laroche on LinkedIn
: I am a Coder, Designer, Webmaster and Expert SEO Consulting, I'm also a wise traveller and an avid amateur photographer. I created the website TipoCode and many others such as Landolia: a World of Photos...

If you need help about this script, please leave a comment below. I reply as much as I can depending of my time, you may also get help from others.
I also offer a paid support, if you are in the need to adapt or create a script...

Leave a comment

Comments (1 comment)

gautam Posted on August 18, 2015
please make this tutorial using OOP(object oriented)
you have showed us that mysql using oop, but how to do that using oop
http://www.tipocode.com/database/mysql-php-oop-database-connection/

i tried but it works , but i want two different files like database and crud file

class.db.php
class.crud.php

i created "class.db.php" using oop but how do i used database in "class.crud.php" file

please help ....