library/DBC_database.php

   1 <?php
   2 /**
   3  * DBC DUTCH SYSTEM
   4  * DATABASE FUNCTIONS - A Simple but effective framework over database operations
   5  *
   6  * Cristian Navalici lemonsoftware [ @ ] gmail [ . ] com
   7  * @version 2.0 May 2008
   8  *
   9  */
  10
  11 // if TRUE, before every sql statement will run SET NAMES utf8
  12 define('SETNAMES', TRUE);
  13
  14 //-----------------------------------------------------------------------------
  15 /**
  16  * DBC SQL SELECT FUNCTION
  17  *
  18  * @param   string      $what - what to select
  19  * @param   string      $from - the table to retrieve the results from
  20  * @param   array       the where clause
  21  * @param   string      the order clause
  22  * @param   string      the join clause
  23  * @param   int         debug flag - if 1, just echo the query, don't execute anything
  24  * @return  resource    returns the mysql resource or FALSE
  25  */
  26 function dsql_select($what, $from, $where = NULL, $order = NULL, $join = NULL, $debug = 0) {
  27
  28     // make the query string
  29     $q = "SELECT $what FROM $from ";
  30
  31     if ( $join ) {
  32         $q .= " JOIN $join ";
  33     }
  34
  35     if ( $where ) {
  36             $wherestr   = _dsql_where($where);
  37             $q .= "WHERE $wherestr ";
  38     }
  39     if ( $order )    $q .= "ORDER BY $order ";
  40
  41     if ( $debug ) echo $q . '<br />';
  42     else return _dsql_query($q);
  43 }
  44
  45
  46 //-----------------------------------------------------------------------------
  47 /**
  48  * DBC SQL START TRANSACTION
  49  *
  50  * @param   none
  51  * @return  bool
  52  */
  53 function dsql_starttrans() {
  54
  55     $q = 'START TRANSACTION';
  56     return _dsql_query($q);
  57 }
  58
  59 //-----------------------------------------------------------------------------
  60 /**
  61  * DBC SQL COMMIT TRANSACTION
  62  *
  63  * @param   none
  64  * @return  bool
  65  */
  66 function dsql_commit() {
  67
  68     $q = 'COMMIT';
  69     return _dsql_query($q);
  70 }
  71
  72 //-----------------------------------------------------------------------------
  73 /**
  74  * DBC SQL UPDATE FUNCTION
  75  *
  76  * @param   string      the table to retrieve the results from
  77  * @param   array       an associative array of update values
  78  * @param   array       the where clause
  79  * @param   int         debug flag - if 1, just echo the query, don't execute anything
  80  * @return  bool
  81  */
  82 function dsql_update($table, $set, $where, $debug = 0) {
  83
  84     $setstr     = _dsql_set($set);
  85     $wherestr   = _dsql_where($where);
  86
  87     // make the query string
  88     $q = "UPDATE $table SET $setstr WHERE $wherestr";
  89
  90     if ( $debug ) echo $q . '<br />';
  91     else return _dsql_query($q);
  92 }
  93
  94
  95 //-----------------------------------------------------------------------------
  96 /**
  97  * DBC SQL INSERT FUNCTION
  98  *
  99  * @param   string      $table - the table to insert the values
 100  * @param   array       $flds - an array with table fields
 101  * @param   array       $val - an array with inserted  values
 102  * @param   int         debug flag - if 1, just echo the query, don't execute anything
 103  * @return  bool
 104  */
 105 function dsql_insert($table, $flds, $val, $debug = 0) {
 106
 107     $fields     = _dsql_fields($flds);
 108     $values     = _dsql_ivalues($val);
 109
 110     // make the query string
 111     $q = "INSERT INTO $table $fields $values";
 112
 113     if ( $debug ) echo $q . '<br />';
 114     else return _dsql_query($q);
 115 }
 116
 117
 118 //-----------------------------------------------------------------------------
 119 /**
 120  * DBC SQL INSERT ON DUPLICATE FUNCTIONS
 121  *
 122  * @param   string      $table - the table to insert the values
 123  * @param   array       $flds - an array with table fields
 124  * @param   array       $val - an array with inserted  values
 125  * @param   int         debug flag - if 1, just echo the query, don't execute anything
 126  * @return  bool
 127  */
 128 function dsql_insert_duplicate($table, $flds, $val, $set = 0, $debug = 0) {
 129
 130     $fields     = _dsql_fields($flds);
 131     $values     = _dsql_ivalues($val);
 132     $update     = _dsql_set($set);
 133
 134     // make the query string
 135     $q = "INSERT INTO $table $fields $values ON DUPLICATE KEY UPDATE $update";
 136
 137     if ( $debug ) echo $q . '<br />';
 138     else return _dsql_query($q);
 139 }
 140
 141 //-----------------------------------------------------------------------------
 142 /**
 143  * DBC SQL LAST INSERTED ID FUNCTION
 144  *
 145  * @param   none
 146  * @return  int
 147  */
 148 function dsql_lastid() {
 149     if ( isset($GLOBALS['dbh']) ) {
 150         // if we call from webinterface
 151         $r = mysql_insert_id($GLOBALS['dbh']) or die(mysql_error());
 152     } else {
 153         // if we call from CLI
 154         $r = mysql_insert_id() or die(mysql_error());
 155     }
 156
 157     return $r;
 158 }
 159
 160 //-----------------------------------------------------------------------------
 161 //
 162 //          PRIVATE FUNCTIONS BELOW
 163 //-----------------------------------------------------------------------------
 164
 165
 166 //-----------------------------------------------------------------------------
 167 /**
 168  * DBC SQL QUERY FUNCTION
 169  *
 170  * @param   string      $str - the query string
 171  * @return  resource|bool
 172  */
 173 function _dsql_query($str) {
 174
 175     if ( isset($GLOBALS['dbh']) ) {
 176         // if we call from webinterface
 177         if ( SETNAMES ) mysql_query("SET NAMES utf8", $GLOBALS['dbh']);
 178         $r = mysql_query($str, $GLOBALS['dbh']) or die(mysql_error());
 179     } else {
 180         // if we call from CLI
 181         $r = mysql_query($str) or die(mysql_error());
 182     }
 183
 184     return $r;
 185 }
 186
 187
 188 //-----------------------------------------------------------------------------
 189 /**
 190  * PREPARE THE SET STRING
 191  *
 192  * @param   array       the array to compile
 193  * @return  string
 194  */
 195 function _dsql_set($setarr) {
 196     $setstr = '';
 197
 198     foreach ( $setarr as $sk => $sv) {
 199         $setstr .= " {$sk} = {$sv},";
 200     }
 201     $setstr = substr(trim($setstr), 0, -1); // cut the last ,
 202
 203     return $setstr;
 204 }
 205
 206 //-----------------------------------------------------------------------------
 207 /**
 208  * PREPARE THE WHERE STRING
 209  *
 210  * @param   array       the array to compile
 211  * @return  string
 212  */
 213 function _dsql_where($wherearr) {
 214     if ( !$wherearr ) return NULL;
 215     $wherestr = '';
 216
 217     foreach ( $wherearr as $wk => $wv) {
 218         // for the same key we could have an array as arg = multiple values (otherwise keys are not unique)
 219         if ( is_array($wv) ) {
 220
 221             foreach ( $wv as $v ) {
 222                 // if we have value starting with <=; != or => then we don't use = for them
 223                 $first = substr(trim($v), 0, 1);
 224                 if ( $first != '>' && $first != '<' && $first != '!' ) $sign = '=';
 225                 else $sign = '';
 226
 227                 $wherestr .= " {$wk} $sign {$v} ";
 228             } // foreach
 229
 230         } else {
 231
 232             // if we have value starting with <=; != or => then we don't use = for them
 233             $first = substr(trim($wv), 0, 1);
 234             if ( $first != '>' && $first != '<' && $first != '!' ) $sign = '=';
 235             else $sign = '';
 236
 237
 238             $wherestr .= " {$wk} $sign {$wv} ";
 239         }
 240     } // foreach
 241
 242     return $wherestr;
 243 }
 244
 245 //-----------------------------------------------------------------------------
 246 /**
 247  * PREPARE THE FIELDS STRING
 248  *
 249  * @param   array       the array to compile
 250  * @return  string
 251  */
 252 function _dsql_fields($fiearr) {
 253     if ( !is_array($fiearr) || !$fiearr ) return '';
 254
 255     $fiestr = '(';
 256     foreach ( $fiearr as $fk => $fv) {
 257         $fiestr .= " {$fv}, ";
 258     }
 259     $fiestr = substr(trim($fiestr), 0, -1); // cut the last ,
 260     $fiestr .= ')';
 261
 262     return $fiestr;
 263 }
 264
 265 //-----------------------------------------------------------------------------
 266 /**
 267  * PREPARE THE VALUES STRING
 268  *
 269  * used by insert
 270  *
 271  * @param   array       the array to compile
 272  * @return  string
 273  */
 274 function _dsql_ivalues($values) {
 275
 276     $valstr = 'VALUES (';
 277     foreach ( $values as $vk => $vv) {
 278         $valstr .= ( is_int($vv) ) ? "{$vv}, " : " '{$vv}', ";
 279     }
 280     $valstr = substr(trim($valstr), 0, -1); // cut the last ,
 281     $valstr .= ')';
 282
 283     return $valstr;
 284 }
 285
 286 //-----------------------------------------------------------------------------
 287 ?>