Home arrow MySQL arrow Page 4 - Performing Basic Tasks with MySQL 4.1 and Above, using mysqli with PHP 5

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

For anyone who has spent a few weeks working with PHP 5, the plethora of cool improvements and new features added to this incarnation of the language has brought a new, more powerful level for developing and deploying Web applications. These include the implementation of exceptions and the brand new object model. But what if I tell you that now you can use PHP 5 to work with MySQL, using an object-oriented approach?

TABLE OF CONTENTS:
  1. Performing Basic Tasks with MySQL 4.1 and Above, using mysqli with PHP 5
  2. Performing basic operations: connecting to MySQL, running queries and more
  3. Leveraging the real power of the “mysqli” extension: running multiple queries
  4. Preparing SQL queries: using the “prepare()”, bind_param()” and “execute()” methods
By: Alejandro Gervasio
Rating: starstarstarstarstar / 16
June 27, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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! 



 
 
>>> More MySQL Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: