Lamp development essentials (13): Use mysql_stmt (preprocessing statement) in PHP to process SELECT query results

Many PHP scripts usually execute the same query statement in other parts except the parameters. For this type of repeated query, different parameters are used for each iteration, mySQL 4.1 provides a mechanism named prepared statement. It can send the entire command to the MySQL server once, and only the parameters change in the future. The MySQL Server only needs to analyze the command structure once. This not only greatly reduces the amount of data to be transmitted, but also improves the processing efficiency of commands. You can use the mysqli_stmt class objects provided in mysqli extension mode to define and execute parameterized SQL commands. The following is a query process implemented using this mechanism.

Example 1:

<? PHP

$ Db = new mysqli ("localhost", "user", "password", "testdb ");

If (mysqli_connect_errno ()){
Printf ("error: % s/n", mysqli_connect_error ());
Exit;
}
Else
{
If ($ stmt = $ db-> prepare ("select ID, name, author, price from book where name like? "))
{

$ Stmt-> bind_param ('s ', $ N );
$ N = "% P % ";

$ Stmt-> execute ();

$ Stmt-> store_result ();
$ Stmt-> bind_result ($ id, $ name, $ author, $ price );
While ($ stmt-> fetch ())
{
Printf ("% s: % s, % s, % S <br/>", $ id, $ name, $ author, $ price );
}
$ Stmt-> close ();
}
$ Db-> close ();
}
?>

Example 2. Object Oriented Style

<? PHP
$ Mysqli = new mysqli ('localhost', 'My _ user', 'My _ password', 'World ');

/* Check connection */
If (mysqli_connect_errno ()){
Printf ("Connect failed: % s/n", mysqli_connect_error ());
Exit ();
}

$ Stmt = $ mysqli-> prepare ("insert into countrylanguage values (?, ?, ?, ?) ");
$ Stmt-> bind_param ('sssd', $ code, $ language, $ official, $ percent );

$ Code = 'deu ';
$ Language = 'bavarianc ';
$ Official = "F ";
$ Percent = 11.2;

/* Execute prepared statement */
$ Stmt-> execute ();

Printf ("% d row inserted./N", $ stmt-> affected_rows );

/* Close statement and connection */
$ Stmt-> close ();

/* Clean up table countrylanguage */
$ Mysqli-> query ("delete from countrylanguage where language = 'bavarianc '");
Printf ("% d row deleted./N", $ mysqli-> affected_rows );

/* Close connection */
$ Mysqli-> close ();
?>

Example 3. Procedural Style

<? PHP
$ Link = mysqli_connect ('localhost', 'My _ user', 'My _ password', 'World ');

/* Check connection */
If (! $ Link ){
Printf ("Connect failed: % s/n", mysqli_connect_error ());
Exit ();
}

$ Stmt = mysqli_prepare ($ link, "insert into countrylanguage values (?, ?, ?, ?) ");
Mysqli_stmt_bind_param ($ stmt, 'sssd', $ code, $ language, $ official, $ percent );

$ Code = 'deu ';
$ Language = 'bavarianc ';
$ Official = "F ";
$ Percent = 11.2;

/* Execute prepared statement */
Mysqi_stmt_execute ($ stmt );

Printf ("% d row inserted./N", mysqli_stmt_affected_rows ($ stmt ));

/* Close statement and connection */
Mysqli_stmt_close ($ stmt );

/* Clean up table countrylanguage */
Mysqli_query ($ link, "delete from countrylanguage where language = 'bavarianc '");
Printf ("% d row deleted./N", mysqli_affected_rows ($ link ));

/* Close connection */
Mysqli_close ($ link );
?>

The following two examples are output:

1 row inserted.
1 row deleted.