Tutorials MySQL PHP Data Objects

PHP Data Objects

Recently, I changed this site from MySQL to PDO, and you should do the same. MySQL is depreciated and will be removed from PHP in the near future. PDO stands for PHP Data Objects and it uses Object Oriented Protocol to connect to databases.

PDO vs MySQLi

MySQLi is useful and easy to learn, but once you master PDO, you will never want to go back. The only thing MySQLi has over PDO is procedural api, which is just a shortcut for OOP. If you use OOP, PDO supports named parameters which makes it a lot easier than MySQLi's numeric binding.

The biggest advantage is PDO supports 12 different databases: CUBRID, MS SQL Server, Firebird/Interbase, IBM, Informix, MySQL, MS SQL Server, Oracle, ODBC and DB2, PostgreSQL, SQLite, and 4D. MySQLi only supports MySQL (shocker, eh?). This means that if you ever move to a different database, all you need to change is the connection and do a bit of bug testing for unsupported queries in PDO. If you use MySQLi, you'll have to redo all of your statements.

For more info, read: http://www.php.net/manual/en/mysqlinfo.api.choosing.php

Switching over from MySQLi

Switching to PDO is easy if you already have it in MySQLi. You can reuse all of your query statements. Prepared statements escapes everything for you, so you don't have to use mysql_real_escape_string for the procedural method.

PDO connection

<?php
// Database info
$hostname   = 'localhost';
$dbusername = 'admin';
$dbpassword = '123';
$dbname     = 'superdatabase';

// Connect to Database
$link = new PDO('mysql:host='.$hostname.';dbname='.$dbname, $dbusername, $dbpassword);

// Show errors for debugging
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>

Queries

If you're using OOP in MySQLi, then you will already be familiar with prepare -> bind -> execute. Prepare the SQL statement, bind the variables, and send it off. Named parameters is a bit longer than ordered, but it's a lot easier for bug testing when something goes wrong.

MySQLi's Procedural Method

$username = mysqli_real_escape_string($link, $username);
$password = mysqli_real_escape_string($link, $password);

$sql    = 'SELECT * FROM users WHERE username = "'.$username.'" AND password = "'.$password.'"';
$result = mysqli_query($link, $sql);

Ordered OOP Binding

$sql    = 'SELECT * FROM users WHERE username = ? AND password = ?';
$result = $link->prepare($sql);
$result->bind_param('ss', $username, $password);
$result->execute();

PDO's Named Parameters

$sql    = 'SELECT * FROM users WHERE username = :username AND password = :password';
$array  = array('username' => $username, 'password' => $password);
$result = $link->prepare($sql);
$result->execute($array);

That's it for this tutorial. If you are still using MySQL, you should switch to MySQLi or PDO. I highly recommend PDO as it supports multiple database while MySQLi only supports MySQL. Named parameters makes it a lot more organized than counting orders.

Posted by on . Category: MySQL


Comments

No comments posted yet

You need to register or login to post new comments.