<

Why PHP Removed Support for the MySQL API

Published on
217,868 Points
28,368 Views
30 Endorsements
Last Modified:
Awarded

Introduction (All good things must come to an end)mysql_warning.pngThe original MySQL API has gone away.  It was deprecated (years ago) by PHP in Version 5.5, and removed from PHP in all current releases.  As a result, the issue is upon us today.  It's time to remediate our old PHP scripts, and bring them up-to-date for the current millennium. This article explains the issues and gives step-by-step and line-by-line instructions that can lead us out of the MySQL darkness and into current best practices.


WHY in the World Did PHP Do This?

Great question!  The MySQL extension was by far the most popular data base extension used by PHP web sites.  It had good documentation and support (consider phpMyAdmin, and after you've used that, go over to phpPgAdmin and compare features).  MySQL was thoroughly debugged, and was widely accessible - you could find literally thousands of examples of PHP+MySQL code with a Google search.  So why did PHP take such a draconian step?  In my opinion, it comes down to two issues.  


First, PHP has "grown up" as a programming language.  At one time, its proponents were looked down upon as script kiddies by the computer science community.  Never mind the fact that parts of Google, much of Yahoo, and nearly all of Facebook, Digg, Wikipedia, WordPress, and Slack are PHP machines -- there was a stench is created by the millions of incompetently written PHP web sites that crashed and got hacked all the time.  It's very easy to pick up a little knowledge about PHP and MySQL, and an unfortunate corollary is that it's very easy to use PHP and MySQL wrong!  Security holes abound, and the unsophisticated programmer propagates these errors by copying PHP code without understanding the dangers.  So in an effort to bring some rigor and science to the creation of PHP scripts, we got this explanation from P. Olson.  As you might imagine, there was early controversy over the change, but that is in the past now.


Second, the PHP authors were actually right -- the MySQL extension was simply terrible about security.  If it had been any good, there wouldn't have been a need for two million MySQL security articles!  There was nothing inherently wrong in the extension; the trouble came from the way novice programmers misused the extension.  As but one example, consider the use of external data in MySQL queries.  A common-sense implementation would have prevented the use of a query variable that had not been escaped.  But the original implementation of the MySQL extension imposed no such requirement, and the resulting security holes caused many web sites to fail. 


PHP tried to discourage the use of the MySQL extension at PHP 5.0 by removing the MySQL extension from the standard installation.  But that did not have the desired effect of luring programmers away from the dangerous and sloppy code, and that was done more than 10 years ago.  So a bigger stick was needed. The only way to make such a sweeping change in the behavior of a language interpreter (and a huge population of users) was to remove the dangerous parts of PHP and force programmers to stop using them.


In addition to security, there are many good reasons to adopt object-oriented programming techniques.  The MySQL extension by its very nature, frustrated this effort.  The MySQL API was procedural, leading to code that was messy, difficult to maintain and impossible to reuse.  In contrast, the MySQLi and PDO extensions offer object-oriented interfaces.


What are Others Saying and Doing About This Change?

On April 7, 2014, the Make WordPress team issued this announcement:

In WordPress 3.9, we added an extra layer to WPDB, causing it to switch to using the mysqli PHP library, when using PHP 5.5 or higher.  For plugin developers, this means that you absolutely shouldn’t be using PHP's mysql_*() functions any more - you can use the equivalent WPDB functions instead.


OK, What Should I Do?

First, adopt a coding standard that prohibits further use of the MySQL extension.  After today, you must never write mysql_query() again! Next, learn about the alternatives to MySQL.  Basically, you've got two good choices: MySQLi or PHP Data Objects ("PDO").


Second, be aware of the environmental changes that accompany this PHP change.  Installation libraries are different now.  The way you install the MySQL extensions is different now.  Please take a moment to read this page, especially if you're moving from an obsolete PHP 5.3 installation to a current release or version!

https://dev.mysql.com/downloads/connector/php-mysqlnd/


Do I Have to Change my PHP Programs?

Yes.  And the rest of this article shows annotated code examples to help you understand how to accomplish the changes.  The scripts have been set up as teaching examples.  You should be able to add your own MySQL credentials and install them on your own servers.  Then you can run them to see how the scripts work and how the output from error messages differs.


The scripts are shown in complete form at the bottom of the article.  In the annotated parts, only the essential pieces are shown to illustrate the different approaches.  


In each code set, the first example is the familiar-but-obsolete MySQL extension.  Following that, we have the MySQLi extension.  In that example, I tried to aim for the most direct one-to-one replacement of existing MySQL programming.  Of necessity this meant avoiding MySQLi prepared statements.  They are introduced in the PDO example, and a complete MySQLi example showing the use of prepared statements is included with the code snippets at the end of the article.  There are some differences in the syntax but for the most part the functionality lines up 1:1 between MySQL and MySQLi.  That cannot be said for PDO, where some substantial programming changes come into play.


MySQLi or PDO?

Which extensions should you choose?  Either will work.  Object-oriented MySQLi requires the smallest number of code changes.  PDO holds out the promise that you could, theoretically, change out the underlying data base without having to change your PHP scripts.  I don't know any professional programmer who believes such a claim, given the variations in SQL.  And PDO can only give you parameter binding if you use prepared statements, thus introducing a good bit of overhead.  Your choice will largely depend on the amount of time you want devote to the required modifications.  Can you trust your programmers to remember to escape external data before using it in a query?  If the answer is "yes" then MySQLi is a good choice, even if many computer scientists would express a preference for PDO.  


If you are considering PDO, this article is worth reading, but be aware that there are errors in the article's code examples.  Read for comprehension, but don't copy the code unless you're willing to do some debugging!


Object-Oriented or Procedural?

Object-oriented.  There are no real advantages and many disadvantages if you get stuck in procedural database extensions.  So just don't do that.


From personal experience, here are the reasons why I choose object-oriented MySQLi.


1. There can be two simultaneous connections to the DB engine.  You do not have to convert all of the old MySQL code at once - you can convert it query-by-query.


2. There are almost no changes needed to the SQL query strings (in PDO almost every query string will have to change).


3. There is almost 100% 1:1 matching of functionality (in PDO some of the MySQL operations are lost, eg, data_seek() does not exist).


4. There are relatively few code changes needed.  If you were to choose procedural MySQLi instead of OOP MySQLi, every call to the query() function would have to change because the procedural version requires the database link identifier as the first argument.  The OOP version infers the database link from the MySQLi object.


5. Anybody who really believes that using PDO will somehow magically let you change out the underlying data base engine, without creating havoc in your application software, has never tried to change out an underlying data base engine.


Do I Have to Change my Query Strings?

Probably not if you choose MySQLi.  Your queries require very modest changes (if any) when you choose the MySQLi extension.  PDO is a different story.  It uses an entirely different query structure with prepared statements and bound data.  PDO does not put PHP variables into its query strings; it passes the variables separately and thus it requires changes in almost all query strings, as well as the attendant PHP programming.  


Do I Have to Change my Data Base Tables?

No.  We are changing the API extensions, but we are not changing the underlying data base tables or the essential nature of the SQL query language.


This Sounds Like a Huge Amount of Work

Yes.  You almost certainly want to use some kind of version control.  But it does not have to be a daunting "all-or-nothing" task.  You can make simultaneous connections to the MySQL server using MySQLi alongside existing MySQL API code, in the same PHP script, and you can change your queries one at a time.  I have not tested it, but I would expect that the same might be true for PDO.


What Will I Learn Here?

Each of these examples performs the same basic data base functions.  We connect to the server and select a data base, we CREATE a table and load it with data.  We run a SELECT query and count the results.  We show the results set (using two methods of access to the rows of the results set).  We DELETE a row from the table.  And finally we run a query that is designed to fail so that we can see the differences in the error output.  You can print out these three scripts and follow the logic as you read the annotation and watch them run.


Man Page References

You will need to read these online references to understand the code in the snippets below.  No excuses, just do it.  You might even want to bookmark the PHP man pages. You may also want to review this web page that provides a map of MySQL functions to the MySQLi and PDO extensions.



MySQL

// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-object.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php

MySQLi


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php


PDO

// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND PDO/MYSQL
// MAN PAGE: http://php.net/manual/en/book.pdo.php
// MAN PAGE: http://php.net/manual/en/class.pdo.php
// MAN PAGE: http://php.net/manual/en/class.pdoexception.php
// MAN PAGE: http://php.net/manual/en/class.pdostatement.php
// MAN PAGE: http://php.net/manual/en/pdo.construct.php
// MAN PAGE: http://php.net/manual/en/pdo.setattribute.php
// MAN PAGE: http://php.net/manual/en/pdo.query.php
// MAN PAGE: http://php.net/manual/en/pdo.prepare.php
// MAN PAGE: http://php.net/manual/en/pdo.prepare.php#97942 <-- NO CURSOR SCROLLING
// MAN PAGE: http://php.net/manual/en/pdostatement.execute.php
// MAN PAGE: http://php.net/manual/en/pdo.lastinsertid.php
// MAN PAGE: http://php.net/manual/en/pdostatement.bindparam.php
// MAN PAGE: http://php.net/manual/en/pdostatement.rowcount.php
// MAN PAGE: http://php.net/manual/en/pdostatement.fetchall.php
// MAN PAGE: http://php.net/manual/en/pdostatement.fetchobject.php


Test Data

Identical for MySQL, MySQLi, and PDO.  It's the first thing you need if you're going to write computer programs.  In fact it's so important that a wise programmer will write the test data first, before a single line of code is laid!

 

// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray"    , "lname" => "Paseur"   )
, array( "fname" => "Bill"   , "lname" => "O'Reilly" )
, array( "fname" => "Ray"    , "lname" => "Capece"   )
, array( "fname" => "John"   , "lname" => "Paseur"   )
)
;


Authentication Credentials

Identical for MySQL, MySQLi, and PDO. Data base authentication credentials are the same for these extensions, however PDO uses the concept of a Data Source Name (combining the Data Base Engine and the Data Base Name).

 

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";


Connect to the Server and Choose the Data Base

Each of these connections has very different information available in the DB connection resource or object.  For MySQL, the connection is a resource that does not tell us very much.  For MySQLi, the connection is an object with a rich collection of information.  For PDO, the connection is an object but it has no visible properties at this point.


MySQL uses connect and select function calls. Errors can be visualized using the appropriate error messages which can be found by calling the relevant MySQL functions.


MySQLi connects to the DB server by instantiation of a new MySQLi object. Errors, if any, can be found by visualizing the appropriate error messages which are properties of the MySQLi object.


Connect/select proceeds a bit differently in PDO.  Check the cautionary notes about accidental exposure of PDO authentication data. For this reason we wrap the instantiation of the PDO object in try/catch code blocks so we can handle the exceptions in our own code, rather than having an uncaught exception expose information that should not be made public.  More information on the PDOException object can be found in the online man pages.  PDO uses the Data Source Name ("DSN") that includes both the host name and the data base name.


MySQL

// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$dbcon = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $err
    = "NO DB CONNECTION: $db_host: "
    . mysql_errno()
    . ' '
    . mysql_error()
    ;
    trigger_error($err, E_USER_WARNING);
}

// SELECT THE MYSQL DATA BASE
if (!mysql_select_db($db_name, $dbcon))
{
    $err
    = "NO DB SELECTION: $db_name: "
    . mysql_errno()
    . ' '
    . mysql_error()
    ;
    trigger_error($err, E_USER_WARNING);
    trigger_error('NO DATABASE', E_USER_ERROR);
}
// SHOW WHAT THE DB CONNECTION LOOKS LIKE
var_dump($dbcon);

  MySQLi


// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}
// SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
var_dump($mysqli);


PDO

// WARNING: AN UNCAUGHT CONNECT ERROR WILL BARK OUT THE DB CREDENTIALS!
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$dsn = "mysql:host=$db_host;dbname=$db_name";
try
{
    $pdo = new PDO($dsn, $db_user, $db_word);
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error('NO PDO Connection', E_USER_ERROR);
}
// SHOW THE PDO CONNECTION OBJECT
var_dump($pdo);


PDO - Error Visualization

This sets the attributes of the PDO object to control what warnings and exceptions we can see.  Without these settings, PDO is silent abouterror conditions.  This concept is similarly applicable to MySQL and MySQLi, where we have to control error visualization on a query-by-query basis.

 

// SET PDO TO TELL US ABOUT WARNINGS OR TO THROW EXCEPTIONS
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );


CREATE TABLE Query Strings

Identical for MySQL, MySQLi, and PDO. Even though our teaching example has a very simple table structure, we will be able to see that (1) the same structure works in all extensions and (2) the same query results can be retrieved in all extensions.

 

// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
)
"
;


Run the CREATE TABLE Query and Handle Errors/Exceptions

MySQL and MySQLi extensions use a similar-looking construct to run the query.  MySQL calls the mysql_query() function.  MySQLi calls the query() method on the MySQLi object.  The PDO extension calls the query() method on the PDO object.  We wrap this in a try/catch block that will enable us to handle any exceptional conditions. The exception, if any, will be reported to the script in the PDOException object.


MySQL

// RUN THE QUERY TO CREATE THE TABLE
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . mysql_errno()
    . ' ERROR: '
    . mysql_error()
    ;
    trigger_error($err, E_USER_ERROR);
}
// SHOW THE RESULTS OF THE QUERY
var_dump($res);

  MySQLi


// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}
// SHOW THE RESULTS OF THE QUERY
var_dump($res);


PDO

// RUN QUERY TO CREATE THE TABLE
try
{
    $pdos = $pdo->query($sql);
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}
// SHOW THE RESULTS OF THE QUERY
var_dump($pdos);


PDO - Prepare a Query

This code prepares a query for use by the PDO object.  We only need to prepare the query once, then it can be reused over and over with different data.  Since the query and the data are sent to the SQL engine separately, the SQL statement is protected from SQL injection.  The results of query preparation are returned to us in the form of a PDOStatement object, stored in $pdos.  Take note of line 6.  This is where we tell PDO what our input data looks like.  The colon-prefixed names are array indexes.  Important: Do not put quotes around these colon-prefixed fields!  They are named placeholders, not variables that might be used in MySQLi queries.  You can see how this lines things up if you look back at the array keys in the test data arrays.  After the query is prepared, we can call the execute() method repeatedly, passing associative arrays with named keys.  There is a query preparation analog for MySQLi, but no such preparation method for MySQL -- with MySQL we must remember to escape the data every time, and failure to escape the data correctly could result in a failed query.  Line 11 calls the prepare() method on the PDO object and returns the PDOStatement object into $pdos.


PDO

// PREPARE THE QUERY TO LOAD THE NAMES ("PREPARE" ONLY NEEDED ONCE)
$sql
=
"INSERT INTO my_table
(  fname,  lname ) VALUES
( :fname, :lname )
"
;
try 
{ 
    $pdos = $pdo->prepare($sql); 
} 
catch(PDOException $exc) 
{ 
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}


Use an Iterator to Escape the Data and Load the Table

The programs use identical iterators, but the method calls and sequences are different.  Unlike PDO which uses prepared query statements, the MySQL and MySQLi extensions must call the mysql_real_escape_string() function or the real_escape_string() method, then use the escaped output to create the query string.


MySQL and MySQLi make the expected function/method calls.  PDO uses the execute() method on the prepared PDOStatement object.


To test for query success and show any applicable error information, MySQL and MySQLi test for FALSE return values from the function/method calls, and execute the error handler code if necessary.  PDO catches the PDOException object that would be thrown by a failing PDOStatement object.


The last insert id (AUTO_INCREMENT KEY) is retrieved on a per-connection basis.  This means that MySQL uses the data base connection resource in its call to mysql_insert_id().  For MySQLi, the number can be found in the insert_id property of the MySQLi Object.  For PDO, the script calls the lastInsertId() method on the PDO object.


MySQL

// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_fn  = mysql_real_escape_string($person['fname']);
    $safe_ln  = mysql_real_escape_string($person['lname']);

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO my_table ( fname, lname ) VALUES ( '$safe_fn', '$safe_ln' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = mysql_query($sql);

    // IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = "QUERY FAIL: "
        . $sql
        . ' ERRNO: '
        . mysql_errno()
        . ' ERROR: '
        . mysql_error()
        ;
        trigger_error($err, E_USER_ERROR);
    }

    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
    $id  = mysql_insert_id($dbcon);
    echo "MySQL INSERTED A ROW CONTAINING <b>$safe_fn $safe_ln</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}


MySQLi


// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_fn  = $mysqli->real_escape_string($person['fname']);
    $safe_ln  = $mysqli->real_escape_string($person['lname']);

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO my_table ( fname, lname ) VALUES ( '$safe_fn', '$safe_ln' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = "QUERY FAIL: "
        . $sql
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        ;
        trigger_error($err, E_USER_ERROR);
    }

    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_fn $safe_ln</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}


PDO

// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
    // USE THE ARRAY OF KEYWORD => VALUE TO ATTACH fname AND lname
    try
    {
        // RUN THE QUERY TO INSERT THE ROW
        $pdos->execute($person);
    }
    catch(PDOException $exc)
    {
        var_dump($exc);
        trigger_error($exc->getMessage(), E_USER_ERROR);
    }

    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $pdo->lastInsertId();
    echo "PDO INSERTED A ROW CONTAINING <b>" . $person['fname'] . ' ' . $person['lname']. "</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}


Create and Execute a SELECT Query

Both MySQL and MySQLi extensions follow similar patterns to escape the external data and substitute the escaped string into the SQL statement.  After the query has been run MySQL has a resource id in $res and MySQLi has a results object.


With the PDO extension we do not need to escape the external data because the data is not put into the query string, but is sent to the SQL engine separately.  Instead we create the query string using the colon-prefixed name that we will pass to the bindParam() method.  We prepare the query on line 7 of the PDO example.  


Note that the prepare process is intended to give us a scrollable results set "cursor" which would, in theory, enable us to retrieve the rows of the results set in any order we chose.  But alas, this tool is unavailable in PHP's implementation of PDO for MySQL data bases.  In spite of being a high-value request, PHP has not implemented it.  Next, we bind the :fname column with the $fname variable using the PDO::PARAM_STR constant to tell PDO that this is a string binding and not an array binding.  Then we try to execute the query.


MySQL

// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$fname    = "RAY";
$safe_fn  = mysql_real_escape_string($fname);

// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLE
$sql = "SELECT id, lname FROM my_table WHERE fname='$safe_fn' ORDER BY lname, fname";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . mysql_errno()
    . ' ERROR: '
    . mysql_error()
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF SCRIPT GETS HERE WE HAVE A RESOURCE-ID IN $res


MySQLi


// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$fname    = "RAY";
$safe_fn  = $mysqli->real_escape_string($fname);

// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLE
$sql = "SELECT id, lname FROM my_table WHERE fname='$safe_fn' ORDER BY lname, fname";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF SCRIPT GETS HERE WE HAVE A RESULT OBJECT IN $res


PDO

$fname    = "RAY";

// CREATE A QUERY FOR USE WITH BINDPARAM()
$sql = "SELECT id, lname FROM my_table WHERE fname = :fname ORDER BY lname, fname";

// CURSOR_SCROLL ALLOWS REPOSITIONING THE CURSOR - LIKE DATA_SEEK() - BUT SADLY NOT WITH MYSQL
$pdos = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

// BIND THE VARIABLE AND TRY THE QUERY
$pdos->bindParam(':fname', $fname, PDO::PARAM_STR);

try
{
    $pdos->execute();
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}


Find the Number of Rows in the Results Set

For MySQL, the number is found by calling the mysql_num_rows() function and passing it the $res resource from the query.  With MySQLi, the number is found in the num_rows property of the $resmysqli_result object.  With PDO, the number is usually found by calling the rowCount() method on the PDOStatement object.  But... the rowCount() method may not always give you exactly what you want to know!  Please see the man page for rowCount() and read the user-contributed notes to learn about the potential ambiguities.


MySQL

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = mysql_num_rows($res);
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}


MySQLi

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}


PDO

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $pdos->rowCount();
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}


Retrieve the Rows of the Results Set

Using MySQL, the rows are retrieved using the mysql_fetch_array() function .  This function was chosen to illustrate why you should not use this function.  Unless you tell it otherwise, it retrieves twice as much data as is necessary to bring back the answers!  The output from print_r() will show what is going on.


With MySQLi, the rows are retrieved using the fetch_array() method on the MySQLI_Result object.  Like mysql_fetch_array() this method retrieves twice as much data is is needed to process the results.  Better choices are shown below


In the PDO example, the rows are retrieved into an array of objects by calling the fetchAll() method on the PDOStatement object. Each of these objects has a property with the name of the column name and a value equal to the column value in the respective row.  You can also retrieve the rows into a "record set" structure, which is a two-dimensional array of arrays, where the main array contains all of the rows, and each row is represented by an associative array.  A useful function for processing record sets is array_column().


MySQL

// ITERATE OVER THE RESULTS SET AS AN ARRAY TO SHOW WHAT WE FOUND
echo "USING MySQL_Fetch_<i>Array</i>(): ";
echo PHP_EOL;
while ($row = mysql_fetch_array($res))
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}


MySQLi


// ITERATE OVER THE RESULTS SET AS AN ARRAY TO SHOW WHAT WE FOUND
echo "USING MySQLi_Result::Fetch_<i>Array</i>(): ";
echo PHP_EOL;
while ($row = $res->fetch_array())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}


PDO

// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
echo "USING PDOStatement::FetchAll(PDO::FETCH_OBJ): ";
echo "<br/>" . PHP_EOL;
while ($row = $pdos->fetchAll(PDO::FETCH_OBJ))
{
    // ROW BY ROW PROCESSING IS DONE HERE
    foreach ($row as $key => $obj)
    {
        echo "$key: ";
        print_r($obj);
        echo PHP_EOL;
    }
}


Reset the Results Set Pointer

MySQL and MySQLi can reset the results set pointer in the existing results set.  We do not need to rerun the query to get to the same data for a different view.  The PHP MySQL PDO implementation does not support this.  The first row of the results set is numbered the same way as the first element of an array.  Numbering starts with zero.


MySQL

// RESET THE RESULTS SET POINTER TO THE TOP
mysql_data_seek($res,0);


MySQLi


// RESET THE RESULTS SET POINTER TO THE TOP
$res->data_seek(0);


Retrieve the Rows of the Results Set (Again)

With MySQL and MySQLi, each row is retrieved in the form of an object with the column name representing a property of the object that points to the value from the row of the database.  In PDO, a second retrieval is not possible; the query must be rerun, or the original results set must be saved in PHP variables so the values can be reused.


Note that retrieving the rows in the form of  objects creates an easy-to-use syntax to incorporate the results inHEREDOCvariables!  If you retrieve the rows in the form of arrays, the quoted variables must be encapsulated in curly braces for HEREDOC, making the syntax inconvenient and much harder to get right.


MySQL

// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo "USING MySQL_Fetch_<i>Object</i>(): ";
echo PHP_EOL;
while ($row = mysql_fetch_object($res))
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}


MySQLi


// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo "USING MySQLi_Result::Fetch_<i>Object</i>(): ";
echo PHP_EOL;
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}


Delete a Row From the Table

MySQL and MySQLi extensions follow similar patterns to escape the external data and substitute the escaped string into the SQL statement. The PDO extension uses the by-now-familiar :name notation with colons prepended to the array index name to prepare the query statement and bind the PHP variable.  A try/catch block encloses the call to the query handler.


MySQL

// DELETE A ROW FROM THE TABLE
$lname   = "O'Reilly";
$safe_ln = mysql_real_escape_string($lname);

// CONSTRUCT AND RUN A QUERY TO DELETE
$sql = "DELETE FROM my_table WHERE lname = '$safe_ln' LIMIT 33";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . mysql_errno()
    . ' ERROR: '
    . mysql_error()
    ;
    trigger_error($err, E_USER_ERROR);
}


MySQLi


// DELETE A ROW FROM THE TABLE
$lname   = "O'Reilly";
$safe_ln = $mysqli->real_escape_string($lname);

// CONSTRUCT AND RUN A QUERY TO DELETE
$sql = "DELETE FROM my_table WHERE lname = '$safe_ln' LIMIT 33";
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}


PDO

// DELETE A ROW FROM THE TABLE
$lname   = "O'Reilly";

// CONSTRUCT AND RUN A QUERY TO DELETE
$sql = "DELETE FROM my_table WHERE lname = :lname LIMIT 33";

// BIND THE VARIABLE AND TRY THE QUERY
$pdos = $pdo->prepare($sql);
$pdos->bindParam(':lname', $lname, PDO::PARAM_STR);
try
{
    $pdos->execute();
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}


Determine the Number of Affected Rows

MySQL calls the mysql_affected_rows() function referencing the data base connection. MySQLi reads the affected_rows property from the MySQLi object. PDO calls the rowCount() method on the PDOStatement object.


MySQL

// HOW MANY ROWS WERE AFFECTED BY DELETE?
$num     = mysql_affected_rows($dbcon);
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>AFFECTED NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>AFFECTED $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}


MySQLi


// HOW MANY ROWS WERE AFFECTED BY DELETE?
$num     = $mysqli->affected_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>AFFECTED NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>AFFECTED $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}

PDO

 

// HOW MANY ROWS WERE AFFECTED BY DELETE?
$num     = $pdos->rowCount();
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>AFFECTED NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>AFFECTED $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}


Visualize Error and Exception Information

A defective query is created to cause a failure of the MySQL server.  Each of the MySQL extensions creates its own error information, which is displayed by the script.  The PDOException object is far more comprehensive in its information.  It would require considerable programming to get this kind of detailed information from MySQL or MySQLi. In the case of PDO, note that the PDOStatement object is not modified by the query failure and still contains the previous query statement.


MySQL

// CONSTRUCT A QUERY THAT WILL FAIL
$sql = "SELECT oopsie FROM my_table ";
$res = mysql_query($sql);

// SHOW THE ERROR INFORMATION
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . mysql_errno()
    . ' ERROR: '
    . mysql_error()
    ;
    echo $err;
}


QUERY FAIL: SELECT oopsie FROM my_table  ERRNO: 1054 ERROR: Unknown column 'oopsie' in 'field list'


MySQLi


// CONSTRUCT A QUERY THAT WILL FAIL
$sql = "SELECT oopsie FROM my_table ";
$res = $mysqli->query($sql);

// SHOW THE ERROR INFORMATION
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    echo $err;
}


QUERY FAIL: SELECT oopsie FROM my_table  ERRNO: 1054 ERROR: Unknown column 'oopsie' in 'field list'


PDO

// CONSTRUCT A QUERY THAT WILL FAIL
$sql = "SELECT oopsie FROM my_table ";
try
{
    $pdos = $pdo->query($sql);
}
catch(PDOException $exc)
{
    // SHOW PDOException AND PDOStatement
    var_dump($exc);
    var_dump($pdos);
}


object(PDOException)#5 (8) {
  ["message":protected]=>
  string(79) "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'oopsie' in 'field list'"
  ["string":"Exception":private]=>
  string(0) ""
  ["code":protected]=>
  string(5) "42S22"
  ["file":protected]=>
  string(49) "/home/account/public_html/RAY_EE_pdo_example.php"
  ["line":protected]=>
  int(284)
  ["trace":"Exception":private]=>
  array(1) {
    [0]=>
    array(6) {
      ["file"]=>
      string(49) "/home/account/public_html/RAY_EE_pdo_example.php"
      ["line"]=>
      int(284)
      ["function"]=>
      string(5) "query"
      ["class"]=>
      string(3) "PDO"
      ["type"]=>
      string(2) "->"
      ["args"]=>
      array(1) {
        [0]=>
        string(28) "SELECT oopsie FROM my_table "
      }
    }
  }
  ["previous":"Exception":private]=>
  NULL
  ["errorInfo"]=>
  array(3) {
    [0]=>
    string(5) "42S22"
    [1]=>
    int(1054)
    [2]=>
    string(39) "Unknown column 'oopsie' in 'field list'"
  }
}
object(PDOStatement)#3 (1) {
  ["queryString"]=>
  string(50) "DELETE FROM my_table WHERE lname = :lname LIMIT 33"
}


Conclusion

With more than 8 years of experience using the PDO Extension API, we can be confident that the API is debugged and ready for "prime time."  The same can be said for the MySQLi Extension.  And given the current status of MySQL, it's time to upgrade our scripts.  This article has shown some links to the relevant man pages and some examples that illustrate the programming changes required to keep our programming running in the modern age.


The MySQL Obsolete Complete Code Example


<?php // RAY_EE_mysql_example.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';

// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-object.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php


// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray"    , "lname" => "Paseur"   )
, array( "fname" => "Bill"   , "lname" => "O'Reilly" )
, array( "fname" => "Ray"    , "lname" => "Capece"   )
, array( "fname" => "John"   , "lname" => "Paseur"   )
)
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$dbcon = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $err
    = "NO DB CONNECTION: $db_host: "
    . mysql_errno()
    . ' '
    . mysql_error()
    ;
    trigger_error($err, E_USER_WARNING);
}

// SELECT THE MYSQL DATA BASE
if (!mysql_select_db($db_name, $dbcon))
{
    $err
    = "NO DB SELECTION: $db_name: "
    . mysql_errno()
    . ' '
    . mysql_error()
    ;
    trigger_error($err, E_USER_WARNING);
    trigger_error('NO DATABASE', E_USER_ERROR);
}
// SHOW WHAT THE DB CONNECTION LOOKS LIKE
var_dump($dbcon);
echo PHP_EOL;


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . mysql_errno()
    . ' ERROR: '
    . mysql_error()
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);


// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_fn  = mysql_real_escape_string($person['fname']);
    $safe_ln  = mysql_real_escape_string($person['lname']);

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO my_table ( fname, lname ) VALUES ( '$safe_fn', '$safe_ln' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = mysql_query($sql);

    // IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = "QUERY FAIL: "
        . $sql
        . ' ERRNO: '
        . mysql_errno()
        . ' ERROR: '
        . mysql_error()
        ;
        trigger_error($err, E_USER_ERROR);
    }

    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
    $id  = mysql_insert_id($dbcon);
    echo "MySQL INSERTED A ROW CONTAINING <b>$safe_fn $safe_ln</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$fname    = "RAY";
$safe_fn  = mysql_real_escape_string($fname);

// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLE
$sql = "SELECT id, lname FROM my_table WHERE fname='$safe_fn' ORDER BY lname, fname";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . mysql_errno()
    . ' ERROR: '
    . mysql_error()
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res
// AND SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = mysql_num_rows($res);
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;


// ITERATE OVER THE RESULTS SET AS AN ARRAY TO SHOW WHAT WE FOUND
echo "USING MySQL_Fetch_<i>Array</i>(): ";
echo PHP_EOL;
while ($row = mysql_fetch_array($res))
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;


// RESET THE RESULTS SET POINTER TO THE TOP
mysql_data_seek($res,0);

// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo "USING MySQL_Fetch_<i>Object</i>(): ";
echo PHP_EOL;
while ($row = mysql_fetch_object($res))
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;


// DELETE A ROW FROM THE TABLE
$lname   = "O'Reilly";
$safe_ln = mysql_real_escape_string($lname);

// CONSTRUCT AND RUN A QUERY TO DELETE
$sql = "DELETE FROM my_table WHERE lname = '$safe_ln' LIMIT 33";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . mysql_errno()
    . ' ERROR: '
    . mysql_error()
    ;
    trigger_error($err, E_USER_ERROR);
}


// HOW MANY ROWS WERE AFFECTED BY DELETE?
$num     = mysql_affected_rows($dbcon);
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>AFFECTED NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>AFFECTED $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;


// CONSTRUCT A QUERY THAT WILL FAIL
$sql = "SELECT oopsie FROM my_table ";
$res = mysql_query($sql);

// SHOW THE ERROR INFORMATION
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . mysql_errno()
    . ' ERROR: '
    . mysql_error()
    ;
    echo $err;
}


The MySQLi Complete Code Example


<?php // RAY_EE_mysqli_example.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';

// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php


// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray"    , "lname" => "Paseur"   )
, array( "fname" => "Bill"   , "lname" => "O'Reilly" )
, array( "fname" => "Ray"    , "lname" => "Capece"   )
, array( "fname" => "John"   , "lname" => "Paseur"   )
)
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
var_dump($mysqli);
echo PHP_EOL;


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);


// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_fn  = $mysqli->real_escape_string($person['fname']);
    $safe_ln  = $mysqli->real_escape_string($person['lname']);

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO my_table ( fname, lname ) VALUES ( '$safe_fn', '$safe_ln' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = "QUERY FAIL: "
        . $sql
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        ;
        trigger_error($err, E_USER_ERROR);
    }

    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_fn $safe_ln</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$fname    = "RAY";
$safe_fn  = $mysqli->real_escape_string($fname);

// CONSTRUCT THE QUERY USING THE ESCAPED VARIABLE
$sql = "SELECT id, lname FROM my_table WHERE fname='$safe_fn' ORDER BY lname, fname";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// AND SO WE CAN NOW USE $res IN OTHER MYSQLI FUNCTIONS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;


// ITERATE OVER THE RESULTS SET AS AN ARRAY TO SHOW WHAT WE FOUND
echo "USING MySQLi_Result::Fetch_<i>Array</i>(): ";
echo PHP_EOL;
while ($row = $res->fetch_array())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;


// RESET THE RESULTS SET POINTER TO THE TOP
$res->data_seek(0);

// ITERATE OVER THE RESULTS SET AS AN OBJECT TO SHOW WHAT WE FOUND
echo "USING MySQLi_Result::Fetch_<i>Object</i>(): ";
echo PHP_EOL;
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    print_r($row);
    echo PHP_EOL;
}
echo PHP_EOL;


// DELETE A ROW FROM THE TABLE
$lname   = "O'Reilly";
$safe_ln = $mysqli->real_escape_string($lname);

// CONSTRUCT AND RUN A QUERY TO DELETE
$sql = "DELETE FROM my_table WHERE lname = '$safe_ln' LIMIT 33";
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}


// HOW MANY ROWS WERE AFFECTED BY DELETE?
$num     = $mysqli->affected_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>AFFECTED NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>AFFECTED $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;


// CONSTRUCT A QUERY THAT WILL FAIL
$sql = "SELECT oopsie FROM my_table ";
$res = $mysqli->query($sql);

// SHOW THE ERROR INFORMATION
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    echo $err;
}


The PDO Complete Code Example


<?php // RAY_EE_pdo_example.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';

// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND PDO/MYSQL
// MAN PAGE: http://php.net/manual/en/book.pdo.php
// MAN PAGE: http://php.net/manual/en/class.pdo.php
// MAN PAGE: http://php.net/manual/en/class.pdoexception.php
// MAN PAGE: http://php.net/manual/en/class.pdostatement.php
// MAN PAGE: http://php.net/manual/en/pdo.construct.php
// MAN PAGE: http://php.net/manual/en/pdo.setattribute.php
// MAN PAGE: http://php.net/manual/en/pdo.query.php
// MAN PAGE: http://php.net/manual/en/pdo.prepare.php
// MAN PAGE: http://php.net/manual/en/pdo.prepare.php#97942 <-- NO CURSOR SCROLLING
// MAN PAGE: http://php.net/manual/en/pdostatement.execute.php
// MAN PAGE: http://php.net/manual/en/pdo.lastinsertid.php
// MAN PAGE: http://php.net/manual/en/pdostatement.bindparam.php
// MAN PAGE: http://php.net/manual/en/pdostatement.rowcount.php
// MAN PAGE: http://php.net/manual/en/pdostatement.fetchall.php
// MAN PAGE: http://php.net/manual/en/pdostatement.fetchobject.php


// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray"    , "lname" => "Paseur"   )
, array( "fname" => "Bill"   , "lname" => "O'Reilly" )
, array( "fname" => "Ray"    , "lname" => "Capece"   )
, array( "fname" => "John"   , "lname" => "Paseur"   )
)
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// WARNING: AN UNCAUGHT CONNECT ERROR WILL BARK OUT THE DB CREDENTIALS!
// $pdo = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, 'foo');
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$dsn = "mysql:host=$db_host;dbname=$db_name";
try
{
    $pdo = new PDO($dsn, $db_user, $db_word);
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}

// SHOW THE PDO CONNECTION OBJECT
var_dump($pdo);
echo PHP_EOL;


// SET PDO TO TELL US ABOUT WARNINGS OR TO THROW EXCEPTIONS
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
)
"
;

// RUN QUERY TO CREATE THE TABLE
try
{
    $pdos = $pdo->query($sql);
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}
// SHOW THE RESULTS OF THE QUERY
var_dump($pdos);


// PREPARE THE QUERY TO LOAD THE NAMES ("PREPARE" ONLY NEEDED ONCE)
$sql
=
"INSERT INTO my_table
(  fname,  lname ) VALUES
( :fname, :lname )
"
;
try { $pdos = $pdo->prepare($sql); } catch(PDOException $exc) { var_dump($exc); }


// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
    // USE THE ARRAY OF KEYWORD => VALUE TO ATTACH fname AND lname
    try
    {
        // RUN THE QUERY TO INSERT THE ROW
        $pdos->execute($person);
    }
    catch(PDOException $exc)
    {
        var_dump($exc);
        trigger_error($exc->getMessage(), E_USER_ERROR);
    }


    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $pdo->lastInsertId();
    echo "PDO INSERTED A ROW CONTAINING <b>" . $person['fname'] . ' ' . $person['lname']. "</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// SOMETHING TO SEARCH FOR
$fname    = "RAY";

// CREATE A QUERY FOR USE WITH BINDPARAM()
$sql = "SELECT id, lname FROM my_table WHERE fname = :fname ORDER BY lname, fname";

// CURSOR_SCROLL ALLOWS REPOSITIONING THE CURSOR - LIKE DATA_SEEK() - BUT SADLY NOT WITH MYSQL
$pdos = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

// BIND THE VARIABLE AND TRY THE QUERY
$pdos->bindParam(':fname', $fname, PDO::PARAM_STR);

try
{
    $pdos->execute();
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $pdos->rowCount();
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo "<br/>" . PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo "<br/>" . PHP_EOL;
}
echo PHP_EOL;


// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
echo "USING PDOStatement::FetchAll(PDO::FETCH_OBJ): ";
echo "<br/>" . PHP_EOL;
while ($row = $pdos->fetchAll(PDO::FETCH_OBJ))
{
    // ROW BY ROW PROCESSING IS DONE HERE
    foreach ($row as $key => $obj)
    {
        echo "$key: ";
        print_r($obj);
        echo PHP_EOL;
    }
}
echo PHP_EOL;


// DELETE A ROW FROM THE TABLE
$lname   = "O'Reilly";

// CONSTRUCT AND RUN A QUERY TO DELETE
$sql = "DELETE FROM my_table WHERE lname = :lname LIMIT 33";

// BIND THE VARIABLE AND TRY THE QUERY
$pdos = $pdo->prepare($sql);
$pdos->bindParam(':lname', $lname, PDO::PARAM_STR);
try
{
    $pdos->execute();
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}


// HOW MANY ROWS WERE AFFECTED BY DELETE?
$num     = $pdos->rowCount();
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>AFFECTED NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>AFFECTED $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;


// CONSTRUCT A QUERY THAT WILL FAIL
$sql = "SELECT oopsie FROM my_table ";
try
{
    $pdos = $pdo->query($sql);
}
catch(PDOException $exc)
{
    // SHOW PDOException AND PDOStatement
    var_dump($exc);
    var_dump($pdos);
}


The MySQLi Complete Code Example Using Prepared Statements


<?php // demo/EE_mysqli_prepare_example.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';

// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQLI
// MAN PAGE: http://php.net/manual/en/mysqli.overview.php
// MAN PAGE: http://php.net/manual/en/class.mysqli.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-stmt.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-result.php
// MAN PAGE: http://php.net/manual/en/class.mysqli-warning.php
// MAN PAGE: http://php.net/manual/en/mysqli.construct.php
// MAN PAGE: http://php.net/manual/en/mysqli.real-escape-string.php
// MAN PAGE: http://php.net/manual/en/mysqli.query.php
// MAN PAGE: http://php.net/manual/en/mysqli.errno.php
// MAN PAGE: http://php.net/manual/en/mysqli.error.php
// MAN PAGE: http://php.net/manual/en/mysqli.insert-id.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-array.php
// MAN PAGE: http://php.net/manual/en/mysqli-result.fetch-object.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.bind-param.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.bind-result.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.execute.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.store-result.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.num-rows.php
// MAN PAGE: http://php.net/manual/en/mysqli-stmt.fetch.php

// CREATE AN ARRAY OF NAMES TO USE FOR TEST DATA
$test_names_arrays = array
( array( "fname" => "Walter" , "lname" => "Williams" )
, array( "fname" => "Ray"    , "lname" => "Paseur"   )
, array( "fname" => "Bill"   , "lname" => "O'Reilly" )
, array( "fname" => "Ray"    , "lname" => "Capece"   )
, array( "fname" => "John"   , "lname" => "Paseur"   )
)
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
var_dump($mysqli);
echo PHP_EOL;


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, fname VARCHAR(24) NOT NULL DEFAULT ''
, lname VARCHAR(24) NOT NULL DEFAULT ''
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE RESULTS OF THE QUERY
var_dump($res);


// PREPARE A QUERY TO LOAD OUR DATA INTO THE TABLE
$sql = "INSERT INTO my_table ( fname, lname ) VALUES ( ?,? )";
$ins = $mysqli->prepare($sql);
if (!$ins)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// USE AN ITERATOR TO LOAD THE TABLE
foreach ($test_names_arrays as $person)
{
    // BIND INPUT VARIABLES TO THE QUERY
    $ins->bind_param('ss', $person['fname'], $person['lname']);

    // RUN THE QUERY TO INSERT THE ROW
    $ins->execute();

    // GET THE AUTO_INCREMENT ID FROM CONNECTION, NOT FROM STATEMENT
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>{$person['fname']}, {$person['lname']}</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}
echo PHP_EOL;


// PREPARE A SELECT QUERY
$sql = "SELECT id, lname FROM my_table WHERE (fname=? AND lname=?) OR id=? ORDER BY lname, fname";
$sel = $mysqli->prepare($sql);
if (!$sel)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

// BIND INPUT VARIABLES TO THE QUERY
$fname = "Ray";
$lname = "Paseur";
$id    = 1;
$sel->bind_param('ssi', $fname, $lname, $id);

// BIND RESULT VARIABLES TO THE QUERY
$sel->bind_result($my_id, $my_lname);

// RUN THE QUERY AND STORE THE RESULT
$sel->execute();
$sel->store_result();


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $sel->num_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;

// ACCESS AND PRINT THE RESULTS SET VIA THE BOUND RESULT VARIABLES
while ($sel->fetch())
{
    echo "id='$my_id' lname='$my_lname' ";
    echo PHP_EOL;
}

// RELEASE THE OBJECT
$sel->close();


// PREPARE A DELETE QUERY
$sql = "DELETE FROM my_table WHERE lname=? LIMIT 33";
$del = $mysqli->prepare($sql);

// BIND THE INPUT VARIABLE TO THE QUERY
$lname   = "O'Reilly";
$del->bind_param('s', $lname);


// RUN THE QUERY
$del->execute();

// HOW MANY ROWS WERE AFFECTED BY DELETE?
$num     = $del->affected_rows;
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>AFFECTED NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>AFFECTED $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}
echo PHP_EOL;


// CONSTRUCT A QUERY THAT WILL FAIL AT PREPARE TIME
$sql = "SELECT oopsie FROM my_table ";
$sel = $mysqli->prepare($sql);

// SHOW THE ERROR INFORMATION
if (!$sel)
{
    $err
    = "QUERY PREPARE() FAIL: "
    . $sql
    . '<br>ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}


Please give us your feedback!

If you found this article helpful, please click the "thumb's up" button below. Doing so lets the E-E community know what is valuable for E-E members and helps provide direction for future articles.  If you have questions or comments, please add them.  Thanks!


 

30
Comment
Author:Ray Paseur
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +7
16 Comments
 
 

Administrative Comment

by:ericpete
Ray_Paseur,

Congratulations! Your article has been published, and it gets a yes vote from me as well.

Thanks for a great summary.

ericpete
Page Editor
0
 
LVL 4

Expert Comment

by:brendonfeeley
Fantastic article, Ray_Paseur!

Very useful and very detailed. A 'Yes' from me :)
0
 
 

Administrative Comment

by:ericpete
Ray,

The Page Editors have determined that your article is worthy of being an Editors' Choice. Congratulations!

ep
PE
0
Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

 
LVL 53

Expert Comment

by:COBOLdinosaur
Just in time.  I just put the move to mysqli on my project list, so now I have this great reference to save me some time.  Thanks for writing it Ray

Cd&
1
 
LVL 53

Expert Comment

by:Scott Fell, EE MVE
Great article Ray.    

The other month I read an article about "all that is wrong with php" http://me.veekun.com/blog/2012/04/09/php-a-fractal-of-bad-design/  and I think your article is addressing this.  I have not fully immersed myself in php and my ears perked up about PDO  and prepared statements and now learning that it also supports other DB's.
1
 
LVL 110

Author Comment

by:Ray Paseur
@padas: That's both funny and true.  For better or worse, PHP has grown by topsy in the hyperactive age of the internet.  This FAQ page has some interesting insights. ;-)

When I started web programming (PHP3) in 1998, PHP was my obvious choice because the entire manual was available online.  I'm sure that factored into its popularity.  At that time nobody had thought about "convention over configuration" or any kind of linguistic consistency.  The same could be said about JavaScript which grew up fast enough that nobody noticed that the plus sign was used for both addition and concatenation.  Oops!
0
 

Expert Comment

by:rgb192
Thanks for the complete tutorial.
1
 
LVL 36

Expert Comment

by:Loganathan Natarajan
Thanks RAY
0
 

Expert Comment

by:shafer23
Ray - excellent article!  Thank you for sharing.
0
 
LVL 110

Author Comment

by:Ray Paseur
Just a note to those upgrading to PHP 5.4+ with MySQL 5.5.32.  This is apparently a fairly common occurrence.  The PDO constructor will issue a warning and throw an exception.
Warning:  PDO::__construct(): The server requested authentication method unknown to the client [mysql_old_password] in /path/to/pdo_example.php on line ##
This does not manifest in MySQLi, only in PDO and it appears to be an incompatibility between MySQL's old password hash (16 characters) and the new password hash (41 characters).  These pages document the issue and offer suggestions about how to address it.
https://dev.mysql.com/doc/refman/5.5/en/old-client.html
http://forums.laravel.io/viewtopic.php?id=8667
http://www.digitalaccesspass.com/forums/threads/1154-PHP-Mysql-compatibility-PDO-__construct()-authentication-method-unknown
0
 
LVL 58

Expert Comment

by:Gary
Shouldn't you be using prepared statements/bound data for MySQLi instead of the old way of sanitizing embedded data?
0
 
LVL 110

Author Comment

by:Ray Paseur
Regarding this the answer is usually "yes" and it depends to some extent on whether you trust your programmers.  If the processes are followed correctly either approach will work correctly and safely.  If you have novices in the development staff and you don't have pre-commit code reviews you are at risk.  A search for "MySQLi Complete Code Example Using Prepared Statements" will show a link point that more succinctly addresses this issue.  Like most things in software development it is a combination of science and art.
0
 
LVL 70

Expert Comment

by:Jason C. Levine
If you have novices in the development staff and you don't have pre-commit code reviews you are at risk

Ahhh, I don't worry about it.  Dreamweaver will handle it for me!

(listens for the sound of Ray's head exploding)

--------------------------------------

In all seriousness, I do wonder how many sites that were built with tools like Dreamweaver by non-coders are sitting out there on shared hosting.  When the ISP moves to 5.5...boom!
1
 
LVL 53

Expert Comment

by:COBOLdinosaur
What I am seeing from reputable hosts is they are migrating in stages by supporting multiple versions.  The site owners can set up a beta sub-domain and use .htaccess in the sub-domain to specify 5.5 as the parser.  That way issues can be detected and addressed without disrupting the main domain.

Any site operator who thinks they can just cut over to new versions of any major piece without full testing is an idiot and the internet is better of if they crash and disappear.

Cd&
0
 
LVL 110

Author Comment

by:Ray Paseur
@COBOLdinosaur:  I am seeing the same.  I have also verified that you can open simultaneous MySQL and MySQLi connections to the data base server and run some queries one way and other queries another.  I have not tested this with PDO yet, but I expect that PDO will also facilitate multiple connections.  All of this will make the transitions easier.
0
 
LVL 53

Expert Comment

by:COBOLdinosaur
I am in the process of migrating now, and I can confirm that using bth types of connects works fine.  There is a slight cost in DB overhead (less than .1%), and I am not see any response time penalty using them together.  

It means rather than having to take an outage and rush through a mass move, I can mix and match and do a slow careful migrate of single modules and avoid ooops! moments.  Once I have everything migrated, I will be able to cutover to PHP 5.5 knowing that everything ran in PHP in a test environment; so all it will take is changing the parser to 5.5 because all the code is know to run at that level.

Best of all ZERO DOWN TIME for a complete migration to PHP 5.5 including converting to PDO.

Cd&
1

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month