MySQL & PHP 4.1 Prepared Statement Class

Small, short code snippets that other people may find useful. Do you have a good regex that you would like to share? Share it! Even better, the code can be commented on, and improved.

Moderator: General Moderators

Post Reply
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

MySQL & PHP 4.1 Prepared Statement Class

Post by infolock »

Code: Select all

<?php
/**
 * This class creates a prepared statement for MySQL 4.1.x+.  Is created due to php versions < 5 that do not have
 * a prepared API built in.  Should be used when issuing the same query 3 or more times.
 *
 * @author Jonathon Hibbard
 * @email  infolock@gmail.com
 */
class DB_Prepare {

  var $link           = NULL;
  var $prepared_key   = array();  

  function DB_Prepare() {
    register_shutdown_function(array(&$this,'_DB_Prepare'));
  }

  function _DB_Prepare() {}


  function &prepare($link, $query) {
    if(!empty($this->link)) {
      trigger_error('You cannot have more than 1 prepared statement at a time!');
      $_kill = $this->close();
      return false;
    }

    if(!is_string($query) || !is_string($link)) {
      trigger_error('$sql, $statement_name, and $prepared_field must be strings!');
    }

    $this->link = $link;

    $sql = "PREPARE $this->link FROM \"$query\"";
    $rst = @mysql_query($sql, $this->_dbh);

    if ($rst === false) {
      $this->throwError(mysql_errno(), 'Query failed: ' . $sql);
    }

    return true;
  }


  function &bind_param($set_params) {
    # Verify we have an array
    if(!is_array($set_params)) {
      trigger_error('$set_params must be an array!', E_USER_ERROR);;
      $_kill = $this->close();
      return false;
    }

    # Prepare the sql
    $sql = "SET ";

    foreach($set_params as $key => $value) {
      # do the set query first
      $sql .= "@$key = \"$value\",";
      $this->prepared_key[] = $key;

    }

    # Run sql
    $sql = substr($sql, 0, -1);
    $rst = @mysql_query($sql, $this->_dbh);

    if ($rst === false) {
      $this->throwError(mysql_errno(), 'Query failed: ' . $sql);
      return false;
    }

  }

  function &execute() {

    # Verify we have a valid key(s)
    if(empty($this->prepared_key) || !is_array($this->prepared_key)) {
      trigger_error('Prepared key cannot be a non-/empty array!  Execution FAILED!');
      $_kill = $this->close();
      return false;
    }

    # Prepare the sql statements
    $sql = "EXECUTE ". $this->link . " USING ";

    # Loop through and get our prepared execution queries ready
    foreach($set_params as $key => $value) {
      # then we do the execute
      $execute_sql .= "@$key,";
    }

    # Run sql
    $sql = substr($sql, 0, -1);
    $this->_res = @mysql_query($sql, $this->_dbh);

    if($this->_res === false) {
      $this->throwError(mysql_errno(), 'Query failed: ' . $sql);
      return false;
    }

    $row = $this->fetchAssoc();

    return $row;
  }

  function &close() {
    if(empty($this->link)) {
      trigger_error('A prepared statement does not exist!');
      return false;
    }

    $sql = "DEALLOCATE PREPARE " . $this->link;
    $rst = @mysql_query($sql, $this->_dbh);

    if($rst === false) {
      $this->throwError(mysql_errno(), 'Query failed: ' . $sql);
      return false;    
    }

    return true;
  }
}
?>
Post Reply