Smarty Forum Index Smarty
WARNING: All discussion is moving to https://reddit.com/r/smarty, please go there! This forum will be closing soon.

Generic Abstract Database Layer

 
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.    Smarty Forum Index -> Tips and Tricks
View previous topic :: View next topic  
Author Message
Hielke Hoeve
Smarty Elite


Joined: 06 Jan 2006
Posts: 406
Location: Netherlands

PostPosted: Sat Jan 28, 2006 11:31 am    Post subject: Generic Abstract Database Layer Reply with quote

Here is a nice little function that you can use to implement layers for different database servers, MySQL Postgres, MSSQL etc.

It simply allows you to request information from the database without knowing flavors of SQL. All you have to do is provide some basic information like the projection elements and selection elements.

Ofcourse this function isn't very useful to you if you don't intent to make your website code available to others, or when you use only one type of database.

Code:
   /*
    * all vars must be arrays or NULL
    *
    * $projection    array of elements to become columns
    *
    * $table       array of tables to use in the other vars
    *
    * $joins       array of arrays of 3 elements:
                * JOIN TYPE: eg LEFT JOIN, INNER JOIN, RIGHT JOIN
                * table to join with: eg table2
                * ON CONDITION: eg table1.a = table2.a
                * or
                * USING CONDITION: eg USING(table1.a, table2.a)
    *
    * $selection   array of arrays of 3 elements:
                * ITEM TO BE CHECKED: table1.a
                * OPERATOR: eg =, >, <, LIKE, !=
                * ITEM OR VALUE TO BE USED IN CHECK: eg '1', 'somestring', table2.a                
    *
    * $group      array of items to GROUP on
    *            
    * $order      array of items to order on ended by a var that is either DESC or ASC: eg 'table1.a ASC' or 'table1.a, table1.b DESC'
    *
    * $limit      array of 2 items that determine the amount of results:
                * eg 1,2 -> return 2 elements starting with the 2nd item: return 2nd item and 3rd item you find.
                * eg 0,10 -> return 10 elements starting with the 1st item: return 1st item through 10th item you find.
    *
    */
   function select_simple_query( $projection, $table, $joins, $selection, $group, $order, $limit ) {
      
      $query = "SELECT ";
      
      $query .= $projection[0];
      for($i=1; $i<count($projection); $i++){
         if($projection[$i] != "" && $projection[$i] != null)
            $query .= ", " . $projection[$i];
      }
      
      $query .= " FROM ";
      
      $query .= $table[0];
      for($i=1; $i<count($table); $i++){
         if($table[$i] != "" && $table[$i] != null)
            $query .= ", " . $table[$i];
      }
      
      if($joins != NULL){
         $query .= " ".$joins[0][0]." ".$joins[0][1]." ON ".$joins[0][2];
         for($i=1; $i<count($joins); $i++){
            if($joins[$i] != "" && $joins[$i] != null) {
               $query .= " ".$joins[$i][0]." ".$joins[$i][1]." ON ".$joins[$i][2];
            }
         }
      }
      
      if($selection != NULL){
         $query .= " WHERE ";
         
         $query .= $selection[0][0]." ".$selection[0][1]." ".$selection[0][2];
         for($i=1; $i<count($selection); $i++){
            if($selection[$i] != "" && $selection[$i] != null) {
               $query .= " ".$selection[$i]." ";
               $i++;
               $query .= $selection[$i][0]." ".$selection[$i][1]." ".$selection[$i][2];
            }
         }   
      }
      
      if($group != NULL) {
         $query .= " GROUP BY ";
         
         $query .= $group[0];
         for($i=1; $i<count($group); $i++){
            if($group[$i] != "" && $group[$i] != null)
               $group .= " ".$group[$i]." ";
         }
      }
      
      if($order != NULL) {
         $query .= " ORDER BY ";
         
         $query .= $order[0];
         for($i=1; $i<(count($order)-1); $i++){
            if($order[$i] != "" && $order[$i] != null)
               $query .= ", ".$order[$i]." ";
         }
         $pos = count($order)-1;
         $query .= " ".$order[$pos];
      }
      
      if($limit != NULL) {
         $query .= " LIMIT ".$limit[0].",".$limit[1];
      }
      
      $this->debug[] = $query;
      $result = mysql_query($query);
      $this->numqueries++;
      
      if( mysql_errno == 0 ) {
         $results = array();
         while($item = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $results[] = $item;
         }
         
         return $results;
      }
      else{
         echo "There is some mysql error:<br>".mysql_errno().": ".mysql_error()."<br>";
         return NULL;
      }
   }
Back to top
View user's profile Send private message
Display posts from previous:   
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.    Smarty Forum Index -> Tips and Tricks All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group
Protected by Anti-Spam ACP