[Sommaire] [Imprimer]

   1  <?php /* INCLUDES $Id: db_connect.php,v 1.42.4.3 2007/01/31 09:36:52 ajdonnison Exp $ */
   2  /**
   3  * Generic functions based on library function (that is, non-db specific)
   4  *
   5  * @todo Encapsulate into a database object
   6  */
   7  
   8  if (!defined('DP_BASE_DIR')) {
   9      die('You should not access this file directly');
  10  }
  11  
  12  // load the db specific handlers
  13  //require_once( "{$dPconfig['root_dir']}/includes/db_{$dPconfig['dbtype']}.php" );
  14  //require_once( "./includes/db_adodb.php" );
  15  require_once  DP_BASE_DIR."/includes/db_adodb.php";
  16  
  17  // make the connection to the db
  18  db_connect( $dPconfig['dbhost'], $dPconfig['dbname'],
  19      $dPconfig['dbuser'], $dPconfig['dbpass'], $dPconfig['dbpersist'] );
  20  
  21      
  22  /*
  23  * Having successfully established the database connection now,
  24  * we will hurry up to load the system configuration details from the database.
  25  */
  26  
  27  $sql = "SELECT config_name, config_value, config_type FROM config";
  28  $rs = $db->Execute($sql);
  29  
  30  if ($rs) { // Won't work in install mode.
  31      $rsArr = $rs->GetArray();
  32  
  33      foreach ($rsArr as $c) {
  34          if ($c['config_type'] == 'checkbox') {
  35              $c['config_value'] = ($c['config_value'] == 'true') ? true : false;
  36          }
  37          $dPconfig["{$c['config_name']}"] = $c['config_value'];
  38      }
  39  }
  40  
  41  
  42  
  43  /**
  44  * This global function loads the first field of the first row returned by the query.
  45  *
  46  * @param string The SQL query
  47  * @return The value returned in the query or null if the query failed.
  48  */
  49  function db_loadResult( $sql ) {
  50      $cur = db_exec( $sql );
  51      $cur or exit( db_error() );
  52      $ret = null;
  53      if ($row = db_fetch_row( $cur )) {
  54          $ret = $row[0];
  55      }
  56      db_free_result( $cur );
  57      return $ret;
  58  }
  59  
  60  /**
  61  * This global function loads the first row of a query into an object
  62  *
  63  * If an object is passed to this function, the returned row is bound to the existing elements of <var>object</var>.
  64  * If <var>object</var> has a value of null, then all of the returned query fields returned in the object. 
  65  * @param string The SQL query
  66  * @param object The address of variable
  67  */
  68  function db_loadObject( $sql, &$object, $bindAll=false , $strip = true) {
  69      if ($object != null) {
  70          $hash = array();
  71          if( !db_loadHash( $sql, $hash ) ) {
  72              return false;
  73          }
  74          bindHashToObject( $hash, $object, null, $strip, $bindAll );
  75          return true;
  76      } else {
  77          $cur = db_exec( $sql );
  78          $cur or exit( db_error() );
  79          if ($object = db_fetch_object( $cur )) {
  80              db_free_result( $cur );
  81              return true;
  82          } else {
  83              $object = null;
  84              return false;
  85          }
  86      }
  87  }
  88  
  89  /**
  90  * This global function return a result row as an associative array 
  91  *
  92  * @param string The SQL query
  93  * @param array An array for the result to be return in
  94  * @return <b>True</b> is the query was successful, <b>False</b> otherwise
  95  */
  96  function db_loadHash( $sql, &$hash ) {
  97      $cur = db_exec( $sql );
  98      $cur or exit( db_error() );
  99      $hash = db_fetch_assoc( $cur );
 100      db_free_result( $cur );
 101      if ($hash == false) {
 102          return false;
 103      } else {
 104          return true;
 105      }
 106  }
 107  
 108  /**
 109  * Document::db_loadHashList()
 110  *
 111  * { Description }
 112  *
 113  * @param string $index
 114  */
 115  function db_loadHashList( $sql, $index='' ) {
 116      $cur = db_exec( $sql );
 117      $cur or exit( db_error() );
 118      $hashlist = array();
 119      while ($hash = db_fetch_array( $cur )) {
 120          $hashlist[$hash[$index ? $index : 0]] = $index ? $hash : $hash[1];
 121      }
 122      db_free_result( $cur );
 123      return $hashlist;
 124  }
 125  
 126  /**
 127  * Document::db_loadList()
 128  *
 129  * { Description }
 130  *
 131  * @param [type] $maxrows
 132  */
 133  function db_loadList( $sql, $maxrows=NULL ) {
 134      GLOBAL $AppUI;
 135      if (!($cur = db_exec( $sql ))) {;
 136          $AppUI->setMsg( db_error(), UI_MSG_ERROR );
 137          return false;
 138      }
 139      $list = array();
 140      $cnt = 0;
 141      while ($hash = db_fetch_assoc( $cur )) {
 142          $list[] = $hash;
 143          if( $maxrows && $maxrows == $cnt++ ) {
 144              break;
 145          }
 146      }
 147      db_free_result( $cur );
 148      return $list;
 149  }
 150  
 151  /**
 152  * Document::db_loadColumn()
 153  *
 154  * { Description }
 155  *
 156  * @param [type] $maxrows
 157  */
 158  function db_loadColumn( $sql, $maxrows=NULL ) {
 159      GLOBAL $AppUI;
 160      if (!($cur = db_exec( $sql ))) {;
 161          $AppUI->setMsg( db_error(), UI_MSG_ERROR );
 162          return false;
 163      }
 164      $list = array();
 165      $cnt = 0;
 166      $row_index = null;
 167      while ($row = db_fetch_row( $cur )) {
 168          if (! isset($row_index)) {
 169              if (isset($row[0])) {
 170                  $row_index = 0;
 171              } else {
 172                  $row_indices = array_keys($row);
 173                  $row_index = $row_indices[0];
 174              }
 175          }
 176          $list[] = $row[$row_index];
 177          if( $maxrows && $maxrows == $cnt++ ) {
 178              break;
 179          }
 180      }
 181      db_free_result( $cur );
 182      return $list;
 183  }
 184  
 185  /* return an array of objects from a SQL SELECT query
 186   * class must implement the Load() factory, see examples in Webo classes
 187   * @note to optimize request, only select object oids in $sql
 188   */
 189  function db_loadObjectList( $sql, $object, $maxrows = NULL ) {
 190      $cur = db_exec( $sql );
 191      if (!$cur) {
 192          die( "db_loadObjectList : " . db_error() );
 193      }
 194      $list = array();
 195      $cnt = 0;
 196      $row_index = null;
 197      while ($row = db_fetch_array( $cur )) {
 198          if (! isset($row_index)) {
 199              if (isset($row[0]))
 200                  $row_index = 0;
 201              else {
 202                  $row_indices = array_keys($row);
 203                  $row_index = $row_indices[0];
 204              }
 205          }
 206          $object->load( $row[$row_index] );
 207          $list[] = $object;
 208          if( $maxrows && $maxrows == $cnt++ ) {
 209              break;
 210          }
 211      }
 212      db_free_result( $cur );
 213      return $list;
 214  }
 215  
 216  
 217  /**
 218  * Document::db_insertArray()
 219  *
 220  * { Description }
 221  *
 222  * @param [type] $verbose
 223  */
 224  function db_insertArray( $table, &$hash, $verbose=false ) {
 225      $fmtsql = "insert into $table ( %s ) values( %s ) ";
 226      foreach ($hash as $k => $v) {
 227          if (is_array($v) or is_object($v) or $v == NULL) {
 228              continue;
 229          }
 230          $fields[] = $k;
 231          $values[] = "'" . db_escape(htmlspecialchars( $v )) . "'";
 232      }
 233      $sql = sprintf( $fmtsql, implode( ",", $fields ) ,  implode( ",", $values ) );
 234  
 235      ($verbose) && print "$sql<br />\n";
 236  
 237      if (!db_exec( $sql )) {
 238          return false;
 239      }
 240      $id = db_insert_id();
 241      return true;
 242  }
 243  
 244  /**
 245  * Document::db_updateArray()
 246  *
 247  * { Description }
 248  *
 249  * @param [type] $verbose
 250  */
 251  function db_updateArray( $table, &$hash, $keyName, $verbose=false ) {
 252      $fmtsql = "UPDATE $table SET %s WHERE %s";
 253      foreach ($hash as $k => $v) {
 254          if( is_array($v) or is_object($v) or $k[0] == '_' ) // internal or NA field
 255              continue;
 256  
 257          if( $k == $keyName ) { // PK not to be updated
 258              $where = "$keyName='" . db_escape( $v ) . "'";
 259              continue;
 260          }
 261          if ($v == '') {
 262              $val = 'NULL';
 263          } else {
 264              $val = "'" . db_escape(htmlspecialchars( $v )) . "'";
 265          }
 266          $tmp[] = "$k=$val";
 267      }
 268      $sql = sprintf( $fmtsql, implode( ",", $tmp ) , $where );
 269      ($verbose) && print "$sql<br />\n";
 270      $ret = db_exec( $sql );
 271      return $ret;
 272  }
 273  
 274  /**
 275  * Document::db_delete()
 276  *
 277  * { Description }
 278  *
 279  */
 280  function db_delete( $table, $keyName, $keyValue ) {
 281      $keyName = db_escape( $keyName );
 282      $keyValue = db_escape( $keyValue );
 283      $ret = db_exec( "DELETE FROM $table WHERE $keyName='$keyValue'" );
 284      return $ret;
 285  }
 286  
 287  
 288  /**
 289  * Document::db_insertObject()
 290  *
 291  * { Description }
 292  *
 293  * @param [type] $keyName
 294  * @param [type] $verbose
 295  */
 296  function db_insertObject( $table, &$object, $keyName = NULL, $verbose=false ) {
 297      $fmtsql = "INSERT INTO `$table` ( %s ) VALUES ( %s ) ";
 298      foreach (get_object_vars( $object ) as $k => $v) {
 299          if (is_array($v) or is_object($v) or $v == NULL) {
 300              continue;
 301          }
 302          if ($k[0] == '_') { // internal field
 303              continue;
 304          }
 305          $fields[] = $k;
 306          $values[] = "'" . db_escape(htmlspecialchars( $v )) . "'";
 307      }
 308      $sql = sprintf( $fmtsql, implode( ",", $fields ) ,  implode( ",", $values ) );
 309      ($verbose) && print "$sql<br />\n";
 310      if (!db_exec( $sql )) {
 311          return false;
 312      }
 313      $id = db_insert_id();
 314      ($verbose) && print "id=[$id]<br />\n";
 315      if ($keyName && $id)
 316          $object->$keyName = $id;
 317      return true;
 318  }
 319  
 320  /**
 321  * Document::db_updateObject()
 322  *
 323  * { Description }
 324  *
 325  * @param [type] $updateNulls
 326  */
 327  function db_updateObject( $table, &$object, $keyName, $updateNulls=true ) {
 328      $fmtsql = "UPDATE `$table` SET %s WHERE %s";
 329      foreach (get_object_vars( $object ) as $k => $v) {
 330          if( is_array($v) or is_object($v) or $k[0] == '_' ) { // internal or NA field
 331              continue;
 332          }
 333          if( $k == $keyName ) { // PK not to be updated
 334              $where = "$keyName='" . db_escape( $v ) . "'";
 335              continue;
 336          }
 337          if ($v === NULL && !$updateNulls) {
 338              continue;
 339          }
 340          if( $v === '' ) {
 341              $val = "''";
 342          } else {
 343              $val = "'" . db_escape(htmlspecialchars( $v )). "'";
 344          }
 345          $tmp[] = "$k=$val";
 346      }
 347      if (count ($tmp)) {
 348          $sql = sprintf( $fmtsql, implode( ",", $tmp ) , $where );
 349          return db_exec( $sql );
 350      } else {
 351          return true;
 352      }
 353  }
 354  
 355  /**
 356  * Document::db_dateConvert()
 357  *
 358  * { Description }
 359  *
 360  */
 361  function db_dateConvert( $src, &$dest, $srcFmt ) {
 362      $result = strtotime( $src );
 363      $dest = $result;
 364      return ( $result != 0 );
 365  }
 366  
 367  /**
 368  * Document::db_datetime()
 369  *
 370  * { Description }
 371  *
 372  * @param [type] $timestamp
 373  */
 374  function db_datetime( $timestamp = NULL ) {
 375      if (!$timestamp) {
 376          return NULL;
 377      }
 378      if (is_object($timestamp)) {
 379          return $timestamp->toString( '%Y-%m-%d %H:%M:%S');
 380      } else {
 381          return strftime( '%Y-%m-%d %H:%M:%S', $timestamp );
 382      }
 383  }
 384  
 385  /**
 386  * Document::db_dateTime2locale()
 387  *
 388  * { Description }
 389  *
 390  */
 391  function db_dateTime2locale( $dateTime, $format ) {
 392      if (intval( $dateTime)) {
 393          $date = new CDate( $dateTime );
 394          return $date->format( $format );
 395      } else {
 396          return null;
 397      }
 398  }
 399  
 400  /*
 401  * copy the hash array content into the object as properties
 402  * only existing properties of object are filled. when undefined in hash, properties wont be deleted
 403  * @param array the input array
 404  * @param obj byref the object to fill of any class
 405  * @param string
 406  * @param boolean
 407  * @param boolean
 408  */
 409  function bindHashToObject( $hash, &$obj, $prefix=NULL, $checkSlashes=true, $bindAll=false ) {
 410      is_array( $hash ) or die( "bindHashToObject : hash expected" );
 411      is_object( $obj ) or die( "bindHashToObject : object expected" );
 412  
 413      if ($bindAll) {
 414          foreach ($hash as $k => $v) {
 415              $obj->$k = ($checkSlashes && get_magic_quotes_gpc()) ? stripslashes( $hash[$k] ) : $hash[$k];
 416          }
 417      } else if ($prefix) {
 418          foreach (get_object_vars($obj) as $k => $v) {
 419              if (isset($hash[$prefix . $k ])) {
 420                  $obj->$k = ($checkSlashes && get_magic_quotes_gpc()) ? stripslashes( $hash[$k] ) : $hash[$k];
 421              }
 422          }
 423      } else {
 424          foreach (get_object_vars($obj) as $k => $v) {
 425              if (isset($hash[$k])) {
 426                  $obj->$k = ($checkSlashes && get_magic_quotes_gpc()) ? stripslashes( $hash[$k] ) : $hash[$k];
 427              }
 428          }
 429      }
 430      //echo "obj="; print_r($obj); exit;
 431  }
 432  ?>