Full Life = Work + Life;

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($stmt) 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($stmt) 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($stmt) 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($stmt) 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 */
}