Using MySQLi and PDO instead of MySQL_*

It is time to deal with mysql_* functions once and for all. These methods are deprecated and slow. The time to upgrade has long passed yet we still see it everywhere. Since I cannot force every author to update their tutorial and blogs, I decided to write a post to hopefully rank better and provide the essential information to help new comers.

Old tutorial are still leading on the web.

I do try to contribute as much as I can and Stackoverflow.com is my platform of choice. When I see people asking questions tagged php and mysql, I automatically know that someone will be using a deprecated function and that the code is vulnerable to SQL injection.

It may look like it will take a while before people move on from mysql_* functions however the community at stackoverflow is doing a pretty good job. In less than a minute after a question is asked, users start commenting about the deprecated functions and say: "mysql_* is deprecated, use mysqli or pdo", and all 3 keywords are hot-linked with their corresponding article.

• Mysql_* is deprecated
• Using MySqli
• Using PDO

I do feel however that simply reading the manual doesn't necessarily help. Seeing a real example may provide with more specific use case and help new users make their application more secure.

Lets get down to it right away.

Connecting to a database.

Before having any interaction with the database, you have to connect to it:

$mysqli = new mysqli('localhost','username','password','database'); 

Now that we have a connection we can query our data from our tables,

$result = $mysqli->query("SELECT * FROM mytable LIMIT 10"); 

Note:
The MySqli driver gives you the option of using it in a object oriented way and functional way. For this particular example I used OO. Using it with functions is not very much different than how it worked with mysql_* . All you have to do is replace mysql_* with mysqli_* and it will work in most cases.

MySqli real world example

When you want to pass data in your query, mysqli allows you to use parameters to make it easier and more secure; no sql injection.

Let's say we want to add pagination to our website and our variable that determines the page is in the URL query. Here is how we would write it.

URL: http://example.com/show/books.php?page=1

$page = $_GET['page']; 
$perpage = 10; // the number of results per page
$offset = ((int)$page-1) * $perpage; // geting the page offset 
$query = "SELECT id,name,author FROM books LIMIT ?,? "; 

// Connect to the database
$mysqli = new mysqli('localhost','username','password','database'); 

// stop if there is an error; 
if ($mysqli->connect_error){ // improve your error handler for extra points
    die("$mysqli->connect_errno: $mysqli->connect_error"); 
} 

// Binding parameters 
$statement = $mysqli->prepare($query); 
$statement->bind_param("ii",$offset,$perpage); 
$statement->execute();
$statement->bind_result($id,$name); 

// Get results
$result = $statement->get_result(); 

Same example using mysqli_* (non object oriented)

$page = $_GET['page']; 
$perpage = 10; // the number of results per page
$offset = ((int)$page-1) * $perpage; // getting the page offset 
$query = "SELECT id,name,author FROM books LIMIT ?,? "; 

// Connect to the database
$connection = mysqli_connect('localhost','username','password','database'); 

// stop if there is an error; 
if (mysqli_connect_errno()){ // improve your error handler for extra points
    die(mysqli_connect_errno().": ".mysqli_connect_error()); 
} 

// Binding parameters 
$stmt = mysqli_prepare($connection,$query); 
mysqli_stmt_bind_param($stmt, "ii",$offset,$perpage);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id,$name);
mysqli_stmt_fetch($stmt);

// Get results
mysqli_stmt_get_result($stmt); 

Converting from mysql to mysqli is not very complicated. These example should be pretty straight forward and for any additional information you can consult the manual

Note:
If you are hosting your app on a shared hosting provider you might want to check with support to see if they support mysqli. Worst case scenario you can move your website to digitalocean.com. You will have much more control for virtually the same price.

Do you want to know more about MySqli and why you should use it? There is and in depth manual here that should answer all your questions.

---