Feb 242013
 
Article MySQL

We have already explained in a previous post how to connect to and work with a MySQL database in PHP. The examples in that post use the “mysql” PHP extension, which is compatible with older versions of PHP.

But, since PHP 5.0, the new “mysqli” (MySQL Improved) extension is available, with more functionality than the previous one. This post is about the new functionality implemented in mysqli, and includes a collection of code samples to connect, query and modifiy a mysql database with this new extension.

Prepared Statements

Probably one of the most relevant improvements in the new mysqli extension is the addition of prepared statements.

A MySQL database is queried and modified with SQL sentences SELECT,INSERT,UPDATE and DELETE. For instance:

In the “classical” programming style, the PHP script composes the SQL sentence in a string, concatenating the values of some variables:

But there are two main problems in generating SQL sentences like this:

– Often, the script must perform the same action repeatedly, for instance to insert a number of records in a table, using sentences that only differ in the values of the fields. In the old style, the SQL driver must parse the string every time, spending processing power.

– The values used in SQL sentences often come from external sources, such as a data file or a form in a web page. This can cause errors and is a vulnerability that can be used by hackers to perform a “SQL Injection” attack against the site.

Using mysqli, it is possible to “”prepare” the sentence, using a template sentence where the actual values are replaced with a placeholder sign “?”. For instance:

This template sentence is analyzed only once, and can be executed multiple times in the script, feeding it with different values each time. On the other hand, the way these values are treated prevents a SQL Injection attack.

Connecting and disconnecting from the database

The connection is established with a call to¬†“mysqli()”. This function returns a database handler object that is then used to prepare the sentences to be executed.

The connection is closed with a call to the “close()” method of the database handler.

Prepare and execute SQL SELECT sentences

The template sentence is passed as an argument to the “prepare()” method of the database handler. This method returns an statement handler:

Next, if the sentence used parameters, these parameters are associated with php variables using the “bind_param()” method of the statement handler:

The first argument to bind_param() is a series of characters that indicates the data type of each of the parameters, in the same sequence as they appear in the SQL statement:

  • i – integer number
  • d – decimal number
  • s – string

In the example above, “si” means that the value of the variable $country is a string, and the value of the variable $age is an integer number.

Next, the fields returned by the SELECT statement are associated with php variables using the “bind_result()” method:

In the example, the fields “first_name” and “name” in the “users” table are associated with the php variables “$first_name” and “$name”.

Finally, the desired values are assigned to the php variables bound to the parameters in the template statement, the statement is executed and the resulting rows are retrieved in a “while” loop. This step can be performed repeatedly for different parameter values, without having to call again the prepare, bind_param and bind_result methods.

Prepare and execute SQL statements INSERT and UPDATE

The insert and update statements are prepared and executed in the same way as the select statements, calling prepare and bind_param. As these statemements do not return a result set, there is no need to make a call to the bind_result method.

References

MySQL Improved Extension

 Posted by at 5:27 pm

 Leave a Reply

(required)

(required)