Connecting to a mySQL Database

PHP provides three different ways to connect to a mySQL database. One of these is procedural while the other two are object oriented. Two of them use the mySQLi interface that was first introduced in early July 2004 and the other uses the PDO interface introduced in late July 2004. Here we are going to compare the three so that you can decide which you are going to use.

mySQLi procedural:

$db = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');

mySQLi Object Oriented:

$db = new mysqli('localhost', 'my_user', 'my_password', 'my_db');

PDO:

$db = new PDO('mysql:host=localhost;dbname=my_db;charset=utf8', 'mu_user', 'my_password',array(
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

As you can see, all three of them are very similar. The PDO version does require a bit more information because it can work with databases other than mySQL and it also provides simpler commands for actually accessing the database and allows you to set up defaults for how these commands are to work.

To start with I recommend that you forget the first of the three options above. Converting the database calls in all your code from procedural to OO is trivially easy and results in slightly shorter code so there is no reason whatsoever for using a procedural connection. Also mysqli_query procedural commands will work with an OO connection so if you are still in the process of converting your database calls to use prepare instead of query you can still use theOO connection with your subsequent database calls just as long as you convert the calls to OO as you convert them to use prepare.

The main differences between the two OO calls (apart from the object they are creating being different are the values for the first and fourth parameters. With the mysqli object the first parameter is the address of the server where the database can be found (and optionally the port to use to access it and the fourth parameter is the name of the database on that server. PDO combines all of this information and a couple of extra things into the first parameter. In PDO the first parameter provides the type of database being accessed, the address (and optionally the port) of the server, the name of the database and the characterset you are using to access the database. The characterset is not needed for the mysqli call as that is defined within the database for mySQL databases but the value does need to be provided again for PDO in case you switch to a different database where this information is not provided that way.

Having included all that information in the first parameter means that PDO can use the fourth parameter for a different purpose. What PDO uses the fourth parameter for is to provide an array of default values for the subsequent PDO calls to use. This saves you having to specify them on every single database call that needs them. Alternatively you can leave out the fourth parameter and assign each of the default values in its own setAttribute call with the attribute to be set specified as the first parameter and the value to set it to as the second parameter. Unless you need to change the defaults part way through the processing it is easier to set them all at the start.

With the example code above we are setting three defaults. As PHP now fully supports prepare statements on the server we can set the emulate prepare option to false so as to ensure that the prepares are run properly on the server and not emulated on the client. The second default we are setting is mySQL specific and that is to set the default fetch mode. This is what makes using prepare statements for select in PDO so much simpler than the mySQLi equivalents. The third option tells PDO to throw exceptions if any database errors occur. This allows us to use try/catch to display the errors in our development environment and to log the errors in our production environment based on what we include in the catch block.

mySQLi doesn't have defaults for these types of things as it doesn't allow for prepare emulation, the fetch mode is specified by your specific choice of commands when retrieving the data and most of the methods return false if they fail allowing you to code the failure processing directly in an if statement rather than using exception processing.

 

This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow
Donate