PHP MySQL Connect, The Right Way!

Connecting to MySQL with PHP is a common among developers. Without a proper connection, your site performance and security could suffer. Learn how to setup & run queries with a PHP MySQL connection, the right way!

Code is a living text. All developers should be aware and embrace this fact. No matter the type of development you do, a year from now, there will probably be a better way to accomplish what you did today. Code is constantly evolving as technology improves—a perfect example being how to connect to MySQL with PHP.

I’ll walk you through how to properly connect to your MySQL databases with PHP for better site performance and security. Learn how to connect to MySQL with PHP the right way!

Connecting to MySQL with PHP

There’s many way to connect to MySQL with PHP. Some options include:

• mysql_connect (deprecated as of PHP 5.5.0)
• mysqli
• PDO_MYSQL

Which one is the best? Doing a quick Google search for ‘php mysql connect‘ will yield a ton of results with one common theme—connecting to MySQL with PHP should be done using a PDO connection. In the old days, most developers used mysql_connect to talk to their database. Code has evolved and that method has been officially deprecated as of PHP 5.5.0. You could use mysqli, but PDO is more stable with better performance and database support. There’s a great in-depth article by Dejan Marjanovic detailing the differences between PDO and MySQLi.

PHP MySQL Connect with PDO

PDO – PHP Data Objects – is a database access layer providing a uniform method of access to multiple databases.

There’s a ton of articles out there about PDO, how it works, why to use it, and detailed information on it’s functions (see the bottom of this post for more information). Instead of going into all the intricacies, I’m just going to show you how to use it. So, let’s start off with the basics, how to use PDO to connect to MySQL.

Connecting with PDO

Depending on the type of database you’re using, there are slightly different connection methods. The example above uses MySQL. Here’s some other types of connection methods:

What if you run into errors? You should always wrap your PDO operations in a try/catch, and use the exception mechanism:

This will output any connection errors you might run into. It’s important to note, the default error mode for PDO is PDO::ERRMODE_SILENT. If you don’t spefify the mode, you’ll need to manually fetch errors after performing a query. PDO has three error modes, in the example above, where using ERRMODE_EXCEPTION. This is the mode you’re going to want to use in most situations. It’ll fire an exception allowing you to handle errors gracefully and hide data that might help someone exploit your system. Here’s the other two modes available:

Fetching Data with PDO

There’s a couple of different ways to run queries using PDO. Generally, it’s best to use prepared statements to safeguard against SQL injection. With the prepare method we prepare the query before the user’s data gets attached. See below:

For better readable code, it’s best to use named parameters vs. ?. Notice we used :name to specify the placeholder.

You could also use the bindParam:

Now, let’s look at how to get the output. Once you’ve called the execute method, there’s a variety of different ways to get your data: an array (the default), and object, etc. In the example above, we’re using the default PDO::FETCH_ASSOC to return an array. This can easily be overridden like so:

Here, we’re returning an anonymous object with property names that correspond to the columns. Other choices include, but not limited to:

• 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

No results? No problem.

What if no results are returned? That’s the problem with the code above, we’re not providing any feedback if nothing is returned. We can easily fix that with the following:

Taking it a step further…

One of the benefits to using the PDO extension is the ability to execute the same SQL query multiple times with different parameters. Check out the example below:

The example above shows how you can easily execute the same query multiple times using different parameters. It’ll insert two rows, one with a name of “Ben”, and the other with “Angel”.

Creating, Updating, Deleting

Now that you’re connected, you’re probably going to want to run some queries. We looked into how to insert data above a little, now I’ll show you some more examples how to create, update and delete data:

Insert

Update

Delete

PDO Object Mapping

One of the coolest aspects of using PDO (mysqli can do this as well), is the ability to may query results to a class or object. See below:

Other Helpful Methods

Here’s some other helpful methods that will make life a lot easier when running queries with PDO:

Get last insert ID

lastInsertId() should always called on the database handle, not statement handle, and will return the auto incremented id of the last inserted row by that connection.

Return number of rows affected

The rowCount() method returns an integer indicating the number of rows affected by an operation. In at least one known version of PDO, according to this bug report the method does not work with select statements. If you’re having this problem, and can’t upgrade PHP, you could get the number of rows with the following:

More On PHP MySQL PDO Connections

• Why you Should be using PHP’s PDO for Database Access by Erik Wurzer
• PDO vs. MySQLi: Which Should You Use? by Dejan Marjanovic
• Introduction to PHP PDO by Kevin Waterson