How to work with prepared statements

Some methods of the PDO class

Method Description
prepare($sql_statement) Prepares the specified SQL statement for execution and returns a PDOStatement object. The specified statement can contain zero or more named (:name) or question mark (?) parameters.
lastInsertId() After an INSERT statement has been executed, this method gets the ID that was automatically generated by MySQL for the row.

Some methods of the PDOStatement class

Method Description
bindValue($param, $value) Returns TRUE for success and FALSE for failure.
execute() Executes the prepared statement. Returns TRUE for success and FALSE for failure.
fetchAll() Returns an array for all of the rows in the result set.
fetch() Returns an array for the next row in the result set.
rowCount() Returns the number of rows affected by the last statement.
closeCursor() Closes the cursor and frees the connection to the server so other SQL statements may be issued.

How to use the fetchAll() method to return a result set

      $query = 'SELECT * FROM products'; 
      $statement = $db->prepare($query);
      $statement->execute(); 
      $products = $statement->fetchAll(); 
      $statement->closeCursor(); 
      foreach ($products as $product) { 
        echo($product['productName'] . "<br />\n");
      }

How to use the fetch() method to loop through a result set

      $query = 'SELECT * FROM products'; 
      $statement = $db->prepare($query); 
      $statement->execute(); 
      $product = $statement->fetch(); 
      while ($product != null) { 
        echo($product['productName'] . "<br />\n");
        $product = $statement->fetch(); 
      } 
      $statement->closeCursor();

How to use named parameters

      $query = 'SELECT * FROM products 
                WHERE categoryID = :category_id AND listPrice > :price';
      $statement = $db->prepare($query);
      $statement->bindValue(':category_id', $category_id); 
      $statement->bindValue(':price', $price); 
      $statement->execute(); 
      $products = $statement->fetchAll(); 
      $statement->closeCursor();

How to use question mark parameters

       $query = 'SELECT * FROM products WHERE categoryID = ? AND listPrice > ?'; 
       $statement = $db->prepare($query); 
       $statement->bindValue(1, $category_id); 
       $statement->bindValue(2, $price); 
       $statement->execute(); 
       $products = $statement->fetchAll(); 
       $statement->closeCursor();

How to modify data

      // Sample data 
      $category_id = 2; 
      $code = 'hofner'; 
      $name = 'Hofner Icon'; 
      $price = '499.99'; 
       
      // Prepare and execute the statement 
      $query = 'INSERT INTO products 
                VALUES (categoryID, productCode, productName, listPrice) 
                (:category_id, :code, :name, :price)';
      $statement = $db->prepare($query); 
      $statement->bindValue(':category_id', $category_id); 
      $statement->bindValue(':code', $code); 
      $statement->bindValue(':name', $name); 
      $statement->bindValue(':price', $price); 
      $success = $statement->execute(); 
      $row_count = $statement->rowCount(); 
      $statement->closeCursor(); 
       
      // Get the last product ID that was automatically generated
      $product_id = $db->lastInsertId(); 
       
      // Display a message to the user 
      if ($success) { 
        echo("<p>$row_count row(s) was inserted with this ID: $product_id</p>\n");
      } else {
        echo("<p>No rows were inserted.</p>\n");
      }

Back