p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: Professional PHP 5 ISBN: 978-0-7645-7282-1 (http://p2p.wrox.com/forumdisplay.php?f=152)
-   -   MySQL conversions - chapter 7 (http://p2p.wrox.com/showthread.php?t=42894)

gorillapoop May 23rd, 2006 02:22 AM

MySQL conversions - chapter 7
 
have been working on translating the sql classes from chapter 7 into mysql. thought someone else might find it useful. and do please make better and repost. also note that all of your auto-increment or unique id column names have to be "id" for these functions to work. didn't think that was clear in the book. these edits seem to work so far:

<?php
class sql {
  private $result_rows; # Result rows hash
  private $query_handle; # db: the query handle
  private $link_ident; # db: the link identifier

  public function __construct() {
    $hostname = 'localhost';
    $username = 'asda';
    $password = 'asdsad';
    $database = 'asdsad';
    if (!$this->link_ident){
            $this->link_ident=mysql_connect($hostname, $username, $password);
    }
    mysql_select_db($database);
  }

  public function query($sql, $code_return_mode = 0) {

    $result=mysql_query($sql,$this->link_ident);
    if (!$result){
        error_log("QUERY FAILED: $sql\n");
        echo ("QUERY FAILED: $sql\n");
        return(0);
    }else{
        $num=mysql_numrows($result);
        for ($i=0; $i<$num; $i++){
            $return_array[$i] = mysql_fetch_array($result);
        }
        $this->result_rows = $return_array;
    }
    return(1);
  }

  public function get_result($row_num, $column_name) {
    return ($this->result_rows[$row_num][$column_name]);
  }

  public function get_row_hash($row_num) {
    return ($this->result_rows[$row_num]);
  }

  public function get_table_hash() {
    return $this->result_rows;
  }

  public function done($close_connection = 0) {
    if ($close_connection) {
      mysql_close ($this->link_ident);
    }
  }


};

?>

and here's the genericObject - i haven't fully tested it, but seems like it's working...

<?
class GenericObject {

  # Member Variables

  private $id;
  private $table_name;

  private $database_fields;
  private $loaded;
  private $modified_fields;

  # Methods

  public function Reload() {
    $sql = new sql(0);
    $id = $this->id;
    $table_name = $this->table_name;
    $sql->query("SELECT * FROM $table_name WHERE id='$id'");
    $result_fields = $sql->get_row_hash(0);
    $this->database_fields = $result_fields;
    $this->loaded = 1;
    if (sizeof($this->modified_fields) > 0) {
      foreach ($this->modified_fields as $key => $value) {
        $this->modified_fields[$key] = false;
      };
    };
  }

  private function Load() {
    $this->Reload();
    $this->loaded = 1;
  }

  public function ForceLoaded() {
    $this->loaded = 1;
  }

  public function GetField($field) {
    if ($this->loaded == 0) {
      $this->Load();
    };
    return $this->database_fields[$field];
  }

  public function GetAllFields() {
    if ($this->loaded == 0) {
      $this->Load();
    };
    return($this->database_fields);
  }

  public function GetID() {
    return $this->id;
  }

  public function Initialize($table_name, $tuple_id = "") {
    $this->table_name = $table_name;
    $this->id = $tuple_id;
  }

  public function SetField($field, $value) {
    if ($this->loaded == 0) {
      if ($this->id) {
        $this->Load();
      };
    };
    $this->database_fields[$field] = $value;
    $this->modified = 1;
    $this->modified_fields[$field] = true;
  }

  public function Destroy() {
    $id = $this->id;
    $table_name = $this->table_name;
    if ($id) {
      $sql = new sql(0);
      $stmt = "DELETE FROM " . $table_name . " WHERE id='" . $id . "'";
      $sql->query($stmt);
    };
  }

  public function Save() {
    $id = $this->id;
    $table_name = $this->table_name;
    $sql = new sql(0);
    if (!$id) {
      $this->loaded = 0;
    };
    if ($this->loaded == 0) {
      # assume this is a new entity
      $stmt = "INSERT INTO " . $table_name ."(";
      foreach ($this->database_fields as $key => $value) {
        if (!is_numeric($key)) {
          $key = str_replace("'", "\'", $key);
          if ($value != "") {
            $stmt .= "$key,";
          };
        };
      };
      # Chop last comma
      $stmt = substr($stmt,0,strlen($stmt)-1);
      $stmt .= ") VALUES (";
      foreach ($this->database_fields as $key => $value) {
        if (!is_numeric($key)) {
          if ($value != "") {
            $value = str_replace("'", "\'", $value);
            $stmt .= "'$value',";
          };
        };
      };
      # Chop last comma
      $stmt = substr($stmt,0,strlen($stmt)-1);
      $stmt .= ")";
    } else {
      $stmt = "UPDATE " . $table_name ." SET ";
      foreach ($this->database_fields as $key => $value) {
        if (!is_numeric($key)) {
          if ($this->modified_fields[$key] == true) {
            $value = str_replace("'", "\'", $value);
            if ($value == "") {
              $stmt .= "$key = NULL, ";
            } else {
              $stmt .= "$key = '$value', ";
            };
          };
        };
      };
      # Chop last comma and space
      $stmt = substr($stmt,0,strlen($stmt)-2);
      $stmt .= " WHERE id='$id'";
    };
    $return_code = $sql->query($stmt, 1);
    if ($this->loaded == 0) {
      # Try to get the ID of the new tuple.
      $stmt = "SELECT MAX(id) AS id FROM $table_name WHERE ";
      foreach ($this->database_fields as $key => $value) {
        if (!is_numeric($key)) {
          if ($value) {
            if ($this->modified_fields[$key] == true) {
              $value = str_replace("'", "\'", $value);
              $stmt .= "$key = '$value' AND ";
            };
          };
        };
      };
      # Chop last " AND " (superfluous)
      $stmt = substr($stmt,0,strlen($stmt)-5);
      $sql->query($stmt);
      $result_rows = $sql->get_table_hash();
      $proposed_id = $result_rows[0]["id"];
      if ($proposed_id > 0) {
        $this->loaded = 1;
        $this->id = $proposed_id;
        return true;
      } else {
        return false;
      };
    };
    return($return_code);
  }
};
?>

carlolsen November 29th, 2006 10:11 AM

As written, the tables all need an "id" column. However, you could modify this code to include the table name, an underscore, and "id" appended to the end and get the same result. The only reason the first column name needs to be "id" is because all the SQL statements are hard-coded that way.

Carl Olsen
www.carl-olsen.com

jonabeer February 15th, 2011 11:02 AM

My version of the GenericObject class. I use other naming conventions for my classes. I Also use Hungarian Naming Convention as strict as possible.
My version of the GenericObject class makes use of the Database class described in chapter 8 of this book (but with an upgrade from DB to MDB2). By doing so the sql class becomes completely useless because my Database class is configured to return array's instead of MDB2 result objects by Database::getRow, Database::getCol and Database::getAll (Database::getOne returns a single value)
PHP Code:

<?php
/**
 *  class.GenericObject.php
 *
 *  Date created : 12-dec-2010
 *  Hystory
 *  - 14-dec-2010 : Converted to HNC
 *  - 09-jan-2011 : Documented
 *  - 10-feb-2011 : Made compatible with Database class
 *
 *  @package ProPHP5_07
 *  @copyright (c) 2011
 *  @version 1.0
 */

require_once('class.Database.php');

/**
 * GenericObject
 *
 * This class is an abstract
 * superclass. A child of this
 * class will represent a single
 * tuple of a single table in the
 * database, where that table
 * describes a single entity, and
 * therfore manage that row in an
 * OO manner. Child classes
 * should always be named
 * Generic<i>Child</i> in order to work
 * with GenericObjectCollection.
 *
 * @package ProPHP5_07
 * @copyright (c) 2011
 * @version 1.0
 * @access public
 */
class GenericObject {

    
/**
     * The identifier for the row in question.
     *
     * @access private
     * @var integer
     */
    
private $m_iId;

    
/**
     * Object DB-handle.
     *
     * @access private
     * @var string
     */
    
private $m_objDB;

    
/**
     * The name of the table in
     * question.
     *
     * @access private
     * @var string
     */
    
private $m_sTableName;

    
/**
     * An associative array with keys
     * representing the names of the
     * columns of the table, and
     * their values being the value
     * of that column for the given
     * row.
     *
     * @access private
     * @var array
     */
    
private $m_aDbFields;

    
/**
     * Indicates whether this object
     * has been populated with data
     * from the database. It is not
     * necessary to load data unless
     * it will be retrieved. This is
     * set to 0 if no data is loaded,
     * 1 if data is loaded.
     *
     * @access private
     * @var bool
     */
    
private $m_bLoaded;

    
/**
     * A hash identical in key-values
     * to m_aDbFields, but with
     * values true or false to
     * represent whether that
     * particular database field has
     * been modified since the
     * contents from the database was
     * last loaded.
     *
     * @access private
     * @var array
     */
    
private $m_aModifiedFields;

    
/**
     * Indicates whether any
     * particular database field has
     * been modified since the
     * contents from the database was
     * last loaded. This is set to 0
     * if no data has been modified,

     * 1 if data has been modified.
     *
     * @access private
     * @var bool
     */
    
private $m_bModified;

    
/**
     * GenericObject::Initialize()
     *
     * Called by the subclass to set
     * the table name and id of the
     * row in question.
     *
     * @param string $p_sTableName
     * @param intger $p_iTupleId
     * @return void
     */
    
public function Initialize($p_sTableName$p_iTupleId "") {
        
$this->m_sTableName $p_sTableName;
        
$this->m_iId $p_iTupleId;
        
$this->m_objDB Database::instance();
    }

    
/**
     * GenericObject::Load()
     *
     * An alias for
     * GenericObject::Reload().
     *
     * @return void
     */
    
private function Load() {
        
$this->Reload();
        
$this->m_bLoaded 1;
    }

    
/**
     * GenericObject::Reload()
     *
     * Populates m_aDbFields array with
     * current values from the database.
     *
     * @return void
     */
    
public function Reload() {
        
$iId $this->m_iId;
        
$sTableName $this->m_sTableName;
        
$aResultFields $this->m_objDB->getRow("SELECT * FROM " $sTableName " WHERE id='" $iId "'");
        
$this->m_aDbFields $aResultFields;
        
$this->m_bLoaded 1;
        if(
sizeof($this->m_aModifiedFields) > 0) {
            foreach(
$this->m_aModifiedFields as $key => $value) {
                
$this->m_aModifiedFields[$key] = false;
            }
        }
    }

    
/**
     * GenericObject::ForceLoaded()
     *
     * Makes this instance of the
     * subclass think that is is
     * loaded even if it isn't.
     * Useful if values have been set
     * manually by some third-party
     * process because this
     * precludes any automatic
     * loading taking place when
     * GetField is called.
     *
     * @return void
     */
    
public function ForceLoaded() {
        
$this->m_bLoaded 1;
    }

    
/**
     * GenericObject::GetID()
     *
     * Gets the current ID of the
     * loaded row. This will either
     * have been set upon
     * instantiation or will have
     * been determined when Save()
     * was called if originally a
     * new entity.
     *
     * @return integer
     */
    
public function GetID() {
        return 
$this->m_iId;
    }

    
/**
     * GenericObject::GetField()
     *
     * Gets the value of the field in
     * question. If not yet loaded,
     * it will automatically call
     * GenericObject::Reload first to
     * load values from the database.
     *
     * @param string $p_sField
     * @return string
     */
    
public function GetField($p_sField) {
        if(
$this->m_bLoaded == 0) {
            
$this->Load();
        }
        return 
$this->m_aDbFields[$p_sField];
    }

    
/**
     * GenericObject::GetAllFields()
     *
     * Same as
     * GenericObject::GetFields, but
     * returns a hash of all fields
     * and values rather than just a
     * single field. Again,
     * automatically calls
     * GenericObject::Reload if so
     * required.
     *
     * @return array
     */
    
public function GetAllFields() {
        if(
$this->m_bLoaded == 0) {
            
$this->Load();
        }
        return 
$this->m_aDbFields;

    }

    
/**
     * GenericObject::SetField()
     *
     * Updates the internal hash
     * (m_aDbFields) to reflect new
     * value of given field. It then
     * sets m_bModified to 1 and the
     * appropriate m_aModifiedFields
     * key to 1.
     *
     * @param string $p_sField
     * @param string $p_sValue

     * @return void
     */
    
public function SetField($p_sField$p_sValue) {
        if(
$this->m_bLoaded == 0) {
            if(
$this->m_iId) {
                
$this->Load();
            }
        }
        
$this->m_aDbFields[$p_sField] = $p_sValue;
        
$this->m_bModified 1;
        
$this->m_aModifiedFields[$p_sField] = true;
    }

    
/**
     * GenericObject::Destroy()
     *
     * Permanently deletes the entry
     * from the database. The object
     * should not be used after this
     * method has been called.
     *
     * @return void
     */
    
public function Destroy() {
        
$iId $this->m_iId;
        
$sTableName $this->m_sTableName;
        if(
$iId) {
            
$objSql = new $objSql(0);
            
$sDeleteQry "DELETE FROM \"" . $ $sTableName "\" WHERE id='" $iId "'";
            
$objSql->query($sDeleteQry);
        }
    }

    
/**
     * GenericObject::Save()
     *
     * Saves the content of the
     * object to the database.
     *
     * @return string|bool
     */
    
public function Save() {
        
$iId $this->m_iId;
        
$sTableName $this->m_sTableName;

        if(!
$iId) {
            
$this->m_bLoaded 0;
        }

        if(
$this->m_bLoaded == 0) {
            
# assume this is a new entity
            
$aValues = array();

            foreach(
$this->m_aDbFields as $key => $value) {
                if(!
is_numeric($key)) {
                    
$key str_replace("'""\'"$key);
                    
$value str_replace("'""\'"$value);
                    if(
$value != "") {
                        
$aValues[$key] = $value;
                    }
                }
            }

            
$sReturnCode $this->m_objDB->insert($sTableName$aValues);
        } else {
            
# assume this is an existing entity
            
foreach ($this->m_aDbFields as $key => $value) {
                if(!
is_numeric($key)) {
                    if(
key_exists($key$this->m_aModifiedFields)) {
                        if(
$this->m_aModifiedFields[$key] == true) {
                            if(
$value != "") {
                                
$value str_replace("'""\'"$value);
                                
$aValues[$key] = $value;
                            }
                        }
                    }
                }
            }
            
$sWhere "id='" $iId "'";
            
$sReturnCode $this->m_objDB->update($sTableName$aValues$sWhere);
        }

        if(
$this->m_bLoaded == 0) {
            
# Try to get the ID of the new tuple.
            
$sSelectQry "SELECT MAX(id) AS id FROM " $sTableName " WHERE ";

            foreach (
$this->m_aDbFields as $key => $value) {
                if(!
is_numeric($key)) {
                    if(
$value) {
                        if(
$this->m_aModifiedFields[$key] == true) {
                            
$value str_replace("'""\'"$value);
                            
$sSelectQry .= $key " = '" $value "' AND ";
                        }
                    }
                }
            }

            
# chop last " AND "
            
$sSelectQry substr($sSelectQry0strlen($sSelectQry)-5);
            
$aResultRows $this->m_objDB->getRow($sSelectQry);
            
$iProposedId $aResultRows["id"];

            if(
$iProposedId 0) {
                
$this->m_bLoaded 1;
                
$this->m_iId $iProposedId;
                return 
true;
            } else {
                return 
false;
            }
        }
        return(
$sReturnCode);
    }
}
?>

I am also figuring out how I can rewrite the code for GenericObjectCollection. I would like it to work with Collection class (chapter 5-6) so i can iterate over the result 'and do fancy things' with it because 'i feel strongly about it' ;). However to make fully use of the Collection class it should also make use of lazy instantiation as it is not always needed to load the actual data from the database besides the corresponding id's. (And thus, save on an extra query that is unleashed on the database)
I'll post the code later when i have finished it, in the meantime, tips and hints are welcome :)

grts,
Jona


All times are GMT -4. The time now is 03:55 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.