Preparing SQL queries: using the “prepare()”, bind_param()” and “execute()” methods - MySQL

As I said before, you can prepare a query before it is executed, and insert markers inside the query in question, which will be replaced with real values. Again, this process will be best understood with an example, thus take a look at the code listing below, which uses a set of new methods to first prepare a query and then execute it:

$mysqli=new mysqli('host','user','password','database');
if(mysqli_connect_errno()){
    trigger_error('Error connecting to host. '.$mysqli-
>error,E_USER_ERROR);
}
// define parameter
$name='Alejandro Gervasio';
// prepare query
if($stat=$mysqli->prepare("SELECT email FROM users WHERE
name=?")){
    // bind parameter to marker
    $stat->bind_param('s',$name);
    // run query
    $stat->execute();
    // bind result set to $email variable
    $stat->bind_result($email);
    // fetch result
    $stat->fetch();
    // display row
    echo 'User with name '.$name.' has the following email
address: '.$email;
}
// close connection
$mysqli->close();

Examine the snippet shown above. As you can see, it uses some new methods, which work together to prepare a SELECT statement and then run it.

In this example, after defining the $name parameter, the “prepare()” method prepares the query, which includes only one marker defined by a question sign (?).  Then, the parameter is bound to this marker as a string value (hence the “s” argument) via the “bind_param()” method, and finally the query is run by the “execute()” method.

So far, this process is fairly comprehensive, but how does the script fetch the corresponding result from the database? Here is where the “bind_result()” and “fetch()” methods take place, since the first one attaches the corresponding result to the $email variable, while the second one populates this variable with the values of the returned row.

Assuming that I included myself as a new row of the “USERS” database table, the previous example would return the following result:

User with name Alejandro Gervasio has the following email
address: alejandro@mydomain.com

Wasn’t that cool? I’m sure you’ll agree with me that preparing queries with the appropriate markers is a very powerful feature of the “mysqli” extension. Try including additional markers within your queries and see what happens in each case. It’s really worthwhile, trust me.

To wrap up

That’s all for the moment. Over this first article, I covered some of the most significant methods and properties that come with the “mysqli” library. From connecting to MySQL and running multiple queries, to binding SQL statements to specific parameters, the experience hopefully has been instructive.

In the second tutorial, I’ll be demonstrating how to use the useful “commit()” and “rollback()” methods, among other handy things, so you don’t have excuses to miss it!