Breaking sql-queries into usefull bits

[Michiel]

Hi,

I'm currently working on a simple and easy text-based database class. I've got allmost all the functions and now I'm ready for improvements. One thing that's on the top of my list, is to make the script compatible with typical sql queries.

To do this I need a function that converts an sql query, determines what function to use (select(), insert(), update(), etc.) and supplies the correct paramaters.

My actual question is how I can break up an sql-query in bits, that I can feed to my various functions? An example: Currently I use the folowing to select the name and age of members that are older than 18: '$object->Select('members', 'name, age', 'age < 18')'. What I actualy would like to do is use the folowing query:
'SELECT name, age FROM members WHERE age < 18'.

Can anyone think of an idea how to handle such a thing? I'm not asking you for a complete script, but instead I'd like you to ask to supply a beginning and/ or some usefull tips.

Thanx in advance, Michiel

PS If more information is needed, please let me know and I would be happy to supply it to you!

[firepages]

Hi , there are 2 common approaches to where you are going ,
the most often used is to simply pass the query string ..

$db->q( "SELECT * FROM blah LIMIT 11,50" ) ;

but this is not X-db e.g. you can't do a LIMIT 10,50 in some DB's.
The second is a variation on the theme you are already persuing.

The problem with your approach (and I have been this route myself) is that you can end up with some incredibly complex arrays of data to pass to your query ... e.g. what if you needed to call..say

<?
SELECT $table1.*,$table2.*,
DATE_FORMAT($table3.date_in, $format ) AS t3_date_in 
FROM $table1 , 
LEFT JOIN $table2 ON $table1.id = $table1.t1_id 
WHERE $table2.blah = $x 
GROUP BY .. 
ORDER BY .... 
LIMIT ... 
etc etc  
?>

of course I made that up , but I often end up with fugly SQL just like that ..

you can imagine the number of arrays of data you would need to pass to your db routine to put that all together, though of course you can then format the SQL query to suit the database of choice which is the problem with the first method .

Not being a big fan of database abstraction I cant say I have solved either , if you only expect to be using one database I would advise to simply pass the SQL complete , even if you later add a new DB to your class you can parse the passed SQL for known inconsistancies/issues and reformat for your database of choice, it will still probably be faster than building your queries from data in function calls.

[Celtboy]

the other major question of course is this: how complex will these queries be? if you plan on only using simple queries like you've posted, then a simple "substring" function would work nicely.

[Michiel]

Thanks for your replies. I've been working on some functions and came up with the following:

function Query($query) {
  $this->_sQuery = $query;
  $type = $this->_GetQueryType();
  switch($type) {
    case 'SELECT':
      $this->_ParseSelectQuery();
     break;
     case 'INSERT':
       $this->_ParseInsertQuery();
     break;
     case 'UPDATE':
       $this->_ParseUpdateQuery();
     break;
  }
}

function _GetQueryType() {
  $query = $this->_sQuery;
  $query = trim($query);
  $elements = explode(' ', $query);
  $type = $elements[0];
  unset($elements[0]);
  $this->_sQuery = implode(' ', $elements);
  return strtoupper($type);
}

function _ParseSelectQuery() {
  $query = $this->_sQuery;
  // Get Columns
  $from_pos = strpos($query, 'FROM');
  $columns = substr($query, 0, $from_pos);
  $columns = str_replace(' ', '', $columns);
  $columns = explode(',', $columns);
  // Get Table
  $start_pos = $from_pos + 4;
  $where_pos = strpos($query, 'WHERE');
  $end_pos = $where_pos - $start_pos;
  $table = substr($query, $start_pos, $end_pos);
  $table = trim($table);
  // Get condition;
  $start_pos = $where_pos + 5;
  $conditions = substr($query, $start_pos);
  $conditions = explode('AND', $conditions);
  // Call actual select-function
  $this->Select($table, $columns, $conditions);
} 

What do you think of it? Any comments/ improvements etc. are always welcome.

Cheers, Michiel\

[firepages]

To be honest I think you are creating more problems than you are solving , you will never get more than simple queries working this way which you would be as well to simply pass to your database class.
So though it may be an interesting project , I don't see any advantage that all this work will produce.