 |
 |
 |
 |
|
|
Date: 03/24/00
- Next message: ssilk: "[PHPLIB-DEV] cvs commit"
- Previous message: Alexander Aulbach: "Re: [PHPLIB-DEV] efficiency of table class"
- Next in thread: ssilk: "[PHPLIB-DEV] cvs commit"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
From: ssilk
Date: Sat Mar 25 02:38:23 2000
Modified files:
php-lib/php/db_mysql.inc
php-lib/php/query_sql.inc
Log message:
A groundup-reengeneering and cleaning of the code in query_sql.inc
Now added a function IU_Clause(), which returns either an insert or update
Query, depending on if the unique-identifier exists in the table or not.
With this it is possible to make a complete formular-data-into-a-table-
program in one or two lines of PHP-code.
Index: php-lib/php/db_mysql.inc
diff -u php-lib/php/db_mysql.inc:1.28 php-lib/php/db_mysql.inc:1.29
--- php-lib/php/db_mysql.inc:1.28 Wed Feb 2 03:32:08 2000
+++ php-lib/php/db_mysql.inc Sat Mar 25 02:37:52 2000
@@ -5,7 +5,7 @@
* Copyright (c) 1998,1999 NetUSE GmbH
* Boris Erdmann, Kristian Koehntopp
*
- * $Id: db_mysql.inc,v 1.28 2000/02/02 02:32:08 ssilk Exp $
+ * $Id: db_mysql.inc,v 1.29 2000/03/25 01:37:52 ssilk Exp $
*
*/
@@ -298,6 +298,7 @@
* [0]["php_subtype"] the subtype of PHP-type
* ["meta"][field name] index-num of field named "field name"
* This could used, if you have the name, but no index-num - very fast
+ * [unique] = field names which have an unique key, separated by space
*/
// if no $table specified, assume that we are working with a query
@@ -329,6 +330,7 @@
$res[$i]["flags"] = <email protected> ($id, $i);
}
} else { // full
+ $uniq=ARRAY();
$res["num_fields"]= $count;
for ($i=0; $i<$count; $i++) {
@@ -366,7 +368,11 @@
$res[$i][php_subtype]="";
break;
}
+ if ( ereg("(unique_key|primary_key)",$res[$i]["flags"]) ) {
+ $uniq[]=$res[$i]["name"];
+ }
}
+ $res["unique"]=join(" ",$uniq);
}
// free the result only if we were called on a table
Index: php-lib/php/query_sql.inc
diff -u php-lib/php/query_sql.inc:1.14 php-lib/php/query_sql.inc:1.15
--- php-lib/php/query_sql.inc:1.14 Mon Mar 20 23:00:00 2000
+++ php-lib/php/query_sql.inc Sat Mar 25 02:37:52 2000
@@ -2,14 +2,23 @@
/*
* Query generation for PHP3
*
- * (C) Copyright 1998 Alex Aulbach
+ * (C) Copyright 1998-2000 Alex Aulbach
+ * if you take parts out of this class, please send
+ * a cookie-file to me - I'm collection cookies:
+ * ssilk <email protected> :-)
+ *
* Credits: Gerard Hickey <Gerard.Hickey <email protected>>
* I took many ideas from his SQL.inc, thanks! :-)
+ * Also Credits to Carmelo Guarneri
+ * I reimplemented his db_usql.inc here
+ * hope he likes it, his functions
+ * are commented with // instead of ##
+ *
* The idea (and also the name, sorry for this) 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.14 2000/03/20 22:00:00 ssilk Exp $
+ * $Id: query_sql.inc,v 1.15 2000/03/25 01:37:52 ssilk Exp $
*
*/
@@ -17,6 +26,79 @@
## Query-class is ALWAYS an extension to the DB_Sql-class!
## See PHPLib-documention to use it!
+/*
+Some general information about this class:
+
+1.
+Currently we cannot guarantee, that the result of a former
+query()-call is not overwritten. That means, that this for example
+dosn't work:
+ $db->query("select * from bla");
+ while ($db->next_record()) {
+ $iu=$db->iu_clause("blubb",$db->Record);
+ }
+This is cause iu_clause uses query() for itself, because currently
+Query-class cannot instanciate itself. In practice this is no problem
+at all, cause by normal you need for the above constructs always
+2 instantiations of the class.
+
+2.
+Some function do have those 3 params:
+ function some_clause ($table,$fields,$special,.....)
+$table is the name of the table in your DB you want to create a clause for.
+$fields is an associative array like
+ $fields=ARRAY (
+ first_name=>'Alexander',
+ last_name =>'Aulbach',
+ email =>'ssilk <email protected>',
+ bla =>'NULL')
+Every unset value is handled by normal as NULL or "Don't Change"
+(except the _plain_-functions or bugs :) !.
+
+But there are some other exceptions: if the field "bla" is defined
+as an integer it is the same as unseting "bla". But if it is a string
+the string "NULL" is taken (because otherwise you cannot insert this value
+to any of your tables).
+
+The $special array was invented cause of having more control about this.
+Seting
+ $special=ARRAY(bla => 'NULL')
+will force bla the value NULL, not the string "NULL". This depends also
+the update-clauses, setting $special to 'NULL' will force an update for
+this field to the value NULL, instead of leaving it untouched if you don't
+set the field.
+
+[thinking about a value 'NOTOUCH' for update-clauses]
+
+You can also force a value to be taken unchanged, no matter if it is
+a string or integer or whatever. For example:
+ $fields=ARRAY(bla=>'SYSDATE()',blubb=>'hugobla');
+ $special=ARRAY(bla=>'func');
+ echo $db->insert_plain_clause("test",$fields,$special);
+outputs
+ INSERT INTO test (bla,blubb) VALUES (SYSDATE(),'hugobla')
+Any other value than "NULL" or "function" must be a data-type. Query
+knows currently 3 types, namely: "string", "int" and "double". Setting
+the special-array to those values means to force the conversion to
+this data-type.
+
+3.
+The $where parameter for EVERY function call needs not to have an
+including "WHERE", you can just begin with "a=1 and b='x' ...".
+
+4.
+The $params-parameter was for most function formerly the $check-parameter.
+To be upward compatible and for more comfort we made this agreement:
+If $params is a string, this means, that it is the $check-value, if it is an
+array you need to make it associative, e.g.:
+ $params=ARRAY(check=>'weak',forceinsert=>true)
+
+5.
+We will NOT EXPLAIN this any more in the rest of the program. :)
+
+*/
+
+
class Query extends DB_Sql {
## DONT FORGET to set the variables from DB_Sql! See there!
@@ -71,6 +153,10 @@
## another meaningful value could be '%'
var $fireball_replace_char = "_";
+
+ ## PRIVATE FUNCTIONS ##
+
+
###########################
## _QDebug
function _QDebug ($str) {
@@ -122,9 +208,9 @@
function convert ($val,$meta,$special="") {
list($type,$subtype)=$this->sql2phptype($meta);
- if (($val == "NULL" &&
+ if ((strtoupper($val) == "NULL" &&
($type != "string" || $type[1] != "")) ||
- $special == "NULL") {
+ strtoupper($special) == "NULL") {
$type="NULL";
} else {
if ($special) {
@@ -142,7 +228,7 @@
$val='NULL';
$subtype='NULL';
}
- if ($val=='NULL' && $this->NULLIsSpace) {
+ if (strtoupper($val)=='NULL' && $this->NULLIsSpace) {
$val=' ';
$subtype='';
}
@@ -189,190 +275,507 @@
return(Array($val,$meta["name"]));
}
+ ##
+ ## Check params
+ ## internal function, which returns an array of splitted parameters
+ ##
+ function chkprms ($params) {
+ if (!is_Array($params)) {
+ $check=strtolower($params);
+ $params=ARRAY(check=>$check);
+ } else {
+ $check=strtolower($params[check]);
+ }
+ if (! ereg("strong|soft|weak",$check) ) {
+ $this->halt("chkprms(): Parameter \$check is not valid: '$check'");
+ }
+ return(ARRAY($params,$check));
+ }
##
- ## 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)
+ ## This functions checks out the situation, that $p1 or $p2 is
+ ## the special-array and $p2 or $p1 is a where-Parameter
+ ## in every possible sitation ($p1 not set, $p2 not set etc)
+ ## and it returns an array ($special,$where)
+ ## perhaps this could be done better, but I made this in 2 minutes.
+ ##
+ function special_or_where ($p1,$p2='') {
+ $special=ARRAY();
+ $where='';
+ if (empty($p1)) {
+ if (is_Array($p2)) {
+ $special=$p2;
+ } elseif (strlen($p2) > 2) { # simplest possible where clause is a=1
+ $where=$p2;
+ }
+ } elseif (empty($p2)) {
+ if (is_Array($p1)) {
+ $special=$p1;
+ } elseif (strlen($p1) > 2) {
+ $where=$p1;
+ }
+ } elseif (empty($p1) && empty($p2)) {
+ # do nothing
+ } else {
+ if (is_Array($p1) && (strlen($p1) > 2)) {
+ $special=$p1;
+ $where=$p2;
+ } elseif (is_Array($p2) && (strlen($p2) > 2)) {
+ $special=$p2;
+ $where=$p1;
+ } else {
+ $this->halt("special_or_where(): parameters in a unkown form.");
+ }
+ }
+ return(ARRAY($special,$where));
+ }
+
+ ##
+ ## uniform where-clause
+ ## brings the where clause form
+ ## " WHERE blablablabla"; which is used everywhere in Query
+ ##
+ function uniform_where ($where) {
+ if (!empty($where)) {
+ $where=trim($where);
+ if (!eregi("^WHERE[[:space:]]",$where)) {
+ ## insert "WHERE" if not set
+ $where="WHERE $where";
+ }
+ $where=" $where";
+ }
+ return($where);
+ }
+
+ ##
+ ## uniforms $fields with $special
+ ## returns an array of values and (converted) names
+ ## Perhaps we can use $special for renaming those names?
+ ##
+ function uniform_vars (&$meta,&$fields,&$special) {
+ for (reset($fields); list($key,$val)=each($fields); ) {
+ if ( isset($meta[meta][$key]) ) {
+ $j=$meta[meta][$key];
+ list($vals[],$names[])=
+ $this->convert($val,$meta[$j],$special[$key]);
+ }
+ }
+ for (reset($special); list($key,$val)=each($special); ) {
+ if ( isset($meta[meta][$key]) && empty($fields[$key]) ) {
+ $j=$meta[meta][$key];
+ list($vals[],$names[])=
+ $this->convert($fields[$key],$meta[$j],$val);
+ }
+ }
+ return(ARRAY($vals,$names));
+ }
+
+ ##
+ ## 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
+ ## many functions
+ ##
+ ## $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]);
+ }
+ }
+
+
+
+ ## BEGIN OF OPEN FUNCTIONS ##
+
+
+
+ ##
+ ## Function to generate an INSERT-Clause
##
## $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)
+ ## $params
+ ## currently not used.
##
## 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") {
+ function insert_Clause ($table,$fields,$special='',$params="soft") {
if (''==$special) $special=ARRAY();
+ list($params,$check)=$this->chkprms($params);
+
$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>";
- }
+ list($vals,$names)=$this->uniform_vars($meta,$fields,$special);
+
$q=sprintf("INSERT INTO %s (%s) VALUES (%s)",
- $table,join($val["name"],","),
- join($val["val"],","));
+ $table,join($names,", "),
+ join($vals,", "));
$this->_QDebug($q);
return($q);
}
- # Replace, a special mySQL-function, same as INSERT
- function replace_plain_Clause ($table,$fields,$special='',$check="soft") {
+ ##
+ ## 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)
+ ##
+ ## Works like insert_clause() with the difference, that
+ ## it will set ALL values of a row.
+ ##
+ ## $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)
+ ## $params Assoc Array
+ ## check => "strong" | "soft"
+ ## "strong" tells you if there were to less
+ ## or too much fields (good for debuging)
+ ## nullisnull => true
+ ## instead of inserting '' or 0 if the field is not set
+ ## it will write NULL
+ ## if you don't want either this or the other use insert_Clause()
+ ##
+ ## if $params is a string it is taken as $check (see default).
+ ##
+ ## returns an insert clause. It's on you to modify it
+ ## and send it to your DB
+ ##
+ function insert_plain_Clause ($table,$fields,$special='',$params="soft") {
if (''==$special) $special=ARRAY();
+ list($params,$check)=$this->chkprms($params);
+
$meta=$this->metadata_buffered($table);
+ if (Count($fields)!=Count($val["name"]) && $check=="strong") {
+ $this->halt("WARNING: insert_plain_clause(): There are not the same number of".
+ " fields as in table for INSERT");
+ }
+
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>";
+ if (!isset($fields[$j])) {
+ if ($param[nullisnull]) {
+ $special[$j]='NULL';
+ } else {
+ $fields[$j]='';
+ }
+ }
}
- $q=sprintf("REPLACE %s (%s) VALUES (%s)",
- $table,join($val["name"],","),
- join($val["val"],","));
- $this->_QDebug($q);
- return($q);
+ return($this->insert_Clause($table,$fields,$special,$params));
}
##
- ## This function is nearly the same, as insert_plain_Clause. Some
- ## difference:
+ ## This function is nearly the same, as insert_Clause, but some
+ ## differences:
## $special: If $special is a string and $where is empty,
## it takes it as $where-Parameter
- ## The where parameter is new and should be generated by yourself
+ ## The where parameter 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") {
- if (''==$special) $special=ARRAY();
- if (is_String($special) && ''==$where) {
- $where=$special;
- $special=ARRAY();
- }
+ function update_Clause ($table,$fields,
+ $special_or_where='',$where_or_special='',$params="soft") {
+ list($special,$where)=$this->special_or_where($where_or_special,$special_or_where);
+ list($params,$check)=$this->chkprms($params);
+
$meta=$this->metadata_buffered($table);
+
if (!$where && $check!="weak") {
- echo "ERROR: update_plain_Clause(): Parameter \$where is empty!<BR>";
+ echo "ERROR: update_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>";
+ list($vals,$names)=$this->uniform_vars($meta,$fields,$special);
+
+ for ($i=0 ; $i < Count ($names); $i++ ) {
+ $s[]=$names[$i]."=".$vals[$i];
}
- if (Count($fields)!=Count($val["name"]) && $check=="strong") {
- echo "WARNING: update_plain_Clause(): There are not the same number of".
- " fields for INSERT<BR>";
+ if (Count($s)) {
+ $q=sprintf("UPDATE %s SET %s%s",$table,join($s,", "),
+ $this->uniform_where($where));
}
- for ($i=0 ; $i < Count ($val["name"]); $i++ ) {
- $s[]=$val["name"][$i]."=".$val["val"][$i];
+ $this->_QDebug($q);
+ return($q);
+ }
+
+ ##
+ ## This function is nearly the same, as insert_plain_Clause. Some
+ ## difference:
+ ## $special_or_where: If $special is a string and $where is empty,
+ ## it takes it as $where-Parameter
+ ## $where: should be generated by yourself, should be in the form
+ ## "WHERE ...." or "...."
+ ## $params: Assoc Array
+ ## check => "strong" || "soft" || "weak"
+ ## "weak" enables you to send a query without $where-parameter
+ ## nullisnull => true
+ ## instead of updateing '' or 0 if the field is not set
+ ## it will write NULL
+ ## if you don't want either this or the other use update_Clause()
+ ##
+ ## if $params is a string it is taken as $check (see default).
+ ##
+ function update_plain_Clause ($table,$fields,
+ $special_or_where='',$where_or_special='',$params="soft") {
+ list($special,$where)=special_or_where($where_or_special,$special_or_where);
+ list($params,$check)=$this->chkprms($params);
+
+ $meta=$this->metadata_buffered($table);
+ if ((Count($fields)!=$meta["num_fields"]) && $check=="strong") {
+ echo "WARNING: update_plain_Clause(): This is not the correct number of".
+ " fields for UPDATE<BR>";
}
- $q=sprintf("UPDATE %s SET %s",$table,join($s,","));
- if ($where) {
- if (!eregi("^[[:space:]]*WHERE",$where)) {
- ## insert "WHERE" if not set
- $where="WHERE $where";
+
+ for ($i=0; $i < $meta["num_fields"]; $i++) {
+ $j=$meta[$i]["name"];
+ if (!isset($fields[$j])) {
+ if ($param[nullisnull]) {
+ $special[$j]='NULL';
+ } else {
+ $fields[$j]='';
+ }
}
- $q.=" $where";
}
- $this->_QDebug($q);
- return($q);
+ return($this->update_Clause($table,$fields,$special,$where,$params));
}
##
- ## This function is nearly the same, as insert_Clause, but some
- ## differences:
- ## $special: If $special is a string and $where is empty,
- ## it takes it as $where-Parameter
- ## The where parameter 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") {
- if (''==$special) $special=ARRAY();
- if (is_String($special) && ''==$where) {
- $where=$special;
- $special=ARRAY();
+ ## DELETE
+ ## deletes fields from the selected Table
+ ## $table: The table
+ ## $where_or_wfields: Either a where-clause-string or an assoc array
+ ## of fields. In this case the function tries to find an
+ ## unique-identifier of those fields and creates only
+ ## an where-clause for those unique-fields, otherwise
+ ## for all the given fields (takes where_Clause() for this)
+ ## $params: Assoc Array
+ ## check => "strong" | "soft" | "weak"
+ ## "weak" let's you delete the hole table, if you want
+ ## (so $where_or_wfields can be empty)
+ ## "strong" checks if $where_or_wfields describes minimum
+ ## one unique-identifier
+ ## if $params is a string it is taken as $check (see default).
+ ##
+ function delete_Clause ($table,$where_or_wfields='',$params="soft") {
+ list($fields,$where)=$this->special_or_where($where_or_wfields);
+ list($params,$check)=$this->chkprms($params);
+
+ if (! empty($where)) {
+ ## then fields must hold some values
+ $meta=$this->metadata_buffered($table);
+ $uniq=split(" ",$meta[unique]);
+ $where=ARRAY();
+ for ( reset($uniq) ; list(,$name)=each($uniq) ; ) {
+ if (isset($fields[$name]))
+ $where[$name]=$fields[$name];
+ }
+ if ($check=="strong" && !count($where)) {
+ echo "ERROR: delete_Clause(): A unique identifier dosn't exist.<br>";
+ echo "Turn of check='strong' or call it with wfields-params.";
+ return(false);
+ } elseif (count($where)) {
+ $fields=$where;
+ }
+ $where=$this->where_clause($table,$fields);
}
- $meta=$this->metadata_buffered($table);
+
if (!$where && $check!="weak") {
- echo "ERROR: update_Clause(): Parameter \$where is empty!<BR>";
+ echo "ERROR: delete_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]);
+ $q=sprintf("DELETE FROM %s%s",$table,
+ $this->uniform_where($where));
+ $this->_QDebug($q);
+ return($q);
+ }
+
+
+ ##
+ ## 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". (won't match
+ ## 'like' etc. but this function is not thought for such operations!)
+ ##
+ ## $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)) $special=ARRAY();
+ if (!$andor) $andor='AND';
+ if (!is_Array($fields)) $fields=ARRAY();
+
+ list($vals,$names)=$this->uniform_vars($meta,$fields,$special);
+
+ for ($i=0; $i < Count($names) ; $i++) {
+ if (empty($op[$names[$i]])) $o='='; else $o=$op[$names[$i]];
+ if ('NULL'==$vals[$i]) {
+ if ($o=='=' || strtoupper($o)=='IS') $o = 'IS';
+ else $o = 'IS NOT';
}
+ $q.=" $andor $names[$i] $o $vals[$i]";
}
- for ($i=0 ; $i < Count ($v["name"]); $i++ ) {
- $s[]=$v["name"][$i]."=".$v["val"][$i];
+ if ($where) {
+ $where=eregi_Replace("^[[:space:]]*WHERE","",$where);
+ $q.=" $andor $where";
}
- 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";
- }
+ if (!$q && $ckeck=='full') {
+ echo "WARNING: where_Clause(): WHERE-clause is empty!<BR>";
}
- $this->_QDebug($q);
+ $q=$this->uniform_where(ereg_Replace("^ $andor ","",$q));
+ $this->_QDebug("where_Clause(): $q");
+ return($q);
+ }
+
+ ##
+ ## SIMP(LE) WHERE-CLAUSE
+ ## Formerly known as 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
+ ## $where-param correctly.
+ ## $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 simp_where_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 && $check=='strong') {
+ $this->halt("where_plain_Clause(): WHERE-clause is empty!");
+ }
+ $q=$this->uniform_where(ereg_Replace("^ $andor ","",$q));
+ $this->_QDebug("where_plain_Clause(): $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
+ ## IU - INSERT or update UPDATE query for
+ ## an unique row in a table
##
- function delete_Clause ($table,$where='',$check="soft") {
- if (!$where && $check!="weak") {
- echo "ERROR: delete_Clause(): Parameter \$where is empty!<BR>";
+ ## This function inserts or update queries, dependig
+ ## if the unique or primary key was found in the table or not.
+ ## IMPORTANT: Cause it is currently not possible to create a Query
+ ## class in the Query class ($classname is not set)
+ ## we must overwrite this class!
+ ## The result before calling this function is not saved!
+ ## Depending on $params, the result is executed or printed out.
+ ## It returns either "U" or "I" or false if an error occours.
+ ## $table: The table
+ ## $fields: Assoc array of values to insert or update
+ ## $special: Assoc array for special handling of $fields
+ ## $params: Assoc array or a string
+ ## check => 'soft' | 'strong'
+ ## the parameter is taken over to
+ ## forceinsert => true
+ function IU_Clause ($table,$fields,$special='',$params='soft') {
+ if (''==$special) $special=ARRAY();
+ list($params,$check)=$this->chkprms($params);
+
+ $meta=$this->metadata_buffered($table);
+
+ if ( empty($meta[unique]) ) {
+ echo "ERROR: IU_Clause(): Table '$table' has no unique".
+ " identifier or primary key!<br>";
+ return(false);
+ }
+ $uniq=split(" ",$meta[unique]);
+ $where=ARRAY();
+ for ( reset($uniq) ; list(,$name)=each($uniq) ; ) {
+ if (isset($meta[meta][$name])) {
+ $tmp= $this->convert($fields[$name],
+ $meta[$meta[meta][$name]],
+ $special[$name]);
+ if ( $tmp[0] !="NULL" ) {
+ $where[$name]=$fields[$name];
+ }
+ } else {
+ echo "ERROR: IU_Clause(): This unique index dosn't exist: '$name'<br>";
+ return(false);
+ }
+ }
+ $whereclause=$this->where_Clause($table,$where,'',$special);
+ if (!$whereclause) {
+ echo "ERROR: IU_Clause(): The result of generating a where-clause to this uniqe identifers is empty:<br>";
+ for ( reset($uniq); list(,$name)=each($uniq) ; ) {
+ echo "[$name]=>'$fields[$name]'<br>";
+ }
+ echo "<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->query("select count(*) from $table $whereclause");
+ $this->next_record();
+ if ($this->Record[0] > 1) {
+ ## this error cannot happen by normal, but perhaps something
+ ## goes wrong with generating the where-clause?
+ echo "ERROR: IU_Clause(): This index isn't unique: '$whereclause'<BR>";
+ return(false);
+ } elseif (!$params[forceinsert] && $this->Record[0] == 1) {
+ return(
+ $this->update_Clause($table,$fields,$special,$whereclause,$check) );
+ } else {
+ return(
+ $this->insert_Clause($table,$fields,$special,$check) );
}
- $this->_QDebug($q);
- return($q);
}
+
+
##
## This function checks wether in table $table a
## field $name is set with value $val
@@ -390,8 +793,6 @@
$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
@@ -411,8 +812,6 @@
return(false);
}
}
-
- ##
## This function is like getrow(), and returns
## an assoc array of the first found row, or false if nothing found
## The where-parameter is a where-clause - could be generated by
@@ -437,100 +836,6 @@
- ##
- ## 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". (won't match
- ## 'like' etc. but this function is not thought for such operations!)
- ##
- ## $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)) $special=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);
- }
##
@@ -559,6 +864,76 @@
return($r);
}
+ //--------------------------------------------------------------
+ // this function can be used to export all the columns of
+ // a record into *global* variables.
+ // So it is the oposite function to capture_vars()
+ // It should be used after a call to next_record().
+ //
+ // $param is an optional assoc-array-parameter:
+ // to_upper=>true - uppercases all vars, otherwise lowercase
+ //--------------------------------------------------------------
+ function dispose_vars($params='') {
+ if (!is_array($params)) $params=ARRAY();
+ for ( reset($this->Record); list($key,$val)=each($this->Record); ) :
+ if (ereg("[A-Za-z_][A-Za-z0-9_]*", $key)) {
+ if ($params[to_upper]) {
+ $field_name = strtoupper($key);
+ } else {
+ # Default behavior, cause phplib
+ # uses always lowercase
+ $field_name = strtolower($key);
+ }
+ $this->_QDebug("'$field_name' => '$val'");
+ $GLOBALS[$field_name]=$val;
+ } else {
+ $this->_QDebug("Not matching: '$key'");
+ }
+ endfor;
+ }
+
+ // Alias to dispose_vars for compatibility to db_usql
+ function import_record_vars($params='') {
+ $params[to_upper]=true;
+ $this->dispose_vars($params);
+ }
+
+ // Alias to dispose_vars, but next_record() is called before
+ function dispose_next_record($params='') {
+ $this->next_record();
+ $this->dispose_vars($params);
+ }
+ //--------------------------------------------------------------
+ // this function can be used to export all the columns of
+ // an assoc array *global* variables.
+ // The names of the indizes are the name of the global var
+ // So it is nearly the same function as dispose_vars()
+ // and another oposite function to capture_vars()
+ //
+ // $param is an optional assoc-array-parameter:
+ // to_upper=>true - uppercases all vars, otherwise lowercase
+ //--------------------------------------------------------------
+ function dispose_asoc_array($array,$params='') {
+ if (!is_array($params)) $params=ARRAY();
+ for ( reset($array); list($key,$val)=each($array); ) :
+ if (ereg("[A-Za-z_][A-Za-z0-9_]*", $key)) {
+ if ($params[to_upper]) {
+ $field_name = strtoupper($key);
+ } else {
+ # Default behavior, cause phplib
+ # uses always lowercase
+ $field_name = strtolower($key);
+ }
+ $this->_QDebug("'$field_name' => '$val'");
+ $GLOBALS[$field_name]=$val;
+ } else {
+ $this->_QDebug("Not matching or empty: '$key'");
+ }
+ endfor;
+ }
+
+
+
##
## all_changed_vars
##
@@ -566,10 +941,12 @@
## 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,
+ ## to the maximum needed changes. 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)
##
+ ## BUGS: Cannot handle NULL values correctly
+ ##
## $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.
@@ -582,6 +959,7 @@
$q1="SELECT * FROM $table $where";
$this->query($q1);
$r=Array();
+ ## TODO: uniform_vars missing here
if ($this->next_record()) {
for ($i=0; $i < $meta["num_fields"]; $i++) {
$j=$meta[$i]["name"];
@@ -600,70 +978,10 @@
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]);
- }
- }
-
-
// The following part is included from db_usql
// This is the better place...
- //--------------------------------------------------------------
- // this function can be used to export all the columns of
- // a record into *global* variables.
- // So it is the oposite function to capture_vars()
- // It should be used after a call to next_record().
- //
- // $param is an optional assoc-array-parameter:
- // to_upper=>true - uppercases all vars, otherwise lowercase
- //--------------------------------------------------------------
- function dispose_vars($params='') {
- if (!is_array($params)) $params=ARRAY();
- for ( reset($this->Record); list($key,$val)=each($this->Record); ) :
- if (ereg("[A-Za-z_][A-Za-z0-9_]*", $key)) {
- if ($params[to_upper]) {
- $field_name = strtoupper($key);
- } else {
- # Default behavior, cause phplib
- # uses always lowercase
- $field_name = strtolower($key);
- }
- $this->_QDebug("'$field_name' => '$val'");
- $GLOBALS[$field_name]=$val;
- } else {
- $this->_QDebug("Not matching: '$key'");
- }
- endfor;
- }
-
- // Alias to dispose_vars for compatibility to db_usql
- function import_record_vars($params='') {
- $params[to_upper]=true;
- $this->dispose_vars($params);
- }
-
- // Alias to dispose_vars, but next_record() is called before
- function dispose_next_record($params='') {
- $this->next_record();
- $this->dispose_vars($params);
- }
-
//--------------------------------------------------------------
@@ -788,7 +1106,7 @@
## Part 2.a: Too much words?
if ($maxwords) {
if ($count($s) > $maxwords) {
- Return(Array(false,1));
+ Return(ARRAY(false,1));
}
}
@@ -866,37 +1184,7 @@
$this->_QDebug("Where-Part: '".join(" ",$p)."'<br>What: $l");
return(Array(join(" ",$p),$l) );
- }
-
- //--------------------------------------------------------------
- // this function can be used to export all the columns of
- // an assoc array *global* variables.
- // The names of the indizes are the name of the global var
- // So it is nearly the same function as dispose_vars()
- // and another oposite function to capture_vars()
- //
- // $param is an optional assoc-array-parameter:
- // to_upper=>true - uppercases all vars, otherwise lowercase
- //--------------------------------------------------------------
- function dispose_asoc_array($array,$params='') {
- if (!is_array($params)) $params=ARRAY();
- for ( reset($array); list($key,$val)=each($array); ) :
- if (ereg("[A-Za-z_][A-Za-z0-9_]*", $key)) {
- if ($params[to_upper]) {
- $field_name = strtoupper($key);
- } else {
- # Default behavior, cause phplib
- # uses always lowercase
- $field_name = strtolower($key);
- }
- $this->_QDebug("'$field_name' => '$val'");
- $GLOBALS[$field_name]=$val;
- } else {
- $this->_QDebug("Not matching or empty: '$key'");
- }
- endfor;
}
-
}
-
PHPLIB Developers Mailing List. Send messages to <phplib-dev <email protected>>.
To unsubscribe, send "unsubscribe" to <phplib-dev-request <email protected>> in
the body, not the subject, of your message.
- Next message: ssilk: "[PHPLIB-DEV] cvs commit"
- Previous message: Alexander Aulbach: "Re: [PHPLIB-DEV] efficiency of table class"
- Next in thread: ssilk: "[PHPLIB-DEV] cvs commit"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]