dbh = @mysql_connect($h,$u,$p); mysql_select_db($db)or die("cannot select database ". mysql_error()); } //closes the DB conection function close(){ if($this->dbh){ return mysql_close($this->dbh); }else{ Base::addLog('db_intf.class.php::DBase_intf::close reports tried to close connection before it was opened'); return false; } } //returns function getColumn($table,$col){ $this->sql_clean_val($table); $this->sql_clean_val($col); if($res= mysql_query("SELECT $col FROM $table")){ $i=1; $rec=mysql_fetch_assoc($res); while($rec){ $x = $rec[$col]; $arr[$i] = $x; $rec=mysql_fetch_assoc($res); $i++; } return $arr; }else{ return mysql_error(); } } //function to get the datatype of a given field function getColType($table, $col){ $this->sql_clean_val($col); $this->sql_clean_val($table); if($type = mysql_field_type(mysql_query("SELECT $col FROM $table LIMIT 1"),0)){ return $type; }else { $this->addLog('db_intf.class.php::DBase_intf::getColType reports: invalid field type ERROR:'.mysql_error(),1); return false; } } function sql_clean_val(&$val){ if(is_array($val)){ $cl_ary = array(); foreach($val as $k=>$v){ $k = mysql_real_escape_string($k); $v = mysql_real_escape_string($v); $cl_ary[$k] = $v; } $val = $cl_ary;//beware, wont work for multi dimensional arrays return $val; }else{ $val = mysql_real_escape_string($val); return $val; } } //function to retrieve a list of records that meet a condition function select($table,$whereCol="none", $val = "none",$col= "*",$orderBy = '',$limit= false){ $this->sql_clean_val($table); if($whereCol !== 'none'){ $this->sql_clean_val($whereCol); $this->sql_clean_val($val); } if($col !== '*'){ $this->sql_clean_val($col); } if($orderBy !== ''){ $this->sql_clean_val($orderBy); } if($limit !== false){ $this->sql_clean_val($limit); } if(is_array($col)){ $col = join(',',$col); } $q = "SELECT $col FROM $table "; if($whereCol != "none" && $val != "none"){ $q .= 'WHERE '.$whereCol.'='; if($this->isQuoteField($table,$whereCol)){ $q .= "'$val'"; }else{ $q .= "$val"; } } $q.=($orderBy)?' ORDER BY '.$orderBy : ''; $q.=($limit !== false)?' LIMIT '.intval($limit): ' '; $res = mysql_query($q); if($res){ while($rec=mysql_fetch_assoc($res)){ $arr[] = $rec; } return $arr; }else { $this->addLog('db_intf.class.php::DBase_intf::select reports: mysql_error:'.mysql_error(),1); return false; } } function select_multival_or($table,$whereCol="none", $val = 0,$col= "*",$orderBy = ''){ $this->sql_clean_val($table); if($whereCol !== 'none'){ $this->sql_clean_val($whereCol); $this->sql_clean_val($val); } if($col !== '*'){ $this->sql_clean_val($col); } if($orderBy !== ''){ $this->sql_clean_val($orderBy); } if(is_array($col)){ $col = join(',',$col); } if(!$val){ return $this->select($table,$whereCol, $val,$col,$orderBy); } if(!is_array($val)){ $val = split(',',$val); } if(is_array($col)){ $col = join(',',$col); } $q = "SELECT $col FROM $table "; $q .= "WHERE "; for($i = 0; $isql_clean_val($table); if($whereCol !== 'none'){ $this->sql_clean_val($whereCol); $this->sql_clean_val($val); } if($col !== '*'){ $this->sql_clean_val($col); } if($orderBy !== ''){ $this->sql_clean_val($orderBy); } if(!$val){ die('select_multival_and() error: no valid values'); } if(!is_array($val)){ if(strstr($val,',')){ } $val = split(',',$val); } if(!is_array($whereCol)){ $whereCol = split(',',$whereCol); } if(is_array($col)){ $col = join(',',$col); } if(count($whereCol) != count($val)){ die('select_multival_and() ERROR: Where column count not equal values'); } $q = "SELECT $col FROM $table "; $q .= "WHERE "; for($i = 0; $isql_clean_val($table); $this->sql_clean_val($cols); $this->sql_clean_val($vals); if($asBool !== false || $asBool !== true){ $this->addLog('DBase_intf::checkDupe reports invalid value for asBool'); return false; } if(!is_array($cols)){ $cols = split(",",$cols); } if(!is_array($vals)){ $vals = split(",",$vals); } $cnum=count($cols); $vnum=count($vals); $q = "SELECT COUNT(*) FROM $table WHERE "; while($cnum > 0){ $q .= "{$cols[$cnum - 1]} = "; $q .= ($this->isQuoteField($table,$cols[$cnum-1]))?"'{$vals[$cnum - 1]}'":"{$vals[$cnum - 1]}"; if($cnum != 1){ $q .= " AND "; } $cnum--; } //$this->addLog('DBase_intf::checkDupe reports query as '.$q);//dev if($res = mysql_query($q)){ //$this->addLog('DBase_intf::checkDupe reports query results as '.mysql_result($res,0));//dev if($asBool){ if(mysql_result($res,0) > 0 ){ $retval = true; }else{ $retval = false; } }else{ $retval = mysql_result($res,0); } //$this->addLog('DBase_intf::checkDupe reports returning '.$retval);//dev return $retval; }else{ //$this->addLog('DBase_intf::checkDupe reports query results as '.$res);//dev return false; } } //function to delete row(s) function delete($table, $whereCol = "", $whereVal = ""){ $this->sql_clean_val($table); if($whereCol !== ''){ $this->sql_clean_val($whereCol); $this->sql_clean_val($whereVal); } $q = "DELETE FROM $table "; if(isset($whereCol) && isset($whereVal)){ $q .= "WHERE $whereCol = "; if($this->isQuoteField($table,$whereCol)){ $q .= "'$whereVal'"; }else{ $q .= "$whereVal"; } } if(mysql_query($q)){ return true; }else{ die('no good'); $this->state_error = mysql_error(); return false; } } function deleteLessThen($table, $whereCol = "", $whereVal = "", $andEqual = false){ $this->sql_clean_val($table); if($whereCol !== ''){ $this->sql_clean_val($whereCol); $this->sql_clean_val($whereVal); } if($andEqual !== false && $andEqual !== true){ Base::addLog('db_intf.class.php::DBase_intf::deleteLessThen reports accessed with invalid andEqual',1); return false; } $q = "DELETE FROM $table "; if(isset($whereCol) && isset($whereVal)){ $q .= "WHERE $whereCol <"; $q .= ($andEqual)? '= ':' '; if($this->isQuoteField($table,$whereCol)){ $q .= "'$whereVal'"; }else{ $q .= "$whereVal"; } } addLog('DBase_intf::deleteLessThen query: '.$q); if($res = mysql_query($q)){ addLog('DBase_intf::deleteLessThen reports deletion was successful TABLE '.$table); return true; }else return mysql_error(); } //function to insert a row function insert($table,$cols,$vals){ $this->sql_clean_val($table); $this->sql_clean_val($cols); $this->sql_clean_val($vals); if(!is_array($cols)){ $cols = split(",",$cols); $vals = split(",",$vals); } $cnum = count($cols); $vnum = count($vals); if($cnum != $vnum){ return "$cnum and $vnum . Number of Columns and the number of Values not equal\nI cannot execute your query."; }else{ for($i = 0;$iisQuoteField($table,$cols[$i]))? "'".$vals[$i]."'":$vals[$i]; } $cols = join(',',$cols); $vals = join(',',$vals); $q = "INSERT INTO $table($cols) VALUES($vals)"; $this->addLog('DBase_intf::insert reports query as: '.$q); if($res = mysql_query($q)){ $this->addLog('insert returnng true'); return true; }else{ $this->addLog('insert returning false: '.mysql_error()); return false; } } } //function to update a row with given criteria function update($table,$cols, $vals, $whereCol="",$whereVal=""){ $this->sql_clean_val($table); $this->sql_clean_val($cols); $this->sql_clean_val($vals); if($whereCol !== ''){ $this->sql_clean_val($whereCol); $this->sql_clean_val($whereVal); } if(!is_array($cols) && strstr($cols,',')){ $cols = split(",",$cols); $vals = split(",",$vals); } if(is_array($cols)){ $cnum=count($cols); $vnum=count($vals); }else { $cnum=1; $vnum=1; } if($cnum != $vnum){ return "Number of Columns and the number of Values not equal\nI cannot execute your query."; }else{ $q = "UPDATE $table SET "; if(is_array($cols)){ while($cnum > 0){ $q .= "{$cols[$cnum - 1]} = "; $q .= ($this->isQuoteField($table,$cols[$cnum - 1]))?"'{$vals[$cnum - 1]}'":"{$vals[$cnum - 1]}"; if($cnum != 1){ $q .= ","; } $cnum--; } }else{ $q .= "$cols = "; $q .= ($this->isQuoteField($table,$cols))?"'$vals'":"$vals"; } if($this->isQuoteField($table,$whereCol)){ $q .= " WHERE $whereCol = '$whereVal'"; }else{ $q .= " WHERE $whereCol = $whereVal"; } return ($res = mysql_query($q)) ? true : false; } } function tableDesc($table){ $this->sql_clean_val($table); if($res = mysql_query("DESCRIBE $table")){ while($r = mysql_fetch_assoc($res)){ $rec_arry[] = $r; } return $rec_arry; }else return addLog("MYSQL ERROR: ". mysql_error()); } //not working well yet function getFields($table){ $this->sql_clean_val($table); if($res = mysql_query("SHOW COLUMNS FROM $table")){ $t_arr = array(); while ($r = mysql_fetch_assoc($res)){ array_push($t_arr, $r['Field']); } return $t_arr; }else { $this->addLog('db_intf.class.php::DBase_intf::getFields reports could not get fields: MYSQL ERROR: '. mysql_error()); return false; } } function getFieldObjects($table){ $this->sql_clean_val($table); if($tquery = mysql_query('SELECT * FROM '.$table.' LIMIT 1')){ $trec = mysql_fetch_row($tquery); }else{ $this->addLog('db_intf.class.php::DBase_intf::getFieldObjects reports error getting row from DB:'.mysql_errno()); return false; } $fObj_ary = array(); if($trec){ foreach($trec as $fld=>$val){ $tObj = mysql_fetch_field($tquery,$fld); if($tObj){ $fObj_ary[$tObj->name] = $tObj; } } }else{ $this->addLog('db_intf.class.php::DBase_intf::getFieldObjects reports: query unsuccessful',1); return false; } return $fObj_ary; } function isQuoteField($tbl,$fld){ $this->sql_clean_val($tbl); $this->sql_clean_val($fld); $typ = $this->getColType($tbl,$fld); return (($typ == "VARCHAR") || ($typ == "LONGCHAR") || ($typ == "DATETIME") || ($typ == "string") || ($typ == "BLOB") || ($typ == "blob"))? true : false; } function fieldExists($t,$c){ $this->sql_clean_val($t); $this->sql_clean_val($c); return in_array($c,$this->getFields($t)); } function showTables(){ if($res = $this->blindQuery('SHOW TABLES FROM '.$this->database)){ while($rec = mysql_fetch_assoc($res)){ $retval[] = array_pop($rec); } return $retval; } } //wrapper function getTableNames(){ return $this->showTables(); } function isTable($table){ $this->sql_clean_val($table); return in_array($table,$this->showTables()); } function blindQuery($str){ return mysql_query($str); } //constructor function DBase_intf(){ if (!defined("MYSQL_HOST")){ require_once(DBCONFIGPATH) or die("Failed to locate database configuration file, please update Invoice system configuration with the correct path"); }else{ $this->connect(MYSQL_HOST,MYSQL_USER,MYSQL_PASSWORD, MYSQL_DATABASE); } } } ?>