Creating queries on the fly using OOP?

[rabbitdog]

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.

[shoebox]

i do have something that works, I prettymuch set up a map of my fields in my data models.. like so

<?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.

[shoebox]

here is my DomQuery class

<?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
/***
 * 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.

[rabbitdog]

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
$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);

[Cam]

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.

[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.

[Cam]

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

[Karl]

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:

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.

[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?

[Cam]

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

[shoebox]

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).

[Karl]

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,