January 16, 2014 11:29 am

How to use PDO database connection in PHP

As we all know that in PHP 5.5 MySQL is deprecated and we have to move new extensions like MyQLi or PDO (PHP Data Objects) for database integration, we have already publish an article on How to use MySQLi_connect PHP and this article will explain you how to connect with MySQL database using PDO connection and we explain very few things in this tutorial like database connection, insert, delete, update and select queries with prepare statement, multiple insert and error handling.

How to use PDO database connection in PHP

Connection

You all knows the old style of connecting to MySQL database:

Using PDO we create a new instance of the class, and describe the name of driver, database name, database user and database password:

We specify mysql as name of driver in this case and followed by database credential required.

Errors

If there is an error in database connection how can we identify so here is try/catch for error handling.

PDO::ERRMODE_EXCEPTION will fire exceptions when occur. Using this procedure we can handle any exception.

Fetch

There is two ways to fetch data query and execute we explain both.

1. Query

In this code we used query and manually escaping data with $conn->quote this method is equivalent to mysql_real_escape_string; these both function escape quote user send in data.

2. Execute

In this example we used prepare statement and execute it after that. In this example SQL injection is almost impossible, because $id never direct get into the query. we used :user_id as a placeholder.

$data->fetch() simply shows array of records and you can make it more stylish with FETCH_OBJ like below.

Few fetch styling parameters:

PDO::FETCH_ASSOC: return array with column names.

PDO::FETCH_BOTH: Default fetch style is BOTH it return array with column name and index start with 0.

PDO::FETCH_OBJ: return anonymous object with property name.

PDO::FETCH_NUM: return array with index by column number.

Result validation with count if there is no records found in given query:

Full code of fetching:

Insert

This example shows you how to insert record in a table and also include multiple records insertion with different data.

Prepare query once and execute it multiple time as you required in your script.

Insert single record and show affected rows:

Update

Delete

If you are still using MySQL API then you must stop and start development with PDO or MySQLi these both extensions more secure.

Closing

I hope you like this explanation please comment your views and improvements in our upcoming tutorials.

Author Huzoor Bux

I am Huzoor Bux from Karachi (Pakistan). I have been working as a PHP Developer from last 5+ years, and its my passion to learn new things and implement them as a practice. Basically I am a PHP developer but now days exploring more in HTML5, CSS and jQuery libraries.


Tutorial Categories:
  • Sonelal

    To use PDO ,Do we need to include some library?

  • Nigam Kumar Tiwari

    Really Interesting…….It will help us to create more secure & reliable projects.

  • Gautam Sharma

    Hi sir..

    i created a simple crud app using PDO and

    When I trying to save record I get this error message

    Fatal error:
    Uncaught exception ‘PDOException’ with message ‘SQLSTATE[23000]:
    Integrity constraint violation: 1062 Dubbele ingang ” voor zoeksleutel
    ‘password” in C:wampwwwpdocrud.php on line 15

    PDOException:
    SQLSTATE[23000]: Integrity constraint violation: 1062 Dubbele ingang ”
    voor zoeksleutel ‘password’ in C:wampwwwpdocrud.php on line 15

    Dubbele ingang ” voor zoeksleutel. = in English means that this: Double input ” for search key
    But I have no password in there, only in dbcon. php is a password..

    how can i solve this…
    please help me..

  • WP DEV

    Hi,
    I ‘m wp developer and need one question about the feedburner.

    Requirement : I want to adjust our daily feedburner post with category wise as well as want to add the some header and footer in feedburner.

    Any idea about this ?

    Thanks,

  • Max John

    U are very good . Please can u do a tutorial on the difference between the procedural (fonctiom ) and PDO with a pratical example on handling a form or something like that ?
    I like 👍 ur articles. They tackle interesting subjects.