Free InsertUpdate Class

From: Jed Hunsaker (spam_at_lavadrop.com)
Date: 07/31/04

  • Next message: Sebastian Lauwers: "Re: Free InsertUpdate Class" 
    Date: Fri, 30 Jul 2004 16:59:37 -0700

    Anyone think hand-coding MySQL queries is a waste of time? Me too! I just
    created this InsertUpdateMySQL class for PHP... free! It only handles, as
    the name implies, INSERT or UPDATE queries. So far, very useful. It makes
    clean code... believe me.

    Tell me what you think, or if there are any improvements I should make, or
    bugs found.

    No documentation, so lemme' give a nutshell of it all. Here's some example
    code:

         <?
         $cn = new mysqli("localhost", "username", "passcode", "database"); //
    new connection object
         $prs = new InsertUpdateMySQL($cn, "products"); // Instantiates object
    to edit products table
         $prs->add("item", "?256"); // adds entry into table with item as
    field, ?256 as value
         $prs->add("color", "red"); // includes the color red in the color
    field
         $prs->add("color", "blue"); // overwrites red with blue
         $prs->remove("color"); // removes color from the query completely
         $prs->add("color", NULL); // puts color back into the query, but
    assigns NULL to it
         $prs->add("dateAdded", "NOW()", true); // true bit allows MySQL
    built-in functions
         $prs->set_conditions("`guid` = '5543564'"); // INSERT query now
    becomes UPDATE query
         $prs->execute(true); // true bit is debug mode, only displays query
    instead of executing it
         $prs->execute(); // actually executes the query
         unset($prs); // clean-up, frees up memory again
         ?>

    And here's the Class...

    <?

    class InsertUpdateMySQL {

     function __construct($cn, $table, $conditions = "") {
      $this->cn = $cn; // mysqli connection object
      $this->table = $table; // name of table to INSERT or UPDATE
      $this->keys = array(); // array of keys to INSERT or UPDATE
      $this->values = array(); // array of values to INSERT or UPDATE
      $this->conditions = $conditions; // optional condition string for WHERE
    clause
      $this->query = ""; // MySQL query string
     }

     public function set_table($table) {
      $this->table = $table;
     }

     public function set_conditions($str) {
      $this->conditions = $str;
     }

     public function add($key, $value, $noString = false) {
      if (!$noString) $value = ($value == NULL) ? "NULL" : "'" .
    $this->cn->real_escape_string($value) . "'";
      $keyPos = in_array($key, $this->keys);
      if ($keyPos) {
       array_splice($this->keys, $keyPos, 1, $key);
       array_splice($this->values, $keyPos, 1, $value);
      } else {
       array_push($this->keys, $key);
       array_push($this->values, $value);
      }
     }

     public function remove($removeKey) {
      foreach ($this->keys as $key => $value) {
       if ($value == $removeKey) unset($this->keys[$key], $this->values[$key]);
      }
     }

     public function execute($debug = false) {
      if (count($this->keys)) {
       $firstValue = true;
       if (!empty($this->conditions)) { // UPDATE record
        $this->query = "UPDATE `$this->table` SET ";
        foreach (array_combine($this->keys, $this->values) as $key => $value) {
         if (!$firstValue) $this->query .= ", ";
         $this->query .= "`$key` = $value";
         $firstValue = false;
        }
        $this->query .= " WHERE $this->conditions";
       } else { // INSERT record
        $this->query = "INSERT INTO `$this->table` (";
        foreach ($this->keys as $key) {
         if (!$firstValue) $this->query .= ", ";
         $this->query .= "`$key`";
         $firstValue = false;
        }
        $this->query .= ") VALUES (";
        $firstValue = true;
        foreach ($this->values as $value) {
         if (!$firstValue) $this->query .= ", ";
         $this->query .= "$value";
         $firstValue = false;
        }
        $this->query .= ")";
       }
       if ($debug) {
        die($this->query);
       } else {
        return $this->cn->query($this->query);
       }
      }
     }

    }

    ?>

  • Next message: Sebastian Lauwers: "Re: Free InsertUpdate Class"