query_sql.inc

PHPLOB注释详细版 使用模板技术的好帮手 PHP最有用的东东了

			$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 (!eregi("^[[:space:]]*WHERE",$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 (!eregi("^[[:space:]]*WHERE",$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 (!eregi("^[[:space:]]*WHERE",$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=eregi_Replace("^[[:space:]]*WHERE","",$where);
			$q.=" $andor $where";
		}
		if (!$q && $ckeck=='full') {
			echo "WARNING: where_plain_Clause(): WHERE-clause is empty!<BR>";
		}
		$q=ereg_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=eregi_Replace("^[[:space:]]*WHERE","",$where);
			$q.=" $andor $where";
		}
		if (!$q && $ckeck=='full') {
			echo "WARNING: where_Clause(): WHERE-clause is empty!<BR>";
		}
		$q=ereg_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]);
		}
	}


}

?>