How to work with prepared statements

A method of the mysqli class

Method Description
prepare($sql_statement) Prepares the specified SQL statement for execution and returns a mysqli_stmt object. The specified SQL statement can contain zero or more question mark (?) parameters. If an error occurs, this method returns a FALSE value.

Four methods of the mysqli_stmt class

Method Description
bind_param($fs, $v1[, $v2]...) Binds the specified values to the parameters in the prepared statement. The first parameter is a format string that specifies the data types for all parameters (s for string, i for integer, d for double, b for BLOB).
bind_result($v1[, $v2]...) Binds the columns in the result set to the specified variables.
execute() Executes the prepared statement. Returns TRUE for success and FALSE for failure.
fetch() Gets the values for the result set columns and stores them in the bound variables.
close() Closes the prepared statement.

How to execute a prepared statement that returns a result set

      $query = "SELECT productCode, productName, listPrice
                FROM products WHERE categoryID = ?";
      $statement = $db->prepare($query);
      $statement->bind_param("i", $category_id); 
      $statement->bind_result($code, $name, $listPrice); 
      $statement->execute();

How to display the result set

      <?php while($statement->fetch()) : ?>
      <tr>
        <td><?php echo($code); ?></td>
        <td><?php echo($name); ?></td>
        <td><?php echo($listPrice); ?></td>
      </tr>  
      <?php endwhile; ?>

How to close the statement

      $statement->close();

How to execute a prepared statement that modifies data

      $query = "INSERT INTO products (categoryID, productCode, productName, listPrice) 
                VALUES (?, ?, ?, ?)"; 
      $statement = $db->prepare($query);
      $statement->bind_param("issd", $category_id, $code, $name, $price); 
      $success = $statement->execute(); 
      if ($success) { 
        $count = $db->affected_rows; 
        echo("<p>$count product(s) were added.</p>");
      } else {
        $error_message = $db->error;
        echo"<p>An error occurred: $error_message.</p>");
      }
      $statement->close();

Back