Update MYSQL - Me no understand

@mankowitz: posted about the same time :)

I did not see a </table> tag in the original post so I assumed it's got more rows after the snip. Created a new row and put the input button there.

It may be a little beside the point, since the issue appears to be HTML and not PHP or MySQL, but if you want your scripts to keep running, you need to get off the MySQL extension.   PHP is doing away with MySQL support.  This article explains why and shows you what you need to do.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Ray, why do I get "Permission Denied" when trying to access your link?

Oki it worked... I understand the syntax now.....   :)


#Ray_Paseur
I will convert soon.... Hopefully.

Sorry, Dan.  That's an artifact of the Experts Exchange "approval" process for articles.  I can't approve my own article and nobody else seems to be approving articles now, so it's stuck in limbo as a result of an edit I made a week ago.  But the good news is that I can copy it and post it here, so I will do that in a moment.

Introduction (All good things must come to an end)

The original MySQL API is going away, deprecated by PHP in Version 5.5, with published statements that it will be removed in the future (see the red warning box).  Since PHP 5.5 is the current release (July 2013), the issues that will arise from the deprecation and removal are already before us.  In fact, some parts of the API are already deprecated. It's time to think about making a change in our scripts.  And if you're thinking about PHP 5.5, it's necessary to make the change now: "Please, note that this alpha version [php 5.5.0alpha2] also introduces the ext/mysql depreciation."

WHY in the World Would PHP Do This?
Great question!  The MySQL extension is by far the most popular data base extension used by PHP web sites.  It has the most robust documentation and support (consider phpMyAdmin, and after you've used that, go over to phpPgAdmin and compare features).  MySQL is thoroughly debugged, and it's accessible - you can find literally thousands of examples of PHP+MySQL code with a Google search.  So why would PHP take a draconian step?  In my opinion, it comes down to two issues.  

First, PHP is "growing up" as a programming language.  Its proponents have long felt the sting of being 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 are PHP machines -- the stench is created by the millions of incompetently written web sites that crash and get 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 get this explanation from P. Olson.  As you might imagine, there has been a firestorm of controversy over the change!

Second, the PHP authors are actually right -- the MySQL extension is simply terrible about security.  If it were any good, there wouldn't be a need for two million MySQL security articles!  There is nothing inherently wrong in the extension; the trouble arises in the way novice programmers misuse the extension.  As but one example, consider the use of external data in MySQL queries.  A common-sense implementation would prevent the use of a query variable that had not been escaped.  But the original implementation of the MySQL extension imposed no such requirement, and it's too late to add that now.  

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 6 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) is to deprecate 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, frustrates this effort.  The MySQL API is procedural, leading to code that is messy, difficult to maintain and impossible to reuse.  In contrast, the MySQLi and PDO extensions offer object-oriented interfaces.  And since they have been around for several years, it's about time we start using them!

OK What Should I Do?
First, adopt a coding standard that prohibits the use of the MySQL extension in all new work.  After today, you must never write mysql_query() again! Next, learn about the alternatives to MySQL.  Basically, you've got two choices: MySQLi or PHP Data Objects ("PDO").

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 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.

Should you choose MySQLi or PDO?  Either will work.  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 anybody who could believe that 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 depend on the amount of time you can devote to the required modifications and the amount of security you require.  Can you trust your programmers to remember to escape the external data before using it in a query?  If the answer is "yes" then MySQLi is a good choice, even if many computer scientists prefer PDO.

Do I Have to Change my Query Strings?
Yes, but your queries probably require very modest changes if you choose the MySQLi extension.  PDO uses an entirely different 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 a little more in the way of query changes.  

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 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 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 would be true of 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 see 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

// WORTH READING
// http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

// 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

Select allOpen in new window

MySQLi

// WORTH READING
// http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

// 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

Select allOpen in new window

PDO

// WORTH READING
// http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

// 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

Select allOpen in new window

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"   )
)
;

Select allOpen in new window

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 = "??";

Select allOpen in new window

Connect to the Server and Choose the Data Base
Each of these connections have 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 referenced man page.  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);

Select allOpen in new window

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);

Select allOpen in new window

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);

Select allOpen in new window

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 about error 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 );

Select allOpen in new window

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 ''
)
"
;

Select allOpen in new window

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);

Select allOpen in new window

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);

Select allOpen in new window

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);

Select allOpen in new window

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);
}

Select allOpen in new window

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;
}

Select allOpen in new window

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;
}

Select allOpen in new window

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;
}

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

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);
}

Select allOpen in new window

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 $res mysqli_result object.  With PDO, the number is found by calling the rowCount() method on the PDOStatement object.

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;
}

Select allOpen in new window

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;
}

Select allOpen in new window

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;
}

Select allOpen in new window

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.

In PDO, 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.

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;
}

Select allOpen in new window

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;
}

Select allOpen in new window

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;
    }
}

Select allOpen in new window

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.

MySQL

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

Select allOpen in new window

MySQLi

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

Select allOpen in new window

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 in HEREDOC variables!  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;
}

Select allOpen in new window

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;
}

Select allOpen in new window

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);
}

Select allOpen in new window

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);
}

Select allOpen in new window

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);
}

Select allOpen in new window

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;
}

Select allOpen in new window

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;
}

Select allOpen in new window

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;
}

Select allOpen in new window

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;
}

Select allOpen in new window

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

Select allOpen in new window

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;
}

Select allOpen in new window

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

Select allOpen in new window

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);
}

Select allOpen in new window

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"
}

Select allOpen in new window

Conclusion
With more than 6 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 begin upgrading our scripts.  This article has shown some links to the relevant man pages and some examples that illustrate the programming changes required to bring our programming into 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>';


// WORTH READING
// http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

// 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;
}

Select allOpen in new window

The MySQLi Complete Code Example

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


// WORTH READING
// http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

// 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;
}

Select allOpen in new window

The PDO Complete Code Example

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


// WORTH READING
// http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

// 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);
}

Select allOpen in new window

The MySQLi Complete Code Example Using Prepared Statements

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


// WORTH READING
// http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

// 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);
}

// NB: THESE VARIABLES MUST EXIST BEFORE THEY CAN BE BOUND!
$person = current($test_names_arrays);

// BIND INPUT VARIABLES TO THE QUERY
$ins->bind_param('ss', $person['fname'], $person['lname']);

// USE AN ITERATOR TO LOAD THE TABLE
foreach ($test_names_arrays as $person)
{
    // 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
$sel->bind_param('ssi', $fname, $lname, $id);
$fname = "RAY";
$lname = "PASEUR";
$id    = 1;

// 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
$del->bind_param('s', $lname);
$lname   = "O'Reilly";

// 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);
}

Select allOpen in new window

Thank you.
Luckily (sadly?) I no longer work in programming so I won't have to rebuild old scripts.

There's definitely going to be a market for php programmers to update old web apps to conform to the new/old way of using PHP/MySQL.

Dan, some things never die.  I recently encountered a development specification that showed the data format in COBOL terms with 80-column records ;-)

amazing guide. .. i read it all, almost. I jumped some examples, but i will convert to mysqli tonight.

It does not look hard, mysql and mysqli are almost the same....

:D I like that.

Will mysqli die in 2 years? Should i consider PDO instead?