<?php
/* 
 Site DB/SQL functions
 (c) 2004-2007 by "Oleg Savchuk" <hide@address.com>

 part of phpProjectMaster project
 http://phpprojmaster.sourceforge.net

 The contents of this file are subject to the GNU GENERAL PUBLIC LICENSE
 http://www.gnu.org/copyleft/gpl.html
*/

//********************************************  DB functions
// connect to db and 
// SET GLOBALS: $dbh
function db_connect(){
 global $dbh, $DB_DBNAME, $DB_HOST,$DB_USER,$DB_PWD;

 $dbh=mysql_connect($DB_HOST,$DB_USER,$DB_PWD) or die('Cannot connect to the database because:'.mysql_error());
 mysql_select_db($DB_DBNAME, $dbh) or die('Cannot select db'.mysql_error());
 db_query("SET NAMES utf8");
//need? db_query("SET collation_connection = 'utf8_general_ci'");

 return $dbh;
}

//**************** check connection and reconnect if necessary
function db_checkconnect($dbh1=NULL){
 global $dbh;
 if (!$dbh1) $dbh1=&$dbh;

 if (!$dbh1 or !mysql_ping($dbh1)) {
    db_connect();
    $dbh1=&$dbh;
 }

 return $dbh1;
}

// ******************
function db_disconnect(){
 global $dbh;

 mysql_close($dbh);
}

// ******************
function db_query($sql, $dbh1=NULL, $skiperr=0){

 $dbh1=db_checkconnect($dbh1);

// rw($sql);
 $sth=mysql_query($sql, $dbh1);
 if (!$sth && $skiperr) return;
 catch_db_err($dbh1, $sth, $sql);

 return $sth;
}

# return one value (0 column or named column) from $sql
function db_value($sql,$field_name=''){
 $sth=db_query($sql);
 if ($field_name){
    $row=mysql_fetch_assoc($sth);
    return $row[$field_name];
 }else{
    $row=mysql_fetch_row($sth);
    return $row[0];
 }
}

# return one row from $sql
function db_row($sql){
 $sth=db_query($sql);
 return mysql_fetch_assoc($sth);
}

# return array of rows from $sql
# return as ARRAY of HASHES
function db_array($sql){
 $sth=db_query($sql);
 $res=array();
 while($row=mysql_fetch_assoc($sth)){
   $res[]=$row;
 };
 return $res;
}

# return array of rows from $sql
# return as ARRAY of ARRAYS
function db_array2($sql){
 $sth=db_query($sql);
 $res=array();
 while($row=mysql_fetch_row($sth)){
   $res[]=$row;
 };
 return $res;
}


##########################
function get_identity($dbh1=NULL){

 $dbh1=db_checkconnect($dbh1);

 return mysql_insert_id($dbh1);

/*
# Windows/MSSQL stuff
# my $sql='select @@IDENTITY id';
# my $sth=db_query($sql);
# my $hr=$sth->fetchrow_hashref;
# return $hr->{id};
*/
}
############# ALIAS for db_quote
function dbq($value, $field_type='', $dbh1=0){
 return db_quote($value, $field_type, $dbh1);
}

#############
function db_quote($value, $field_type='', $dbh1=0){

 $dbh1=db_checkconnect($dbh1);

 if ($field_type=='x'){
      $value=$value;

 }elseif ($field_type=='s'){ //explicit setting of string, no matter of ~!
      $value="'".mysql_real_escape_string($value, $dbh1)."'"; 

 }elseif ($field_type=='i'){ //explicit setting of number, no matter of ~!
      $value=$value+0;

 }elseif (preg_match("/^~!\w/", $value)){  //special case - if started from ~! - don't quote, just remove '~!'
      $value=substr($value, 2); #cut everything starting from position after ~!

 } else {
      $value=trim($value);
      $value="'".mysql_real_escape_string($value, $dbh1)."'"; //mysql_real_escape_string doesn't add '' at begin/end as with Perl's DBI->quote()
 }

 return $value;
}

//******************** helpers for INSERT/UPDATE

//for INSERT
function db_insert($table, $vars){

 foreach ($vars as $key => $value){
   $names.=( ($names)?',':'' ).dbq($key);
   $values.=( ($values)?',':'' ).dbq($value);
 }
 $sql="insert into $table ($names) VALUES ($values)";

 $sth=db_query($sql);
 return get_identity();
}

//for UPDATE
function db_update($table, $vars, $key_name, $key_id){
 $sql="insert into $table ($names) VALUES ($values)";

 $sth=db_query($sql);
 return get_identity;
}


//################# helper sub for update
//used as (example): 
//       $sql="update TABLE set ".get_sqlupdate_set($IFORM)." where KEY_id=$item_id";
//       db_query($sql);
function get_sqlupdate_set($hitem){
 $sql='';
 while ( list($k,$v)=each($hitem) ) {
    $k=str_replace("`","",$k);      #mysql names should'nt contain ` !
    $sql_fields.=", `$k`";          #then quote name to prevent SQL injection
    $sql.=", $k=".db_quote($v);     #and quote value too, of course :)
 }
 $sql= preg_replace("/^,/",'',$sql);

 return $sql;
}

//################# helper sub for insert
//used as (example): 
//       $sql="insert into TABLE ".get_sqlinsert_set($IFORM,', add_time',', now()');
//       $sth=db_query($sql);
//       $item_id=get_identity();
function get_sqlinsert_set($hitem,$more_fields, $more_values){
 $sql_fields='';
 $sql_values='';

 while ( list($k,$v)=each($hitem) ) {
    $k=str_replace("`","",$k);      #mysql names should'nt contain ` !
    $sql_fields.=", `$k`";          #then quote name to prevent SQL injection
    $sql_values.=", ".db_quote($v); #and quote value too, of course :)
 }
 $sql_fields= preg_replace("/^,/",'',$sql_fields);
 $sql_values= preg_replace("/^,/",'',$sql_values);

 return " ($sql_fields $more_fields) VALUES ($sql_values $more_values)";
}

##########################
function catch_db_err($dbh, $sth, $sql=""){
 if (!$sth) die("Error in DB operation:<br>\n".mysql_error($dbh)."<br>\n$sql");
}

?>