SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict rabbitdog's Avatar
    Join Date
    Jul 2001
    Location
    So. Tenn.
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Creating queries on the fly using OOP?

    I'm searching for any information anyone might have on techniques they've utilized to generate insert and update queries on the fly.

    I find myself in the situation of constantly rewriting queries for various admin functions for sites. Typing out insert and update queries again and again is beginning to give me migraines.

    I've played around with the idea of passing a list of fields which should be updated (with the assumption that the variable names within the $_POST array match those specified, which match the field names in the table). That leaves some issues about inserting blank (empty values).

    Has anyone been down this route before and developed something that works? Can you offer any insight into methods of handling this type of function, or make any suggestions of a better method to go about writing it?

    TYIA.
    Mr Vector
    High quality, royalty free, vector graphics
    for t-shirt artists and graphic/web designers.

  2. #2
    SitePoint Zealot
    Join Date
    Oct 2002
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i do have something that works, I prettymuch set up a map of my fields in my data models.. like so

    PHP Code:
    <?php
    require_once(CLASS_ROOT."Dom.php");
    class 
    PublicationDom extends Dom
    {
            
     var 
    $key_publication;
     var 
    $key_publicationGroup;
     var 
    $key_author;
     var 
    $subject;
     var 
    $title;
     var 
    $leadin;
     var 
    $keywords;
     var 
    $status;
     var 
    $format;
     var 
    $rating;
     var 
    $views;
     var 
    $price;
     var 
    $publish;
     var 
    $imgFilename;
     var 
    $imgWidth;
     var 
    $imgHeight;
     var 
    $sourceTitle;
     var 
    $sourceURL;
     var 
    $timestamp;
     
     function 
    PublicationDom() 
     {
      
    parent::Dom();
      
    $this->setTable('publications');
      
    $this->setIdField('key_publication');
      
      
    $this->selectFields = array(
       
    'key_publication',
       
    'key_publicationGroup'
       
    'key_author'
       
    'subject'
       
    'title'
       
    'leadin'
       
    'keywords'
       
    'status',
       
    'format',
       
    'rating',
       
    'views',
       
    'price',
       
    'publish',
       
    'imgFilename',
       
    'imgWidth',
       
    'imgHeight',
       
    'sourceTitle',
       
    'sourceURL',
       
    'timestamp');
      
    $this->addForeignKey('key_publicationGroup''publication_groups''key_publicationGroup');
      
    $this->addForeignLookupField('publication_groups''name''groupName');
      
    $this->addForeignLookupField('publication_groups''publicationClass''publicationClass');
      
    $this->addForeignKey('key_author''users''id');
      
    $this->addForeignLookupField('users''username''authorName');

      
    $this->insertFields = array(
       
    'key_publicationGroup'
       
    'key_author'
       
    'subject'
       
    'title'
       
    'leadin'
       
    'keywords'
       
    'status',
       
    'format',
       
    'rating',
       
    'views',
       
    'price',
       
    'publish',
       
    'imgFilename',
       
    'imgWidth',
       
    'imgHeight',
       
    'sourceTitle',
       
    'sourceURL',
       
    'timestamp');
      
    $this->updateFields = array(
       
    'subject'
       
    'title'
       
    'leadin'
       
    'keywords'
       
    'status',
       
    'format',
       
    'rating',
       
    'views',
       
    'price',
       
    'publish',
       
    'imgFilename',
       
    'imgWidth',
       
    'imgHeight',
       
    'sourceTitle',
       
    'sourceURL',
       
    'timestamp');
      
    $this->formFile 'publication.frm';
     }
    }
    (I've removed my get/set methods to shorten this post)

    i pass the Model to a QueryGenerator class to generate my sql statements.

    $selectFields are the base fields used in all select queries, $insertFields are the fields to use when inserting into db, and $update.. well you get the idea.

    You can see I even accounted for foreignKeys and foreignLookupFields.

    I have a standard way of generating my forms also so that when submitting a form, I can just pass the form object to the QueryIterator along with my Data Model and it will do all the work for me.

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2002
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here is my DomQuery class
    PHP Code:
    <?php
    require_once(CLASS_ROOT.'QueryBuilder.php');
    /**
     * Class <code>DomQuery</code> builds SQL statements from a <code>Dom</code> object.
     **/
    class DomQuery extends QueryBuilder 
    {
     
    // DATA MEMBERS
     
    var $dom;
     
     
     
    // CREATORS

        /***
         * Construct a new <code>DomQuery</code>
         * @param $dom is the <code>Dom</code> to use for this <code>DomQuery</code>
         * @param $queryType is the type of SQL query that will be generated 
      * (select, insert, update, delete)
         ***/
     
    function &DomQuery($queryType$dom
     {
      
    $this->dom $dom;
      
    $this->setTable($this->dom->table);
      
    $field $this->dom->getIdField();
      
    $this->setIdField($field);
      switch (
    strtolower($queryType))
      {
       case 
    'select':
        
    $this->registerFields($this->dom->getSelectFields());
        
    $this->registerForeignKeys($this->dom->getForeignKeys());
        
    $this->registerForeignLookupFields($this->dom->getForeignLookupFields());
        break;
       case 
    'insert':
        
    $this->registerFields($this->dom->getInsertFields());
        break;
       case 
    'update':
        
    $this->registerFields($this->dom->getUpdateFields());
        break;
       case 
    'delete':
        break;
      }
     }

     
    // MANIPULATORS
     
     /**
      * Add fields to be included in sql statement from Array
      * $param fields is the array of fields to add
      * @returns void
      **/ 
     
    function registerFields($fields
     {
      foreach (
    $fields as $field
      {
       
    parent::addField($field$this->dom->getValue($field));
      }
     }
     
    /**
      * Adds foriegn keys from Array
      * $param keys is the array of foreign keys to add
      * @returns void
      **/
     
    function registerForeignKeys($keys)
     {
      foreach(
    $keys as $foreignTable => $fieldArray)
      {
       
    $this->addForeignKey($fieldArray[0], $foreignTable$fieldArray[1]);
      }
     }

     
    /**
         * Adds foriegn lookup fields from Array
      * $param fields is the array of fields to add
      * @returns void
      **/  
     
    function registerForeignLookupFields($fields)
     {
      foreach(
    $fields as $asName => $fieldArray)
      {
       
    //echo $asName.' '.$field.'<br />';
       
    $this->addForeignLookupField($fieldArray[0], $fieldArray[1], $asName);
      }
     }
     
    }
    ?>
    PHP Code:
    <?php
    /***
     * Class <code>QueryBuilder</code> generates SQL queries from 
     * table descriptions
     ***/
    class QueryBuilder {
     
    // DATA MEMBERS
     /***
     * The table's name
     * @type string
     ***/
     
    var $table;
     
    /***
     * The tables primary key
     * @type string
     ***/
     
    var $idField;
     
    /***
     * The fields we will be using in the query
     * @type string
     ***/
     
    var $fields = array();
     
    /***
     * The foreign keys we will be using for table joins
     * @type string
     ***/
     
    var $foreignKeys = array();
     
    /***
     * The foreign lookup fields we will be using for table joins
     * @type string
     ***/
     
    var $foreignLookupFields = array();
     
    // ACCESSORS 
     
    function &getTable() 
     {
      return 
    $this->table;
     }
     
     function &
    getIdField() 
     {
      return 
    $this->idField;
     }
     
    /***
     * Returns the value of a field in <code>$fields</code>
     * @returns string
     ***/
     
    function &getFieldValue($field
     {
      return 
    $this->fields[$field];
     }
     
    /***
     * Returns <code>fields</code>
     * @returns array
     ***/
     
    function &getFields() 
     {
      return 
    $this->fields;
     } 
     
    /***
     * Returns comma separated field list
     * @private
     * @returns string
     ***/
     
    function &getFieldsString()
     {
      
    $fieldstr '';
      
    $delimiter '';
      
    $i 0;
      foreach (
    $this->fields as $field => $value) {
       
    $field trim($field);
       
    $delimiter = ($i count($this->fields) - 1) ? ', ' '';
       
    $fieldstr .= $this->table.'.'.$field.$delimiter;
       
    $i++;
      }
      return 
    $fieldstr;
     }
     
    /***
     * Returns comma separated values
     * @private
     * @returns string
     ***/
     
    function &getValuesString()
     {
      
    $valstr '';
      
    $i 0;
      foreach (
    $this->fields as $field => $value) {
       
    // get the field value from the dataobject 
       
    $value trim($value);
       
    // set the delimiter
       
    $delimiter = ($i count($this->fields) - 1) ? ', ' '';
       
    // add quotes to value if needed
       
    if (!is_numeric($value)) {
        
    $valstr .= "'".$this->formatText($value)."'".$delimiter;
       } else {
        
    $valstr .= $value.$delimiter;
       }
       
    $i++;
      }
      return 
    $valstr;
     }

     
    /***
     * Returns comma separated values
     * @private
     * @returns string
     ***/
     
    function &getTableJoinString() 
     {
      
    $joinStr '';
      foreach (
    $this->foreignKeys as $relationship) {
       
    $row split(','$relationship);
       
    $foreignTable trim($row[0]);
       
    $nativeKey trim($row[1]);
       
    $foreignKey trim($row[2]);
       
    $joinStr .= "LEFT JOIN $foreignTable ON $this->table.$nativeKey = $foreignTable.$foreignKey ";
      }
      return 
    $joinStr;
     }
     
    /***
     * Returns comma separated values
     * @private
     * @returns string
     ***/
     
    function &getForeignLookupString()
     {
      
    $lookupStr '';
      
    $i 0;
      
    $cnt count($this->foreignLookupFields);
      foreach (
    $this->foreignLookupFields as $foreignLookups
      {
       
    $i++;
       
    $delimiter = ($i $cnt) ? ', ' '';
       
    $row split(','$foreignLookups);
       
    $foreignTable trim($row[0]);
       
    $foreignField trim($row[1]);
       
    $asName trim($row[2]);
       
    $lookupStr .= "$foreignTable.$foreignField as $asName$delimiter ";
      }
      return 
    $lookupStr;
     }
     
    /***
     * Returns INSERT sql statement
     * @returns string
     ***/
     
    function &getInsertStatement() 
     {
      
    // get the fields list needed for this query string.
      
    return "INSERT INTO $this->table(".$this->getFieldsString().") VALUES(".$this->getValuesString().")";
     }
     
     
    /***
     * Returns UPDATE sql statement
     * @param id is the unique id of the record to update
     * @returns string
     ***/
     
    function &getUpdateStatement($id
     {
     
      
    $id = (is_numeric($id)) ? $id "'$id'";
      
      
    $sql "UPDATE $this->table SET ";
      
    $i 0;
      
    $cnt count($this->fields);
      foreach(
    $this->fields as $field => $value) {
       
    // get the field value from the data object
       
    $value trim($value);
       
    // set the delimiter
       
    $delimiter = ($i $cnt -1) ? ', ' '';
      
       
    // eclose in quotes if needed
       
    $value = (is_numeric($value)) ? $value "'".$this->formatText($value)."'";
       
       
    $sql .= "$field = $value$delimiter";
       
    $i++;
      }
      
    $sql .= " WHERE $this->idField = $id";
      return 
    $sql;
     }
     
     
    /***
     * Returns DELETE sql statement
     * @param id is the unique id of the record to update
     * @returns string
     ***/
     
    function &getDeleteStatement($id
     {
      
    $id = (is_numeric($id)) ? $id "'$id'";
      return 
    "DELETE FROM $this->table WHERE $this->idField = $id";
     }
     
     
     
    /***
     * Returns UPDATE sql statement
     * @param id is the unique id of the record to update
     * @returns string
     ***/
     
    function &getSelectByIdStatement($id$whereStr NULL$orderbyStr NULL$groubyStr NULL)
     {
      
    $id = (is_numeric($id)) ? $id "'$id'";
      return 
    "SELECT ".$this->getFieldsString().", ".$this->getForeignLookupString()." FROM $this->table ".$this->getTableJoinString()." WHERE $this->idField = $id";
     }
     
    /***
     * Returns SELECT sql statement
     * @param whereStr is the WHERE clause of the SQL statement that should be used (default is '')
     * @param orderbyStr is the ORDER BY clause of the SQL statement that should be used (default is '')
     * @returns string
     ***/
     
    function &getSelectStatement($whereStr NULL$orderbyStr NULL$groubyStr NULL)
     {
      
    $orderbyStr = ($orderbyStr) ? $orderbyStr " " ORDER BY $this->idField DESC ";
      
    $sql "SELECT ".$this->getFieldsString().", ".$this->getForeignLookupString()." FROM $this->table ".$this->getTableJoinString()." ".$whereStr." ".$groubyStr." ".$orderbyStr." ";
      echo 
    $sql;
      return 
    $sql;
     }
     
     
    // MANIPULATORS
     
    function setTable($table
     {
      
    $this->table $table;
     }
     function 
    setIdField($field
     {
      
    $this->idField $field;
     }
     
     function 
    addField($field$value ''
     {
      
    $this->fields[$field] = $value;
     }
     
     function 
    deleteField($field
     {
      unset(
    $this->fields[$field]);
     }
     
     
     function 
    resetFields() 
     {
      unset(
    $this->fields);
     }
     
     function 
    fieldExists($field
     {
      return isset(
    $this->fields[$field]); 
     }

     function 
    formatText($str
     {
      
    $str str_replace("\n""<br />"$str);
      
    $str addslashes($str);
      return 
    $str;
     }
     
     function 
    addForeignKey($foreignTable$nativeField$foreignField)
     {
      
    $this->foreignKeys[] = "$nativeField$foreignTable$foreignField";
     }
     function 
    addForeignLookupField($foreignTable$foreignField$asName)
     {
      
    $this->foreignLookupFields[] = "$foreignTable$foreignField$asName";
     }
     
    }
    ?>
    I still need to build in some methods to validate my sql queries to avoid any potentially harmless user input.

  4. #4
    SitePoint Addict rabbitdog's Avatar
    Join Date
    Jul 2001
    Location
    So. Tenn.
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the code snippet guys! That was more than I expected.

    After a frustrating search for something along the lines of what I wanted - I found most of the stuff to be overkill.

    I would up writing my own class instead. Though its not done yet, I've got the bulk of it completed so I thought I'd toss it up here for anyone who was interested.

    PHP Code:
    <?php
    $database 
    'blah';
    mysql_connect('localhost','blah','blah');
    mysql_select_db('$database');

    class 
    quickQuery {
     var 
    $table;
     var 
    $idField;
     var 
    $database;
     var 
    $numColumns;
     var 
    $fieldList;
     
     
    //constructor, sets working database.
     
    function quickQuery($database) {
      
    $this->database=$database;
     }
     
    //sets the primary key field name for the insert statement. Must be called before update()
     //is there a way to make that more concise?
     
    function setId($id) {
      
    $this->idField=$id;
     }
     
    //gets a list of all the fields in a specified table. Internal class usage only.
     
    function getFields() {
      
    $tablefields mysql_list_fields($this->database$this->table);
      
    $this->numColumns mysql_num_fields($tablefields);
      for (
    $i=0;$i<$this->numColumns;$i++) {
       
    $this->fieldList[]=mysql_field_name($tablefields$i);
      }
     }
     
    //generates insert query based on array of fields passed in proper order. 
     //suggestion from another php user: thinks I should do this like the update query, and use an associative array.
     //I'm still thinking about that one.
     
    function insert($table,$fieldValues=Array()) {
      
      
    $this->table=$table;
      
    $this->getFields();
      
    $fields="'";
      foreach (
    $this->fieldList as $field) {
       
    $fields.="$field','";
      }
      
    $fields trim($fields,",'");
      
    $values="'";
      foreach(
    $fieldValues as $value) {
       
    $values.="$value','";
      }
      
    $numValues=count($fieldValues);
      if (
    $numValues!=$this->numColumns) {
       for(
    $numValues;$numValues<$this->numColumns;$numValues++) {
        
    $values.="','";
       }
      }
      
    $values substr($values,0,strlen($values)-3);
      
    $queryString="insert into $this->table ($fields) VALUES($values)";
      return 
    $queryString;
     }
     
    //generates update query based on array of values passed.
     
    function update($table,$id,$fieldValues=Array()) {
      
    $this->table=$table;
      
      
    $updateFields="";
      foreach(
    $fieldValues as $var=>$value) {
       
    $updateFields.="$var='$value',";
      }
      
    $updateFields=trim($updateFields,',');
      
    $queryString="UPDATE $this->table SET $updateFields WHERE $this->idField=$id";
      
      return 
    $queryString;
     }
     
    //generates update query automatically based on values present in the $_POST array
     
    function updatefromPost() {
      
    $this->table=$table;
      
      
    $this->getFields();
      
    $updateFields="";
      foreach(
    $this->fieldList as $field) {
       
    $updateFields.="$field='$_POST[$field]',";
      }
      
    $updateFields=trim($updateFields,',');
      
    $queryString="UPDATE $this->table SET $updateFields WHERE $this->idField=$id";
      
      return 
    $queryString;
     }

    }
    $quickQuery = new quickQuery($database);
    //test of the update statement
    $quickQuery->setId('id');
    $update=array('test'=>'eeks''test2'=>'eeks');
    print 
    $quickQuery->update('news_articles',15,$update);
    Mr Vector
    High quality, royalty free, vector graphics
    for t-shirt artists and graphic/web designers.

  5. #5
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    I reckon you've got the right idea Rabbit Simple and it works. I haven' the need for something like that yet but it's probably what I'd do, pass some arrays to a function that would loop through the arrays and append the fields and values to the end of a query.

  6. #6
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can see if I can dig out the class I created for simple admin functions, you pass it a DB connection and a table name and it'll generate the correct HTML form to capture new data or edit an existing row in the table, and handle the insertion/update correctly, it even sets limits on the maxlength of fields etc. automatically.
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.

  7. #7
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Karl
    I can see if I can dig out the class I created for simple admin functions, you pass it a DB connection and a table name and it'll generate the correct HTML form to capture new data or edit an existing row in the table, and handle the insertion/update correctly, it even sets limits on the maxlength of fields etc. automatically.
    Sounds handy! I wrote up something like that a while ago but it was the other way around, you create the form and it does all the work at the other end

  8. #8
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is just a quick example:

    http://scripts.karlaustin.com/adminForms/index.php

    (If you test it, whatever you enter won' be given to anyone else or used for any purpose, so no need to worry)

    That is setup for the following table:
    Code:
    CREATE TABLE adminForm (
      id mediumint(9) NOT NULL auto_increment,
      username varchar(16) NOT NULL default '',
      text text NOT NULL,
      choice enum('Choice 1','Choice 2','Choice 3') NOT NULL default 'Choice 1',
      string varchar(10) NOT NULL default '',
      PRIMARY KEY  (id)
    ) TYPE=MyISAM;
    I shall post the code after the weekend, as I'm away in a bit for the weekend.
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.

  9. #9
    public static void brain Gybbyl's Avatar
    Join Date
    Jun 2002
    Location
    Montana, USA
    Posts
    647
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This may sound kind of newbieish, but hey, only one way to learn:

    Why are you guys using 'DOM' so much... Are you actually inheriting from an xml parser class, or is that just the popular thing to call your superclass?

    Or is it that you're running your queries based on the output/structure of an xml file?
    Ryan

  10. #10
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Gybbyl
    This may sound kind of newbieish, but hey, only one way to learn:

    Why are you guys using 'DOM' so much... Are you actually inheriting from an xml parser class, or is that just the popular thing to call your superclass?

    Or is it that you're running your queries based on the output/structure of an xml file?
    I was wondering too but I decided that I didn't need to know that much

  11. #11
    SitePoint Zealot
    Join Date
    Oct 2002
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Gybbyl
    This may sound kind of newbieish, but hey, only one way to learn:

    Why are you guys using 'DOM' so much... Are you actually inheriting from an xml parser class, or is that just the popular thing to call your superclass?

    Or is it that you're running your queries based on the output/structure of an xml file?

    Well, i dunno about anyone else.. but the "Dom" in the code pasted refers to a DataObjectModel. It has nothing to do with XML. I only chose this name because I am following a MVC (Model View Controller) pattern in my OOP.

    So, as you may guess, I also have a DataObjectView (Dov) and DataObjectController (Doc).

  12. #12
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just to dig this back up, I have put the source code for my little app up on the web now, it's released under the BSD license and can be found here:

    http://scripts.karlaustin.com/adminForms/

    If you use it, i'd like to know, same goes for any suggestions etc.

    Thanks,
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •