query_sql.inc
No OneTemporary
Actions

Size

22 KB

Referenced Files

None

Subscribers

None

query_sql.inc
View Options

	<?php
	/*
	* Query generation for PHP3
	*
	* (C) Copyright 1998 Alex Aulbach
	* Credits: Gerard Hickey <Gerard.Hickey@nsc.com>
	* I took many ideas from his SQL.inc, thanks! :-)
	* The idea is of this class is based in November 1997,
	* it was a collection of functions for PHP/FI and mSQL.
	*
	* $Id: query_sql.inc,v 1.1.1.1 2004-04-29 10:18:10 adigeo Exp $
	*
	*/


	/*
	The Query-class is an enhancement to the db_*-classes. Currently It supports
	mySQL an Oracle but it is easy expandable. See down.

	It always needs the class DB_Sql!

	Query is fully upward compatible with DB_Sql. Example:

	OLD: NEW:

	require("db_mysql.inc"); require("db_mysql.inc");
	class hugobla extends DB_sql {} require("query_sql.inc");
	$db = new hugobla; class hugobla extends Query {}
	$db = new hugobla;

	It just provides a number of new functions.

	The Query-class is inteded to help you with creating selects, inserts,
	updates, where-clauses etc. Not just simple selects, but longer ones. It
	is indeed a great help for tables with more than 10-20 columns. But it can
	also be used for simple and small selects. The inbuilt checks help you
	programming saver.

	Here is an example:

	file: insert.php3
	------------------
	<?
	## gets data from my form and inserts it into db

	require("prepend.inc"); ## here the classes are loaded and configured
	$db = new hugobla;
	$db->query($db->insert_plain_Clause("mytable",$db->capture_vars("mytable"),ARRAY()));
	echo "Values inserted";

	?>

	file: insert2.php3
	-------------------
	<?
	## gets data from my form and inserts it into db with a new INDEX
	## myindex is defined as INT AUTO_INCREMENT PRIMARY KEY
	## (this is mysql, in oracle you have to define a trigger)
	## mytime is defined as DATETIME (DATE in oracle)

	require("prepend.inc"); ## here the classes are loaded and configured
	$db = new hugobla;
	$mytime="SYSDATE()";
	$db->query($db->insert_plain_Clause("mytable",$db->capture_vars("mytable"),
	ARRAY(myindex=>'NULL',mytime='func')));
	echo "Values inserted: "$db->last_insert_id();

	?>

	This example is nice, cause you see how easy it can be used. :-)

	The best thing is, that you don't have to care, if a field is a string or a
	number. The values are automatically converted into the right form. The type
	of the vars are read from the table. Stringvalues are encapsulated with '
	(configurable) and escaped (the code for this is currently not good - we are
	assuming, that PHP is configured not to encapsulate strings), int-
	and real-values are casted. It can handle "NULL"-values, function-statements
	or other values for insertion.

	You will make less errors.

	mySQL and most other DB's accept a a short form of insert-clause (INSERT
	INTO bla VALUES (...)). The Query-class will always make the longer form
	(INSERT INTO BLA (...) VALUES (...)). This makes it possible to use ALTER
	TABLE-commands without changing the program! E.g. changing a field in a
	table from NUMBER to VARCHAR(10) is fully encapsulated with this class.

	The class supports currently only mysql and oracle. I think the differences
	between the DBs are encapsulated enough in the db_* classes, so it is
	possible to handle the remaining small differences inside this class (this
	affects mainly the function sql2phptype() ) and it could be easiely extended
	(asuming, that the metadata()-function of the db_*-class works correctly).
	In this case it is important, that the $type-variable in the db_*.inc-class
	is correctly set.


	TODO-list:
	- A method to create querys like the LIMIT-clause in mySQL. For Oracle
	this works:

	select linenum, foo, bar
	from (select rownum as linenum, foo, bar from
	(select foo,bar from chaos order by bar) )
	where linenum between 11 and 20;

	- cleaner escaping, handling of \ and NUL (current code is bullshit)
	Some ideas?

	- Little Alta-Vista: add functions to create a where clause from a search
	string with rules to handle searching for more than one word.
	half automatic generating search patterns into a where-clause
	simple search engine support, simple support for semi full-text-search

	- automatic configurable manipulation of values, eg.
	triming of strings (delete whitespace at begin and end)
	also : TOUPPER, TOLOWER etc

	- SELECT-Clause (GROUP BY, HAVING, JOIN...)

	- make new functions insert_Clause() etc. which inserts only the
	fields they got from your call (the current will do "plain" insert)

	- where_Clause() - creating WHERE for select, update, exists etc.

	- serv all queries directly into db, return just the handle
	(hm, how to deal with the DB-handle?)

	- Return a 2-dimensional (Table-compatible) field from select (not so important)

	- The sql2phptype() can be used to help creating automatic input forms
	for a table

	DEPENDING:
	- db_mysql: new function metatabledata(), which returns the table-info from
	current selected table (will return multiple table-columns with a join)
	- db_mysql: perhaps the function sql2phptype() should be placed there?


	For developers of new db_*.inc: the function metadata() is very important
	for the correct work of this class. T

	*/

	class Query extends DB_Sql {

	## DONT FORGET to set the variables from DB_Sql! See there!

	## For debugging: if set to TRUE the Query is printed out,
	## before executing or returning
	var $Q_Debug=false;

	## set this to another value, if you want to hide it
	## in your HTML-code
	## example: var $Q_Debug_print="\n<!-- QDebug: %s -->\n";
	var $Q_Debug_print="<BR><B>QDebug:</B>\n%s\n<BR>\n";

	## Set this to TRUE if you only want to test, which query
	## will be created (ideal in combination with $Q_Debug)
	## This depends only functions which will make changes
	var $No_Write=false;

	## currently unused, this var is just an idea for later use.
	var $Backslash_N_is_NULL = false;

	## Metacache: see funtcion metadata_buffered()
	var $meta_cache_off = false;

	## This is the char, which will be replaced by \char.
	## PHP3 seems to be NOT binary-safe, so not quoting
	## for \0 (some ideas?)
	## we use ereg_replace to do the replacements.
	## with PHP3.0.6 you should replace this with str_replace()!
	## Quoting = 1 -> normal quoting using AddSlashes
	## 2 -> Replace \' to '' - needed eg. for sybase or oracle
	var $Quoting=1;
	var $Quotechar="'";

	var $StrLengTrunc = false;
	var $StrLengWarn = false;

	###########################
	## _QDebug
	function _QDebug ($str) {
	if ($this->Q_Debug) {
	printf($this->Q_Debug_print,$str);
	}
	}

	###########################
	## Set DB-Classname
	## This is only a 3rd posibility for setting the classname
	##
	function set_db_class ($db_class) {
	$this->Database=$db_class;
	}


	###########################
	## This function gets a datatype from the DB and returns an
	## equivalent datatype for PHP
	##
	## It returns also a subtype for a string
	##
	function sql2phptype ($type,$format='') {
	## $this->type is currently either "mysql" or "oracle"
	switch ($this->type) {
	case "mysql":
	switch ($type) {
	case "var string":
	case "string" :
	case "char" :
	return(Array("string",""));
	break;
	case "timestamp" :
	case "datetime" :
	case "date" :
	case "time" :
	return(Array("string","date"));
	break;
	case "blob" :
	return(Array("string","blob"));
	break;
	case "real" :
	return(Array("double",""));
	break;
	case "long" :
	default :
	return(Array("int",""));
	break;
	}
	break;
	case "oracle":
	switch ($type) {
	case "VARCHAR2" :
	case "VARCHAR" :
	case "CHAR" :
	return(Array("string",""));
	break;
	case "DATE" :
	return(Array("string","date"));
	break;
	case "BLOB" :
	case "CLOB" :
	case "BFILE" :
	case "RAW" :
	case "LONG" :
	case "LONG RAW" :
	return(Array("string","blob"));
	break;
	case "NUMBER" :
	if ($format) {
	return(Array("double",""));
	} else {
	return(Array("int",""));
	}
	break;
	default :
	$this->halt("sql2phptype(): Type is not a valid value: '$type'");
	break;
	}
	break;
	default:
	$this->halt("sql2phptype(): DB-type is not a valid value: ".$this->type);
	break;
	}
	}


	#######################################
	## This function returns a PHP-variable depending
	## on type. E.g. a string is returned as 'string'
	##
	## The parameters mean
	## $val - the value
	## There is a special case: If value is "NULL" and
	## the type is not "string" or subtype is empty, then
	## a value "NULL" is inserted. This let you just spare
	## a little bit work with $special
	##
	## $meta - the meta information for this field (that's what
	## is returned by metadata() from DB_sql-class, but just one
	## single row, e.g. $meta[2], not hole $meta).
	##
	## $special - Overwrites the type of the var if set. Some special
	## meanings:
	## "NULL" means, that this value must be set to "NULL"
	## "func" means, that $val should be untouched -
	## e.g. to insert the value of a SQL-function
	## [ INSERT INTO bla VALUES ( time=NOW() ) ]
	##

	function convert ($val,$meta,$special="") {
	list($type,$subtype)=$this->sql2phptype($meta["type"],$meta["format"]);
	if (($val == "NULL" &&
	($type != "string" \|\| $type[1] != "")) \|\|
	$special == "NULL") {
	$type="NULL";
	} else {
	if ($special) {
	$type=$special;
	if ($type!="func") {
	$val=$type;
	$type="func";
	}
	}
	}
	switch ($type) {
	case "string" :
	$val=(string)$val;
	if ($this->Quoting) {
	$val=AddSlashes($val);
	}
	if ($this->Quoting==2) {
	$val=str_replace("\\'","''",$val);
	}
	if ($subtype!='date' &&
	( $this->StrLengTrunc \|\| $this->StrLengWarn ) ) {
	if ( strlen($val) > $meta[len] ) {
	if ($this->StrLengWarn) {
	echo "<BR>STRING TOO LONG: '$meta[name]'";
	if ($this->StrLengTrunc) {
	echo ", TRUNCATING!";
	}
	}
	if ($this->StrLengTrunc) {
	$val=substr($val,0,$meta[len]);
	}
	}
	}
	$val=$this->Quotechar . $val . $this->Quotechar;
	break;
	case "int" :
	$val=(int)$val;
	break;
	case "double" :
	$val=(double)$val;
	break;
	case "NULL" :
	$val="NULL";
	break;
	case "func" :
	$val=(string)$val;
	break;
	default :
	echo "UNKNOWN TYPE: $type<BR>";
	}
	$this->_QDebug("Val: $meta[name] => $val<BR>");
	return(Array($val,$meta["name"]));
	}


	##
	## Function to generate a plain INSERT-Clause
	## ("plain" means, that every field in the table will
	## be set to a value, default is '' or 0 if nothing said
	## in $special)
	##
	## $fields is an assoc. Array consisting out of
	## table_name => value-pairs
	## $special is an assoc. field which will commit special
	## handling to convert() (See there)
	## $check could be "strong" or "soft".
	## "soft" won't tell you if there were to less
	## or too much fields (good for debuging)
	##
	## returns the insert clause. It's on you to modify it
	## and send it to your DB
	##
	function insert_plain_Clause ($table,$fields,$special,$check="soft") {
	$meta=$this->metadata_buffered($table);

	for ($i=0; $i < $meta["num_fields"]; $i++) {
	$j=$meta[$i]["name"];
	## NOT IMPLEMENTED: SEARCHING FOR $fields[$i]
	list($val["val"][$i],$val["name"][$i])=
	$this->convert($fields[$j],$meta[$i],$special[$j]);
	}
	if (Count($fields)!=Count($val["name"]) && $check=="strong") {
	echo "WARNING: insert_plain_clause(): There are not the same number of".
	" fields as in table for INSERT<BR>";
	}
	$q=sprintf("INSERT INTO %s (%s) VALUES (%s)",
	$table,join($val["name"],","),
	join($val["val"],","));
	$this->_QDebug($q);
	return($q);
	}

	# Replace, a special mySQL-function, same as INSERT
	function replace_plain_Clause ($table,$fields,$special,$check="soft") {
	$meta=$this->metadata_buffered($table);

	for ($i=0; $i < $meta["num_fields"]; $i++) {
	$j=$meta[$i]["name"];
	## NOT IMPLEMENTED: SEARCHING FOR $fields[$i]
	list($val["val"][$i],$val["name"][$i])=
	$this->convert($fields[$j],$meta[$i],$special[$j]);
	}
	if (Count($fields)!=Count($val["name"]) && $check=="strong") {
	echo "WARNING: replace_plain_Clause(): There are not the same number of".
	" fields as in table for INSERT<BR>";
	}
	$q=sprintf("REPLACE %s (%s) VALUES (%s)",
	$table,join($val["name"],","),
	join($val["val"],","));
	$this->_QDebug($q);
	return($q);
	}

	##
	## This function is nearly the same, as insert_plain_Clause,
	## The where parameter is new and should be generated by yourself
	## The check parameter knows 3 values: strong, soft and weak
	## weak enables you to sent a query without $where (enables you
	## to update the hole table)
	##
	function update_plain_Clause ($table,$fields,$special,$where,$check="soft") {
	$meta=$this->metadata_buffered($table);
	if (!$where && $check!="weak") {
	echo "ERROR: update_plain_Clause(): Parameter \$where is empty!<BR>";
	return(false);
	}

	for ($i=0; $i < $meta["num_fields"]; $i++) {
	$j=$meta[$i]["name"];
	## NOT IMPLEMENTED: SEARCHING FOR $fields[$i]
	list($val["val"][$i],$val["name"][$i])=
	$this->convert($fields[$j],$meta[$i],$special[$j]);
	#echo "V: ".$val["name"][$i]." : ". $val["val"][$i]." - ".$fields[$j]."<BR>";
	}
	if (Count($fields)!=Count($val["name"]) && $check=="strong") {
	echo "WARNING: update_plain_Clause(): There are not the same number of".
	" fields for INSERT<BR>";
	}
	for ($i=0 ; $i < Count ($val["name"]); $i++ ) {
	$s[]=$val["name"][$i]."=".$val["val"][$i];
	}
	$q=sprintf("UPDATE %s SET %s",$table,join($s,","));
	if ($where) {
	if (!preg_match("/^[[:space:]]*WHERE/i",$where)) {
	## insert "WHERE" if not set
	$where="WHERE $where";
	}
	$q.=" $where";
	}
	$this->_QDebug($q);
	return($q);
	}


	##
	## This function is nearly the same, as insert_Clause,
	## The where parameter is new and should be generated by yourself
	## The check parameter knows 3 values: strong, soft and weak
	## weak enables you to sent a query without $where (enables you
	## to update the hole table)
	##
	function update_Clause ($table,$fields,$special,$where,$check="soft") {
	$meta=$this->metadata_buffered($table);
	if (!$where && $check!="weak") {
	echo "ERROR: update_Clause(): Parameter \$where is empty!<BR>";
	return(false);
	}

	$i=0;
	for (reset($fields); list($key,$val)=each($fields); $i++) {
	if ( isset($meta[meta][$key]) ) {
	$j=$meta[meta][$key];
	list($v["val"][$i],$v["name"][$i])=
	$this->convert($val,$meta[$j],$special[$key]);
	}
	}
	for ($i=0 ; $i < Count ($v["name"]); $i++ ) {
	$s[]=$v["name"][$i]."=".$v["val"][$i];
	}
	if (Count($s)) {
	$q=sprintf("UPDATE %s SET %s",$table,join($s,","));
	if ($where) {
	if (!preg_match("/^[[:space:]]*WHERE/i",$where)) {
	## insert "WHERE" if not set
	$where="WHERE $where";
	}
	$q.=" $where";
	}
	}
	$this->_QDebug($q);
	return($q);
	}



	##
	## DELETE
	## deletes the selected Table
	## $check can be "soft" and "weak". Weak let's you delete the
	## hole table, if you want
	##
	function delete_Clause ($table,$where,$check="soft") {
	if (!$where && $check!="weak") {
	echo "ERROR: delete_Clause(): Parameter \$where is empty!<BR>";
	return(false);
	}

	$q=sprintf("DELETE FROM %s",$table);
	if ($where) {
	if (!preg_match("/^[[:space:]]*WHERE/i",$where)) {
	## insert "WHERE" if not set
	$where="WHERE $where";
	}
	$q.=" $where";
	}
	$this->_QDebug($q);
	return($q);
	}


	##
	## This function checks wether in table $table a
	## field $name is set with value $val
	##
	## it returns the number of found matches or zero
	##
	function exists ($table,$name,$val) {
	$meta=$this->metadata_buffered($table);
	$j=$meta["meta"][$name];
	list($k)=$this->convert($val,$meta[$j]);
	$q=sprintf("SELECT COUNT(%s) as c FROM %s WHERE %s=%s",
	$name,$table,$name,$k);
	$this->_QDebugs($q);
	$this->query($q);
	$this->next_record();
	return($this->f("c"));
	}

	##
	## This function creates a query like exists, but returns
	## an assoc array of the first found row, or false if nothing found
	## field $name is set with value $val
	##
	function getrow ($table,$name,$val) {
	$meta=$this->metadata_buffered($table);
	$j=$meta[meta][$name];
	list($k)=$this->convert($val,$meta[$j]);
	$q=sprintf("SELECT * FROM %s WHERE %s=%s",
	$table,$name,$k);
	$this->_QDebug($q);
	$this->query($q);
	if ($this->next_record()) {
	return($this->Record);
	} else {
	echo "<BR><B>WARNING:</B> getrow(): KEY: $name VAL: $val not found<BR>";
	return(false);
	}
	}



	##
	## WHERE-PLAIN-CLAUSE
	## Let you generate a WHERE-Clause with a Loop.
	##
	## Returns a where-clause beginning with " WHERE "
	##
	## This function generates a where-clause
	## $mywhere An array of simple expressions, eg. "firstname='Alex'"
	## $andor This string is printed bewtween the where-Array
	## default is 'AND'. It will handle an existing
	## $oldwhere correctly. You can set this to '', but then
	## the correct operator must be set by you in the where
	## $where an existing WHERE-clause. Default is empty.
	## $check if 'strong', it will stop, if an empty where-clause
	## will be returned, to avoid "full" selects. Default is soft
	##
	function where_plain_Clause ($mywhere,$andor='AND',$where='',$check="soft") {
	$meta=$this->metadata_buffered($table);
	$q='';

	for ($i=0; $i<Count($mywhere); $i++ ) {
	$q.=" $andor ".$mywhere[$i];
	}
	if ($where) {
	$where=preg_replace("/^[[:space:]]*WHERE/i","",$where);
	$q.=" $andor $where";
	}
	if (!$q && $ckeck=='full') {
	echo "WARNING: where_plain_Clause(): WHERE-clause is empty!<BR>";
	}
	$q=preg_replace("/^ $andor /"," WHERE ",$q);
	$this->_QDebug("where_plain_Clause(): $q");
	return($q);
	}

	##
	## ANOTHER-WHERE-CLAUSE
	##
	## This function generates a where-clause beginning with " WHERE "
	## Different form where_plain_Clause() this function is fully automated
	## It can handle NULL-Values (IS NULL) in a special manner:
	## if a value of $fields is 'NULL', we are looking, if the
	## operator is '='. In this case the operator is changed into "IS"
	## in any other case it is changed into "IS NOT".
	##
	## $tables table
	## $fields Assoc name=>value-fields
	## $op Assoc name=>operator. If empty, '=' is taken. it is printed
	## between the name/value pairs.
	## if $op is 'func' the name is taken as function name,
	## inside the brakets is the value.
	## $special Affects the calculation of value.
	## See INSERT_CLAUSE() for more about this.
	## $andor This string is printed bewtween the name/value-pairs,
	## default is 'AND'. If $where is set, it prints
	## it directly at the end before concatenating
	## $where an existing WHERE-clause. Default is empty.
	## $check if 'strong', it will stop, if an empty where-clause
	## will be returned, to avoid "full" selects. Default is soft
	##
	## Returns a where-clause beginning with " WHERE "
	##
	function where_Clause ($table,$fields,$op='',$special='',
	$andor='AND',$where='',$check="soft") {
	$meta=$this->metadata_buffered($table);
	$q='';

	if (!is_Array($op)) $op=ARRAY();
	if (!is_Array($special)) $op=ARRAY();
	if (!$andor) $andor='AND';

	$i=0;
	for (reset($fields); list($key,$val)=each($fields); $i++) {
	list($k[val],$k[name])=
	$this->convert($fields[$key],$meta[$meta[meta][$key]],$special[$key]);
	if (!$op[$key]) $o='='; else $o=$op[$key];
	if ('NULL'==strval($k[val])) {
	if ($o=='=' \|\| strtoupper($o)=='IS') $o = 'IS';
	else $o = 'IS NOT';
	}
	$q.=" $andor $k[name] $o $k[val]";
	}
	if ($where) {
	$where=preg_replace("/^[[:space:]]*WHERE/i","",$where);
	$q.=" $andor $where";
	}
	if (!$q && $ckeck=='full') {
	echo "WARNING: where_Clause(): WHERE-clause is empty!<BR>";
	}
	$q=preg_replace("/^ $andor /"," WHERE ",$q);
	$this->_QDebug("where_Clause(): $q");
	return($q);
	}


	##
	## capture-vars
	##
	## This function returns an assoc. Array consisting out of
	## name=>value-pairs needed by all the other functions. It reads
	## the name of the vars from the fields in $table and the values
	## from the $GLOBALS-var-field.
	## This has the sense, that you can name the variables in your
	## Input-Form exactly like the names in your table. This again
	## let make you less errors and less side effects.
	##
	## $table The name of the table
	##
	function capture_vars ($table) {
	$meta=$this->metadata_buffered($table);
	$r=Array();
	for ($i=0; $i < $meta["num_fields"]; $i++) {
	$j=$meta[$i]["name"];
	if (isset($GLOBALS[$j])) {
	$r[$j] = $GLOBALS[$j];
	$this->_QDebug("Found $j: $r[$j]");
	}
	}
	return($r);
	}

	##
	## all_changed_vars
	##
	## This function returns an assoc. Array consisting out of
	## name=>value-pairs which have a different value from the value
	## currently existing in your table. This is needed by
	## update_Clause(), cause with this, the update-query can be shortened
	## to the maximum needed max. Can also be used for much other things,
	## e.g. checking if something in your form has been changed (in this
	## case it returns an empty array)
	##
	## $table The name of the table
	## $fields Your assoc value field, which you want to check for
	## $where The where-clause, which matches your row.
	## This functions writes warnings, if your where-clause
	## returns more than one row or nothing
	##
	function all_changed_vars ($table,$fields,$where,$check='soft') {
	$meta=$this->metadata_buffered($table);

	$q1="SELECT * FROM $table $where";
	$this->query($q1);
	$r=Array();
	if ($this->next_record()) {
	for ($i=0; $i < $meta["num_fields"]; $i++) {
	$j=$meta[$i]["name"];
	if ($this->Record[$j]!=$fields[$j]) {
	$r[$j]=$fields[$j];
	$this->_QDebug("Changed $j: ".$fields[$j]." -> ".$this->Record[$j]);
	}
	}
	if ($this->next_record()) {
	echo "ERROR: all_changed_vars(): Found more than one row!<BR>";
	}
	} elseif ($check!='soft') {
	echo "<BR><B>WARNING:</B> all_changed_vars(): No row found!<BR>";
	}
	$this->_QDebug("WHERE: $where");
	return($r);
	}

	##
	## metadata_buffered (internal)
	##
	## This function calls metadata() if it won't find the buffer,
	## this speeds the Query-class strongly up, cause it is needed in nearly
	## every function
	##
	## $table the name of the table
	##
	## Returns the metadata-field
	##
	function metadata_buffered($table) {
	if ( !is_Array($this->meta_buf[$table]) \|\| $this->meta_cache_off) {
	return ($this->meta_buf[$table]=$this->metadata($table,true));
	} else {
	return ($this->meta_buf[$table]);
	}
	}


	}

	?>

File Metadata

Mime Type: text/x-php
Expires: Sat, Nov 23, 2:19 PM (22 h, 51 m)
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 3409280
Default Alt Text: query_sql.inc (22 KB)

query_sql.incNo OneTemporaryActions

query_sql.incView Options

File Metadata

Event Timeline

query_sql.inc
No OneTemporary
Actions

query_sql.inc
View Options