MySQL “Improved”
Found an old post I wrote, about the new “MySQLi” functions in PHP 5+. PHP6 is almost on the horizon, and I personally haven’t seen too much wide adoption of this new MySQL functionality.
The i in “MySQLi” stands for i”mproved”. While most of the functions between MySQL and MySQLi will work exactly the same (just by replacing the mysql_ with mysqli such as mysqli_query()), there are several cool things that come with this new "improved" MySQL library. One thing that’s available is to use the database connector itself as a "class", making it easy to create your own database library that is customized to your application. This makes it real easy to separate the logic and extend your database code, for those of us who are OO nuts 
Another advantage is that you can also use an SSL connection easily with the database, without jumping through hoops by just providing the path to the certificate. But the best improvement is the ability to use prepared statements. This makes queries (more) secure by telling MySQL exactly which types of values to expect, and also stores a template of those values in memory. The result is security, and since it’s cached, it ends up being much faster on repeat queries and inserts.
I will use the procedural version of the code in these examples, to keep it a bit easier, and I’m also leaving out error checking, for the sake of example.
[php]
//First we create our connection
$db_link = mysqli_connect(‘localhost’, ‘username’, ‘password’, ‘db name’);
//Now we want to prepare our statement
// So we first have to initialize it.
//Now we prepare the actual statement. The ? replace the variables
// We’re going to "insert" those later on
mysqli_stmt_prepare($db_link, ‘SELECT * FROM data WHERE stringvalue=? AND numbervalue=?’);
[/php]
So we’re prepared the statement, putting in question marks where there are going to be values placed in. We are “binding” a value into there, so we have to use a function mysqli_stmt_bind_param(). If we look at the parameters for this function, it is:
[php]
mysqli_stmt_bind_param(statement, types, variables);
[/php]
The types, are defined as:
- i – integer (any whole number)
- d – double (number, with decimals)
- s – string
- b – blob
Since we have to variables in the query above (string value and number value), our "types" are going to be "si". The first is a string (s) and the second is a number (i).
[php]
mysql_stmt_bind_param($statement, ‘si’, $string_value, $number_value);
[/php]
You might be wondering “What’s the point?”. It helps a great deal with security, when it comes to inserting data:
[php]
$statement = mysqli_prepare($link, ‘INSERT INTO table (name, idnumber, address) VALUES (?, ?, ?)’);
mysqli_stmt_bind_param($statement, ‘sis’, $name, $idnumber, $address);
$name = ‘First Last’;
$idnumber = 123456789;
$address = ‘I live here’;
mysqli_stmt_execute($statement);
mysqli_stmt_close($statement);
[/php]
As you can see, MySQL is now expecting certain types for the data which is being inserted, and treats it as such – strings as strings, integers as integers, and so on, and will automatically take the best precautions to ensure the data is valid.
For more info, check out the Zend information on MySQLi.
