Web (PHP, Javascript)

PHP mysqli Prepared Statements

Recently I had a close look at the mysqli prepared statements. Why use prepared statements in php if you can easily concatenate a statement string and then execute it?

There are several reasons:

  1. Performance is an interesting issue, for example an article http://dealnews.com/developers/php-mysql.html states that prepared statements make a difference.
  2. From the programming point of view though one gets character escaping written by someone who went through the trouble to do it properly and it is guaranteed to hapen each time.
  3. From the style perspective – most your SQL statements are created once and are generally in one place, therefore it is easy to make changes if your schema has been changed.

Let’s start from the beggining. mysqli is an extended version of the well known mysql library. Why is that a good thing? Well most functions are called the same and therefore porting from one to the other will not be as difficult – one does not need to change the approach, just make sure that everything works and the names of functions have a ‘mysqli’ instead of ‘mysql’.

mysqli supports procedural style interface as well and object-oriented. The procedural one is a little richer, so if you are using the OO one, like I do and can’t find some method – look for a procedure with a similar name and there is a good chance you will find it.

Few library methods of my own, just for reference:

  /*
   * Connects to the DB
   */
  public function connect() {
    /* Connecting, selecting database */
    $this->_link = new mysqli($this->_host, $this->_user, $this->_password, $this->_database);
    /* check connection */
    if (mysqli_connect_errno()) {
        die("Could not connect: ".mysqli_connect_error());
    }
  }

  /*
   * Closes the connection to the DB
   */
  public function disconnect() {
    $this->_link->close();
  }

  public function prep_stmt($query) {
    $stmt = $this->_link->prepare($query) or die (
      "Could not prepare statement<br>".$query."<br>".$this->_link->error);
    return $stmt;
  }

Now we get to actually using it:

$stmt = $db->prep_stmt("select * from .... where userid = ? and username = ?"); 

/* Binding 2 parameters. */
$stmt->bind_param("is", $userid, $username);

/* Binding 2 result. */
$stmt->bind_result($isbn, $title, $author, $coef, $bookid);

/* Executing the statement */
$stmt->execute( ) or die ("Could not execute statement");

/*
 * Making PHP buffer the whole result,
 * not recommended if there is a blob or
 * text field as PHP eats loads of memory
 */
$stmt->store_result();
while ($stmt->fetch()) {
 /*
  * Here you can use the variables $isbn, $title, $author, $coef, $bookid,
  * which contatin the data for 1 row.
  */
}

The interesting thing about binding parameters is that you are binding variables rather than values, so this will work:

$stmt->bind_param("is", $userid, $username);

$userid = 15;
$username = "gfg";

/* Executing the statement */
$stmt->execute( ) or die ("Could not execute statement");

while ($stmt->fetch()) {
  // ....
}

$userid = 16;
$username = "jhgfd";

/* NO NEED TO BIND THE PARAMETERS AGAIN */

/* Executing the statement */
$stmt->execute( ) or die ("Could not execute statement");

while ($stmt->fetch()) {
  // ....
}

And of course I found a problem. If you need to display hierarchical data of at least 2 levels, for example you fetch information about books by one complex SQL statement and fetch comments for books by a different SQL statement, which you execute for each book.You cannot use the same connection to the database for 2 concurrent prepared statements. As long as the first result set has not been closed the second statement will cause an error. Ok, so I have tried using 2 different connections – still could not do it. PHP would crash and would not display anything at all. Not the most pleasant of experiences. So the way around it was to create a second connection and use a simple SQL statement – not a prepared one – then everything worked. I hope this will be fixed in the next mysqli release.Continuing the previous example:

$stmt = $db->prep_stmt("select * from .... where userid = ? and username = ?"); 

/* Binding 2 parameters. */
$stmt->bind_param("is", $userid, $username);

/* Binding 2 result. */
$stmt->bind_result($isbn, $title, $author, $coef, $bookid);

/* Executing the statement */
$stmt->execute( ) or die ("Could not execute statement");

/*
 * Making PHP buffer the whole result,
 * not recommended if there is a blob or
 * text field as PHP eats loads of memory
 */
$stmt->store_result();
while ($stmt->fetch()) {
 /*
  * Here you can use the variables $isbn, $title, $author, $coef, $bookid,
  * which contatin the data for 1 row.
  */
  print "<tr>".
  "<td>".$isbn."</td>".
  "<td>".$title."</td>".
  "<td>".$author."</td>".
  "</tr><tr><td>";

  $result = $db2->query("select ... where dbBookID = $bookid and dbUserID != $userid");

  /* print some comments for the book above */
}
Standard

11 thoughts on “PHP mysqli Prepared Statements

  1. Is this a typo: $stmt->execute($stmt)
    ??

    I get this error when I use that syntax:
    mysqli_stmt::execute() expects exactly 0 parameters, 1 given in

    I thought it would be trivial to find an example of using bind variables with a MySQL SELECT statement, but it’s proven really difficult! I got my update and insert statements working with them, but returning results from a prepared statement still isn’t working from me. Thanks for your tutorial here!

  2. sam s says:

    Great article! I have a silly question, i was able to get my code to work using the code you outlined above. In addition to this code, am I supposed to make some procedural code on the mysql server itself?

  3. Andrej Kazakov says:

    Prepared statements are a function of the RDBMs. I am not quite sure, how it works internally, but it definitely does not require anything else from you as long as the server supports it (most of them do).

  4. Working on a new website using the mysqli OO stuff to do prepared statements (this article helped a LOT, by the way), but I’m getting some weird errors, and wondering if you’ve run into anything like them.

    For some reason when I try to select from the database with a prepared statement using a string parameter, it returns 0 rows, even when there are some there. For instance:

    $stmt = $db->prep_stmt(“SELECT * FROM Users WHERE Username = ?”);
    $stmt->bind_parm(‘s’, $username);
    $stmt->execute();

    Then when I do a check using $stmt->num_results, it’s equal to 0.

    But, if I run the query in the procedural style, which doesn’t do the parameterization, it comes back ok, with 1 row like it should.

    $result = $db->executeQuery(“SELECT * FROM Users WHERE Username = ‘$username'”);

    $db->executeQuery($query) calls mysqli_query($this->connection, $query)

    Any ideas? Thanks in advance for any time you spend helping, I really appreciate it.

  5. skillet-thief says:

    I am having the same problem as Josh A (and have been looking at my code for 3 days now…). I wonder if there is a bug somewhere…

  6. I’m liking the looks of the excerpt from the library class you have with the methods for connecting to the database, etc…

    I’m looking to make a class like that to handle database connection stuff. What’s the best way to do this? Can you send me or post the source code for the whole class?

    Shouldn’t it be a static class? (because we only need one connection to the database)
    http://www.karlbunyan.co.uk/2004/12/php-5-static-classes.aspx

    And shouldn’t the connect() and disconnect() functions be static? That way you call them like so: Database::connect(), where Database is the name of the class.

    Here’s what I mean:
    http://bryanstamour.com/?p=20

    How do we write static classes in php?
    http://wiki.php.net/rfc/static-classes

    Apparently, we do so by declaring the class abstract (because abstract classes cannot be instantiated–You can’t create objects of an abstract class) and declaring all the properties and methods and methods static.

    I guess that’s what I’ll do… Maybe I’ll post my solution.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s