New domains
• Register a new AC.UK domain
  
  
   

The original MySQL extension will soon become obsolete: http://news.php.net/php.internals/53799

But what does this mean? Well if you use PHP and MySQL then you will certainly be using, or have code which uses, the MySQL extension. There are TWO alternatives:

• the MySQLi (Improved Extension), or
  
• the PDO extension
  

Either of these should now start to be used as opposed to the original MySQL extension as this old extension will eventually become obsolete.

BUT which to choose?  There is a discussion about which maybe the better here: zend-framework-community
and here: stackoverflow.com-mysqli-or-pdo-what-are-the-pros-and-cons.
There are also lots of other discussions on whether to choose PDO or MySQLi here: google.co.uk: q=pdo+or+mysqli&More=Discussions

The good news is we provide both MySQLi hosting and PHP PDO hosting on all our servers (excluding Windows), and we have found the new extensions run faster, and using them in an object oriented style means the code is easier to read and manage so long term management will be simpler too. Below is a short, NOT definitive, look at both MySQLi and PHP PDO.

^back to top^

The NEWer PHP MySQLi-extension

The MySQLi Improved extension is not new, its been around for several years, but it is not yet as commonly used as the old original MySQL extension. On the PHP site they now recommend the use of MySQLi extension (or PDO). Its easy to use and not that much different from the old extension, take a look at the PHP code comparisons below. The difference in time to execute queries is significant - on average the queries we tested completed 20% quicker (and upto 50% quicker on occasions) than the old extension. Add to this the fact that you can use the object oriented API and it makes for a good long term solution.

1.a. Below is a some simple MySQLi code to insert and select data - NOTE that the "class" should be within a single file so it can be included when required, it is shown here within the same page for simplicity:

<?php
define("DEBUG", 1);
define("DB_DRIVER", "mysql");
define("DB_SERVER", "localhost");
define("DB_PORT", "3306");
define("DB_NAME", "dbname");
define("DB_USER", "dbuser");
define("DB_PASS", "dbpass");
class db extends mysqli
{
    protected static $instance;
    private function __construct()
    {
        mysqli_report(MYSQLI_REPORT_OFF);
        @parent::__construct(DB_SERVER, DB_USER, DB_PASS, DB_NAME, DB_PORT);
        if(mysqli_connect_errno()) {if(DEBUG) throw new exception(mysqli_connect_error(), mysqli_connect_errno());}
    }
    public static function getInstance()
    {
        if( !self::$instance ) self::$instance = new self();
        return self::$instance;
    }
}
$sql = db::getInstance();
$sql->query("INSERT INTO `test` (`integer`, `string`) VALUES(null,'just a string')");
$result = $sql->query("select * from test");
while( $row = $result->fetch_assoc() )
{
  echo $row['integer']." ".$row['string']."<br>";
}
$sql->close();
?>


Compare the above PHP code to the original code used with the standard MySQL extension below. The class in the code above requires extending to add error trapping, there is an example here: stackoverflow.com/questions/1820421/extending-the-mysqli-class

1.b. Here is the OLD MySQL code to insert and select data:

<?php
define("DEBUG", 1);
define("DB_DRIVER", "mysql");
define("DB_SERVER", "localhost");
define("DB_NAME", "dbname");
define("DB_PORT", "3306");
define("DB_USER", "dbuser");
define("DB_PASS", "dbpass");
$link = mysql_connect(DB_SERVER,DB_USER,DB_PASS) or die ("Unable to connect to SQL server");
mysql_select_db(DB_NAME,$link) or die ("Unable to select database");
$result = mysql_query("INSERT INTO test (`integer`, `string`) VALUES (null, 'old strings')");
$result = mysql_query("select * from test");
while ($row = mysql_fetch_object($result))
{
 echo $row->integer." ".$row->string."<br>";
}
mysql_close();
?>


^back to top^

Updating PHP code - migrating from MySQL to MySQLi

As can be seen from the above examples the difference is not so radical as first impressions may suggest, and the transition can be painless once you get used the different syntax. Also by extending MySQLi with your own class (as shown in 1.a. above) will make your scripts far easier to update when the inevitable upgrades force further code changes.

Many of the older PHP scripts used the mysql_result to set variables like this:

1.c. Here is the OLD MySQL code you may have used to set variables from database data:

<?php
// connection to MySQL already made...
while ($i < mysql_num_rows($customer_result))
{
   $name=mysql_result($customer_result,$i, 'name');
   $email=mysql_result($customer_result,$i, 'email');
}
?>


This made it simple to use the variables as-is rather than thinking how to access it from an array (eg. $row['email']), and if all your scripts are coded this way then you need a quick and simple solution. If you take the old PHP code and swap the "while" loop with the the code below, it will take the $row array and pair-off each key-value pairs, so enabling you to use the variables as-is, which will mean the rest of the old PHP script should work without change.

1.d. MySQLi code to set variables from database data:

<?php
// connection to MySQL already made...
while( @$row = $result->fetch_assoc() )
{
  if ($row) foreach ($row AS $key => $value)  $$key = $value;
  echo "$name - $email";
}
?>


 

^back to top^

PHP PDO-extension

The great thing about PDO is that it will work with a long list of databases (not just MySQL, but ORACLE, MS SQL, IBM, etc. see full list). As it is so extensive you may have thought it would be difficult to use! Well its not. Or runs slow! No, our tests showed it is faster than the old MySQL extension (but a little slower than the newer MySQLi extension).

2.a. Here is some simple PDO code to insert and select data:

<?php
define("DEBUG", 1);
define("DB_DRIVER", "mysql");
define("DB_SERVER", "localhost");
define("DB_PORT", "3306");
define("DB_NAME", "dbname");
define("DB_USER", "dbuser");
define("DB_PASS", "dbpass");
try
{
 $dbh = new PDO(DB_DRIVER.":host=".DB_SERVER.";port=".DB_PORT.";dbname=".DB_NAME, DB_USER, DB_PASS);
 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
 catch(PDOException $e) { if (DEBUG) echo $e->getMessage();}
try
{
 $result = $dbh->query("INSERT INTO test (`integer`, `string`) VALUES (null, 'next string')");
 echo $dbh->lastInsertId();// show last insert id.
 $result = $dbh->query("select * from test");
 foreach($result as $row)
 {
  echo $row['integer']." ".$row['string']."<br>";
 }
}
 catch(PDOException $e) { if (DEBUG) echo $e->getMessage();}
?>


Its good practice to "include" a generic class into each script needing a database connection, so try this article on how to write your PDO class:
http://stackoverflow.com/questions/2047264/use-of-pdo-in-classes
also:
http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html

^back to top^

Prepared SQL statements

One of the main advantages of MySQLi and PDO is you can use Prepared SQL statements which helps protect your website from exploit SQL injections, also the performance increase when used in large loops will be substantial.

The PHP MySQLi prepare function binds the query by referencing the "?" placeholders within the query with the parameters, as shown below, which is very different from the old method as in code 2.c. below.

2.b. MySQLi prepared statement which helps prevent SQL exploits (you should still check user input is as expected prior to sending data to a query of course):

<?php
// connection to MySQL already made...
$anumber=5;
$str = 'a string';
$stmt = $sql->prepare("INSERT INTO `test` (`integer`, `string`) VALUES(?,?)");
$stmt->bind_param('is', $anumber, $str);// "is"=integer string - d for double
$stmt->execute();
?>


2.c. OLD method of cleansing input prior to running a query in MySQL:

<?php
// connection to MySQL already made...
 $username =  mysql_real_escape_string($_POST['username']);
 $password = mysql_real_escape_string($_POST['password']);
 $qstr = "SELECT * from members where username ='$username' and password ='$password'";
 $result = mysql_query($qstr);
?>


There is also a good article on how to use MySQLi prepared-statments here: databasejournal - Connecting-and-prepared-statements-with-the-mysqli-extension, and also here: zend.com/article/686.

The PHP PDO prepare function binds the query by referencing "named" placeholders, prefixed with a colon (:), so making it far easier to read and edit.

2.d. PDO prepared statement which also helps prevent SQL exploits (you should still check user input is as expected prior to sending data to a query of course):

<?php
// connection to MySQL already made...
$name = 'smith';
$stmt = $dbh->prepare("SELECT * FROM customer WHERE name = :name");
$stmt->bindParam(':name', $name, PDO::PARAM_STR, 5);
$stmt->execute();
?>


There is also a good article on how to use PDO prepared-statments here: phpro.org/tutorials/Introduction-to-PHP-PDO

 

Conclusion

We suggest you start to phase out the use of the old MySQL extension and ensure any new PHP code you write uses the new MySQLi extension.  Over time (possibly the next 24 months) phase out the use of the old extension from all existing PHP code.

If you are updating PHP scripts which used the old extension then it will be least time consuming to convert them to use the newer MySQLi-extension. If you are writing new code for a new application then choose PDO as this provides a consistent interface to many different databases so should help future proof your PHP application.

Need assistance with this change? Please contact us and we will provide you with a quote.

We provide MySQLi hosting, and EU Reseller hosting including MySQLi or UK Reseller hosting including MySQLi

Also, we provide PHP PDO hosting.

If you would like to receive further details please contact us.

 

---

Post to del.icio.us | Post to Yahoo! | Digg it? | DigThishost

^back to top^

---