<?
/*
clsDB.php erlaubt den Zugriff auf eine MySQL-Datenbank
Copyright (C) 2004 Thomas Meinusch
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc.,\ 59 Temple Place, Suite 330, Boston, MA
02111-1307, USA.
*/
class DB {
var $database = "";
var $link_id = 0;
var $query_id = 0;
var $record = array();
var $handle;
var $errdesc = "";
var $errno = 0;
var $message = "";
#************************************************************
function connect($server, $database, $user, $password, $handle){
$this->handle=$handle;
if ($this->link_id == 0) {
$this->link_id=mysql_connect($server,$user,$password);
if (!$this->link_id) {
$this->errormsg("Link-ID == false, connect failed");
return;
}
$this->select_db($database);
}
}
function select_db($database="") {
if ($database!="") $this->database=$database;
if(!@mysql_select_db($this->database, $this->link_id)) {
$this->errormsg("cannot use database ".$this->database);
}
}
function close($link_id=-1) {
if ($link_id!=-1) $this->query_id=$link_id;
mysql_close($this->link_id);
}
function query($sql) {
$sql=str_replace("[!handle]", $this->handle, $sql);
$this->query_id = mysql_query($sql,$this->link_id);
if (!$this->query_id) {
$this->errormsg("SQL fehlerhaft: ".$sql);
#print "SQL: $sql<br>\n";
}
return $this->query_id;
}
function unbuffered_query($sql) {
$sql=str_replace("[!handle]", $this->handle, $sql);
$this->query_id = mysql_unbuffered_query($sql,$this->link_id);
if (!$this->query_id) {
$this->errormsg("SQL fehlerhaft: ".$sql);
#print mysql_error();
#print "SQL: $sql<br>\n";
}
return $this->query_id;
}
function data_seek($result,$pos){
if(!mysql_data_seek($result,$pos)){
$this->errormsg("SQL to Position $pos faild");
}
}
function query2xml($xml,$sql) {
$result=mysql_query($sql);
$fields=$this->getFields($sql);
while($row=mysql_fetch_row($result)){
$xml->push('row');
foreach(array_keys($row) as $key){
$xml->element($fields[$key]->name,$row[$key]);
}
$xml->pop();
}
return $xml;
}
function fetch_array($query_id=-1) {
if ($query_id!=-1) $this->query_id=$query_id;
// Fehler ausgeben
if ($query_id==0) return;
$this->record = mysql_fetch_array($this->query_id, MYSQL_ASSOC);
return $this->record;
}
function free_result($query_id=-1) {
if ($query_id!=-1) $this->query_id=$query_id;
return @mysql_free_result($this->query_id);
}
function num_rows($query_id=-1) {
if ($query_id!=-1) $this->query_id=$query_id;
return mysql_num_rows($this->query_id);
}
function fetch_field($query_id=-1) {
return mysql_fetch_field($this->query_id);
}
function num_fields($query_id=-1) {
return mysql_num_fields($this->query_id);
}
function affected_rows(){
return mysql_affected_rows($this->link_id);
}
function insert_id(){
return mysql_insert_id();
}
function errormsg($msg) {
$this->errdesc=mysql_error();
$this->errno=mysql_errno();
$message.="mysql error: $this->errdesc\n<br>";
$message.="mysql error number: $this->errno\n<br>";
$message.="Referer: ".getenv("HTTP_REFERER")."\n<br><br>";
$message.=$msg;
$this->message = $message;
}
function array2fields($arr){
$f="";
foreach($arr as $fld){
if($f!="") $f.=", ";
$f.=$fld;
}
return $f;
}
function createSelectSQL($table, $fields, $where="", $limit=0){
$sql="SELECT $fields FROM $table";
if(strlen($where)) $sql.=" WHERE $where";
if($limit) $sql.=" limit $limit";
return $sql;
}
# or oder array?
function createWhereClause($fields, $values, $emptyvals=false){
$where="";
$max=1;
foreach($fields as $fld){
$val=$values[$fld];
if($fld=="handle" && !is_array($val) && $val=="") $val=$this->handle;
if (strlen($val) || $emptyvals){
if ($where!="") $where.=" and ";
$max=max($max,$size<count($val));
$where.="(";
for ($size=0;$size<count($val);$size++){
if($size>0) $where.=" or ";
if(is_array($val)){
$where.="$fld='".$val[$size]."'";
}else{
$where.="$fld='".$val."'";
}
}
$where.=")";
}
}
return $where;
}
function prepareVal($type, $val, $size=1){
if(is_array($val)){
$x=$val[$size];
}else{
$x=$val;
}
switch($type){
case "date":
return "STR_TO_DATE('".$x."','%d.%m.%Y')";
case "datetime":
return "STR_TO_DATE('".$x."','%d.%m.%Y %H:%i:%s')";
default:
return "'".$x."'";
}
}
function createInsertSQL($table, $values, $nq){
if (!$table || $values=="") return; // upps
// $fields=$this->getFields($table);
$tblinfo=$this->getFieldsInfo($table);
//echo "X".($tblinfo[$fld][4])."Y";
//echo "P: $primary_key<br>";
//print_r(array_search("PRI",$tblinfo));
$primary_key="";
if(!$tblinfo) return ""; // Upps
foreach($tblinfo as $array){
if(array_intersect($array,array(4 => "PRI"))) $primary_key=$array[0];
}
$max=1;
$fields=array_intersect(array_keys($values),array_keys($tblinfo));
foreach($fields as $fld){
$size=count($values[$fld]);
$max=max($size, $max);
}
for ($size=0;$size<$max;$size++){
$partF="(";
$partV="VALUES(";
foreach ($fields as $fld){
$val=$values[$fld];
// if ($tblinfo[$fld][4]=="PRI") $primary_key=$fld;
if (strlen($val)){
if ($partF!="("){
$partF.=", ";
$partV.=", ";
}
// TM 2005.04.05
/*
if(is_array($val)){
$partV.="'".$val[$size]."'";
}else{
$partV.="'".$val."'";
}
*/
$partV.=$this->prepareVal($tblinfo[$fld][1], $val, $size);
if(!is_array($nq) || $nq[$fld]==false){
$fname= str_replace("\\", "\\\\", $fld);
$fname= str_replace("'", "\\'", $fld);
}
$partF.=$fld;
}
}
$partF.=")";
$partV.=")";
$sql[]= "INSERT INTO $table $partF $partV;";
}
return array("sql" => $sql, "primary_key" => $primary_key);
}
function insertData($sql){
if(is_array($sql)){
if(count($sql)==1) return $this->insertData($sql[0]);
foreach($sql as $bef) $rows[]=$this->insertData($bef);
}else{
$qid=$this->query($sql);
return mysql_insert_id();
}
return($rows);
}
function makeUpdateSQL($table, $values, $nq, $set="", $keys=""){
$sql="";
$wherekeys="";
$fields=$this->getFields($table);
if(!$fields) return ""; // Upps
if($keys) $wherekeys=$this->createWhereClause($keys,$values);
$part="";
$i=0;
$max=1;
while($i<$max){
$part="";
$where=$wherekeys;
# foreach(array_intersect($fields->name, $values) as $f){
foreach($fields as $f){
$val=$values[$f->name];
$max=max(count($val),$max);
if(is_array($val)) $val=$val[$i];
if($keys=="" && $f->primary_key){
if($where) $where.=" and ";
$where.=$f->name."='".$val."'";
}else{
if ((substr_count($where, " ".$f->name."='")==0 || substr_count($where, ".".$f->name."='")==0)){
if(array_key_exists($f->name, $values)){
if ($part!="") $part.=", ";
if(!is_array($nq) || $nq[$fname]==false){
// TM 2005.04.19
$part.=$f->name."=".$this->prepareVal($f->type, $val);
// TM Ende
}else{
$part.=$f->name."=$val";
}
}
}
}
}
/*
if($set){
// TM 2005.06.16
foreach(explode(",",$set) as $x){
echo "\n$x ".$values[$x]."<br>\n";
if($part && $x) $part.=", ";
if(array_key_exists(trim($x), $values)){
$part.="$x=".$values[$x];
}else{
$part.="$x";
}
}
}
$update= "UPDATE $table SET $part";
// TM Ende
*/
$update= "UPDATE $table SET $part";
if($part && $set) $update.=", ";
if($set) $update.=$set;
$sql[]="$update WHERE $where";
$i++;
}
return $sql;
}
function updateData($sql){
if(is_array($sql)){
foreach($sql as $bef) $rows[]=$this->updateData($bef);
}else{
$qid=$this->query($sql);
$rows=$this->affected_rows();
}
return($rows);
}
function createCopySQL($tabsrc, $tabdest, $const, $values, $where){
$ti=$this->getFields($tabdest,true);
$ts=$this->getFields($tabsrc,true);
$fds=$this->array2fields(array_intersect($ti["name"], $ts["name"]));
$fdi=$fds;
$vars=explode(",",$const);
foreach($vars as $f){
if(strlen(trim($f))){
$x=explode("=",$f);
$fdi=$x[0].", ".$fdi;
if($x[1]){
$fds=$x[1]." as ".$x[0].", ".$fds;
}else{
// quote!!! \ und '
// $temp=$values[$x[0]];
$fds="'".$values[$x[0]]."' as ".$x[0].", ".$fds;
}
}
}
$sql="INSERT INTO ".$tabdest." ($fdi) ";
$sql.=$this->createSelectSQL($tabsrc, $fds, $where);
return $sql;
}
function createDeleteSQL($table, $values, $keys=""){
if($keys){
$where=$keys;
}else{
$fields=$this->getFields($table, true);
if(!$fields) return ""; // Upps
$where=$this->createWhereClause($fields["name"],$values);
}
$sql="DELETE FROM $table WHERE $where";
return $sql;
}
function deleteData($sql){
if(is_array($sql)){
foreach($sql as $bef) $rows[]=$this->deleteData($bef);
}else{
$this->query($sql);
$rows=$this->affected_rows();
}
return $rows;
}
function syncTable($table, $values, $nq){
$fields=$this->getFields($table,true);
if(!$fields) return ""; // Upps
$primary_key= $fields["name"][array_search("1",$fields["primary_key"])];
$where=$this->createWhereClause($fields["name"],$values);
$res=$this->query("Select $primary_key from $table where $where");
if($res){
$row=$this->fetch_array($res);
if($row) return array("id" => $row[$primary_key], "primary_key" => $primary_key);
}
$arr=$this->createInsertSQL($table, $values, $nq);
$id=$this->insertData($arr['sql']);
return array("id" => $id, "primary_key" => $primary_key);
}
function getFields($sql, $fld2Array=false){
$sql=trim($sql);
if(substr_count($sql, " ")==0) $sql="SELECT * FROM $sql limit 1";
$res=$this->query($sql);
if($this->message!="") return;
if($fld2Array==false){
for ($i=0;$i<$this->num_fields($res);$i++){
$fld[]=$this->fetch_field($res);
}
return $fld;
}
if ($res==0) return;
$meta=array();
for ($i=0;$i<$this->num_fields($res);$i++){
$fld=$this->fetch_field($res);
$meta["table"][$i] = $fld->table;
$meta["name"][$i] = $fld->name;
$meta["type"][$i] = $fld->type;
$meta["blob"][$i] = $fld->blob;
$meta["max_length"][$i] = $fld->max_length;
$meta["not_null"][$i] = $fld->not_null;
$meta["numeric"][$i] = $fld->numeric;
$meta["unsigned"][$i] = $fld->unsigned;
$meta["primary_key"][$i] = $fld->primary_key;
$meta["multiple_key"][$i] = $fld->multiple_key;
$meta["unique_key_key"][$i] = $fld->unique_key;
$meta["zerofill"][$i] = $fld->zerofill;
$meta["length"][$i] = mysql_field_len($res, $i);
}
return $meta;
}
function getFieldsInfo($table,$field="") {
if(! $this->isTable($table)) return; // Upps
$result=mysql_query("SHOW FULL COLUMNS FROM `$table` LIKE '$field'");
if(mysql_num_rows($result)>0){
while($row=mysql_fetch_row($result)){
$fld[$row[0]]=$row;
}
}
return $fld;
}
function isTable($table) {
$result=mysql_query("SHOW FULL TABLES LIKE '$table'");
if($row=mysql_fetch_row($result)) return $row;
return;
}
}