Web Analytics Made Easy -
StatCounter Breaking sql-queries into usefull bits - CodingForum

Announcement

Collapse
No announcement yet.

Breaking sql-queries into usefull bits

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Breaking sql-queries into usefull bits

    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!

  • #2
    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

    PHP Code:
    <?
    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.
    resistance is...

    MVC is the current buzz in web application architectures. It comes from event-driven desktop application design and doesn't fit into web application design very well. But luckily nobody really knows what MVC means, so we can call our presentation layer separation mechanism MVC and move on. (Rasmus Lerdorf)

    Comment


    • #3
      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.

      Comment


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

        PHP Code:
        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($query0$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\

        Comment


        • #5
          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.
          resistance is...

          MVC is the current buzz in web application architectures. It comes from event-driven desktop application design and doesn't fit into web application design very well. But luckily nobody really knows what MVC means, so we can call our presentation layer separation mechanism MVC and move on. (Rasmus Lerdorf)

          Comment

          Working...
          X