Location: PHPKode > scripts > Mysql record set > mysql-record-set/mysql_recordset.class.php
<?php
/*
	Diese Klasse soll dem Benutzer die Möglichkeit geben
	effektiv und einfach in einem MySQL Datensatz zu navigieren
	
	!!!Bedingung ist eine einfache Select Abfrage am Anfang!!!
	
	Ziele:
	- Select Statements
	- Update Stateme nts
	- Insert Statements
	- Add und Delete
	 
	Funktionsweise wie bei ADODB Recordsets unter VB
	
	Vorgehen:
	1.)Parsen der SQL Syntax --> analysieren und Suche nach Tabellenname
	2.)über den Tabellenname wird dann nach den Feldern gesucht
	

*/

class recordset
{

	//Private variables DO NOT ACCESS
	
	##################################
	//Verbindungsvariablen
	
	var $server = "localhost";
	var $dbname = "";
	var $user = "";
	var $pass = "";
	
	###################################
	
	var $transaction = false;
	var $query;					//Query String
	var $connection;			//Connection Pointer
	var $result;				//Query Result
	var $errormsg;				//Fehlernachricht
	var $record_length;			//Länge des Recordsets
	var $record_pointer;			//Zeiger auf aktuellen Datensatz
	
	var $table_name;
	var $table_fields = array();
	var $table_condition;
	
	var $filter;
	
	//Public variables
	
	var $resultarray = array();
	var $fields = array();
	var $EOF;
	var $BOF;
	
	//Private Functions
	
		
	function db_connect($server, $user, $pass, $dbname)
	{
		$conn = @mysql_connect($server, $user, $pass) or die ("Fehler beim Öffnen der Datenbank");
		mysql_select_db($this->dbname, $conn) or die ("Could not select Database");
		return $conn;
	}
	
	function extract_table()
	{
		$tmpString = $this->query;
		$regexpnew = "select ([a-z0-9*, ]+)from ([a-z0-9\_]+)[ ]{0,1}(where ([a-z0-9]+[ ]{0,1}[=]{1}[ ]{0,1}[a-z0-9]+)[ ]{0,1}((and|or){1}[ ]{1}([a-z0-9]+[ ]{0,1}[=]{1}[ ]{0,1}[a-z0-9]+))*){0,1}";
		$regexp = "select ([a-z0-9, \.]+) from ([a-z0-9_]+)[ ]{0,1}(where ([a-z0-9, =]+)){0,1}";
		
		if (eregi($regexpnew, $tmpString, $regs))
		{
			//Hier werden nun die Tabelle und die Felder und der Filter-
			//wert zwischengespeichert um später die Insert und Del
			//statements zusammenzubauen
			
			$tablefields_not = trim($regs[1]);
			//Zerlegen der Felder
			
			if ($tablefields_not != "*")
			{
				$this->table_fields = explode(",", $tablefields_not);
				$lngFields = count($this->table_fields);
				
				for ($i = 0; $i < $lngFields ; $i++)
				{
					$this->table_fields[$i]= trim($this->table_fields[$i]);
				}
			}
			else
			{
				//Scheinabfrage starten
				//Metainformationen über Tabelle sammeln
				
							
				$resmeta = mysql_query($tmpString,$this->connection);
				
				$i = 0;
				while ($i < mysql_num_fields($resmeta)) {
					
									
					$meta = mysql_fetch_field($resmeta);
						
					$this->table_fields[$i] = $meta->name;
					
					$i++;
				}
				mysql_free_result($resmeta);
				
			}
			
			//Tabellenname
			$this->table_name = $regs[2];
			
			//Filterbedingung
			$this->filter = $regs[4];			
			
		}
		else
		{
			echo "no way";
		}
		
	}
		
	//Public Functions	
	
	function open($qry)
	{
		$this->query = $qry;
		$this->connection = $this->db_connect($server,$user,$pass,$dbname);
		$this->result = mysql_query($qry, $this->connection);
		
		if (!$this->result)
		{
			$this->errormsg = "Fehler beim Abfragen der Datenbank";
		}
		else
		{
			$this->resultarray = mysql_fetch_array($this->result,$this->connection);
		}
	}
	
	function close_cnn()
	{
		mysql_close($this->connection);		
	}
	
	function add_new()
	{
		//$query =  "INSERT INTO `tt_contact` ( `id` , `name` , `vorname` , `anschrift` , `plz` , `stadt` , `telefon` , `fax` , `mobil`, `email` , `userid` ) VALUES ( \"\", \"".$this->name."\", \"".$this->vorname."\", \"".$this->anschrift."\", \"".$this->plz."\", \"".$this->stadt."\", \"".$this->telefon."\", \"".$this->fax."\", \"".$this->mobil."\" , \"$this->email\" , \"".$userid."\" ) "; 
		//Hinzufügen eines Recordsets
		$this->extract_table();
		
		$lngRec = count($this->table_fields);
		$tmpQuery = "Insert ino $this->table_name (";
		
		//Resultarray wird vor dem Benutzen geleert um überreste
		//zu vermeiden
		
		for ($i = 0;$i < $lngRec; $i++)
		{
			$this->resultarray[$this->table_fields[$i]] = "";
		}
		
				
		$tmpQuery .= $this->table_fields[0];
		for ($i = 1;$i < $lngRec; $i++)
		{
			$tmpQuery .= ",".$this->table_fields[$i];
		}
		$tmpQuery.=") values (";
		
		$tmpQuery .= "\"".$this->resultarray[$this->table_fields[0]]."\"";
		
		for ($i = 1;$i < $lngRec; $i++)
		{
			$tmpQuery .= ",\"".$this->resultarray[$this->table_fields[$i]]."\"";
		}
		
		$tmpQuery .=")";
		
		echo $tmpQuery;	
	}
	
	function update()
	{
		//$query = "Update `tt_contact` set `name` = \"$this->name\", `vorname` =\"$this->vorname\", `anschrift` = \"$this->anschrift\", `plz`= \"$this->plz\", `stadt` = \"$this->stadt\", `telefon`=\"$this->telefon\", `fax`=\"$this->fax\", `mobil`=\"$this->mobil\", `email`=\"$this->email\" where `id`=\"$userid\"";
		//Update des Recordsets
		
		$this->extract_table();
		
		$lngRec = count($this->table_fields);
		$tmpQuery = "Update $this->table_name set ";
		
		//Resultarray wird vor dem Benutzen geleert um überreste
		//zu vermeiden
		
		for ($i = 0;$i < $lngRec; $i++)
		{
			$this->resultarray[$this->table_fields[$i]] = "";
		}
		
		//Die Reihenfolge der Bezeichnung der Felder ist mit den
		//Werten in resultarray
		$tmpQuery .= $this->table_fields[0]."= \"".$this->resultarray[$this->table_fields[0]]."\"";
		for ($i = 1;$i < $lngRec; $i++)
		{
			$tmpQuery .= ",".$this->table_fields[$i]."= \"".$this->resultarray[$this->table_fields[$i]]."\"";
		}
		
		$tmpQuery .= " where ". $this->filter;
			
		$res = mysql_query($tmpQuery,$this->connection);
			
	}
	
	function delete()
	{
		//$this->recordpointer liefert position
			
		$this->extract_table();
		$lngRec = count($this->table_fields);
		$tmpQuery = "Delete from ". $this->table_name." where ";
		
		
		$tmpQuery .= $this->table_fields[0]."=\"".$this->resultarray[$this->table_fields[0]]."\"";
		for ($i = 1;$i < $lngRec; $i++)
		{
			$tmpQuery .= "and ".$this->table_fields[$i]."= \"".$this->resultarray[$this->table_fields[$i]]."\"";
		}
		$res = mysql_query($tmpQuery,$this->connection);
		if (!$res)
		{
			//Fehlerbehandlung
		}
	}
	
	function filter($filterstring)
	{
		$this->query .= " where ".$filterstring;
		$this->result = mysql_query($this->query, $this->connection);
		
		if (!$this->result)
		{
			$this->errormsg = "Fehler beim Abfragen der Datenbank";
		}
		else
		{
			$this->resultarray = mysql_fetch_array($this->result,$this->connection);
		}
		
	}
	
	//Bewegungsfunktionen
	function move_next()
	{	
		$this->record_pointer += 1;
		$this->res_length();
		if (!($this->record_pointer > ($this->record_length-1)))
		{
			mysql_data_seek($this->result, $this->record_pointer);
			$this->resultarray = mysql_fetch_array($this->result,$this->connection);
			$this->EOF = false;
		}
		else
		{
			$this->record_pointer -= 1;
			$this->EOF = true;
		}
	}
	
	function move_prev()
	{
		$this->record_pointer -= 1;
		if (!($this->record_pointer < 0))
		{
			mysql_data_seek($this->result, $this->record_pointer);
			$this->resultarray = mysql_fetch_array($this->result,$this->connection);
			$this->BOF = false;
		}
		else
		{
			$this->record_pointer += 1;
			$this->BOF = true;
		}
	}
	
	function move_first()
	{
		$this->record_pointer = 0;
		mysql_data_seek($this->result, $this->record_pointer);
		$this->resultarray = mysql_fetch_array($this->result);
		$this->BOF = true;
	}
	
	function move_last()
	{
		$this->res_length();
		$this->record_pointer = $this->record_length-1;
		mysql_data_seek($this->result, $this->record_pointer);
		$this->resultarray = mysql_fetch_array($this->result);
		$this->EOF = true;
	}
	
	//Spezifische Funktionen
	function res_length()
	{
		$this->record_length = mysql_num_rows($this->result);
		return $this->record_length;
	}

}
?>
Return current item: Mysql record set