Location: PHPKode > projects > Template Parser Engine > cls/clsMySQL.php
<?
/*
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;
	}
}
Return current item: Template Parser Engine