<?php
/*
My Simplified Quality Expressions - MySQX aka MySqueaks!
Initiated by: Han Jun Kwang (http://hjk.ikueb.com)
version 1.0.5
Last edited: 27th July 2007
For more information, refer to mysqueaks_doc.htm.
Please read and accept the GNU Public License before you use MySqueaks.
A copy of it is found in the LICENSE file: just use any plain text editor, such as the one you are using now, to open it.
TABLE OF CONTENTS (Line number)
01. List of constants.......................... 27
02. SqueaksCore Class.......................... 93
03. General Functions.......................... 160
04. Query Functions............................ 900
05. Logical Operator Functions.................1171
06. Insertion and Selection Functions..........1658
07. Data Type Functions........................2047
08. Alter Table Functions......................2404
09. MySqueaks Class............................2500
10. ASCII Art..................................2617
*/
/*
// **********************************************************************
// START
// List of constants
// **********************************************************************
*/
// for numeric types
define("SQX_UNSIGNED", 1);
define("SQX_UNSIGNED_ZF", 2);
// for Text Fulltext paramter
define("SQX_FULLTEXT", true);
// for Enum/Set
define("SQX_ENUM", 1);
define("SQX_SET", 2);
// for Precision
define("SQX_FLOAT", 1);
define("SQX_DOUBLE", 2);
// for chronological types
define("SQX_DT", 1);
define("SQX_DATE", 2);
define("SQX_TIME", 3);
define("SQX_YEAR", 4);
define("SQX_TS", 5);
// for Char/Binary
define("SQX_NORM", 1);
define("SQX_VAR", 2);
// for subtype
define("SQX_TINY", 1);
define("SQX_SMALL", 2);
define("SQX_MEDIUM", 3);
define("SQX_LONG", 4);
define("SQX_BIG", 5);
define("SQX_DEF", 6);
// for "no quotes"
define("SQX_NO_QUOTES", true);
// for wildcards
define("SQX_WILD_NONE", 0);
define("SQX_WILD_LEFT", 1);
define("SQX_WILD_RIGHT", 2);
define("SQX_WILD_BOTH", 3);
// for "skipping" parameters
define("SQX_SKIP", false);
// for querying
define("SQX_NONE", 2);
define("SQX_NUM_ROWS", 1);
define("SQX_FETCH_ASSOC", 0);
define("SQX_FETCH_ROW", 3);
define("SQX_FETCH_OBJECT", 4);
define("SQX_AFFECTED_ROWS", 5);
// for Join
define("SQX_JOIN", 0);
define("SQX_LEFT", 1);
define("SQX_RIGHT", 2);
define("SQX_INNER", 3);
define("SQX_LEFT_INNER", 4);
define("SQX_RIGHT_INNER", 5);
define("SQX_OUTER", 6);
define("SQX_LEFT_OUTER", 7);
define("SQX_RIGHT_OUTER", 8);
// for Drop (Alter Table)
define("SQX_PRIMARY_KEY", false);
define("SQX_COLUMN", 1);
define("SQX_INDEX", 2);
define("SQX_FOREIGN_KEY", 3);
/*
// **********************************************************************
// START
// SqueaksCore Class
// **********************************************************************
*/
class SqueaksCore {
var $iterator = 0;
var $BunchExp = array();
var $Expressions = array();
var $Fields = array();
var $Set = array();
var $Value = array();
var $Order = array();
var $Limit = 0;
var $Duplicate = -1;
var $Query = "000";
var $Database = null;
var $GroupBy = false;
var $Having = -1;
var $Drop = array();
var $Arrangement = array();
var $Change = array();
var $EngineType = "MyISAM";
var $Union = "";
var $View = "";
var $Error = array("\nSqueaks! There appear to be an error...<br />\nSQX_ERROR<br />\nThe offending SQL statement is:<br />\nSQX_QUERY<br />\nPlease contact the website administrator for help.\n", true, true);
var $Fault = false;
// for 1.0.0
var $ThisTable = null;
var $Tables = array();
var $Columns = array();
var $JoinColumns = array();
var $AliasTable = array();
var $Status = false;
var $TableSelect = array();
var $TableJoin = array();
// constructor
// returns true if successful
function SqueaksCore($dbh, $dbname = false, $tblname = false, $engine = false, $char_set = false, $collate = false, $comment = false) {
if (is_array($dbh)) {
if (count($dbh) >= 3) {
$this->Database = mysql_connect($dbh[0], $dbh[1], $dbh[2]) or $this->getError();
} else {
return false;
}
} else {
if ($dbh) $this->Database = $dbh; else $this->getError();
}
if (!$this->Fault) {
if ($dbname) {
if (!mysql_select_db($dbname, $this->Database)) $this->CreateDatabase($dbname);
}
$this->ThisTable = null;
$this->Tables = array();
$this->Columns = array();
$this->JoinColumns = array();
$this->Status = false;
$this->RefreshTables();
if (is_string($tblname)) $this->Identity($tblname, $engine, $char_set, $collate, $comment);
}
if ($this->Fault) return false; else return true;
}
/*
// **********************************************************************
// START
// General Functions
// **********************************************************************
*/
// resets the object except $this->Database (use ChangeDatabase() function)
// returns true
function Clear() {
$this->iterator = 0;
$this->BunchExp = array();
$this->Expressions = array();
$this->Fields = array();
$this->Set = array();
$this->Value = array();
$this->Order = array();
$this->Limit = 0;
$this->Duplicate = -1;
$this->Query = "000";
$this->GroupBy = false;
$this->Having = -1;
$this->Drop = array();
$this->Arrangement = array();
$this->Change = array();
$this->Error = array("Squeaks! There appear to be an error...<br />\nSQX_ERROR<br />\nThe offending SQL statement is:<br />\nSQX_QUERY<br />\nPlease contact the website administrator for help.\n", true, true);
$this->Fault = false;
$this->TableSelect = array();
$this->TableJoin = array();
return true;
}
// prevents SQL injection attacks
// returns $value
function MakeSafe($value, $no_value_quotes = false) {
if ($value === null) return null;
if (get_magic_quotes_gpc()) $value = stripslashes($value);
if (!is_numeric($value) && !$no_value_quotes) $value = "'". mysql_real_escape_string($value, $this->Database)."'";
return $value;
}
// for 1.0.0: Bind a table to the class
// returns true
function Identity($tblname, $engine = false, $char_set = false, $collate = false, $comment = false) {
if (!is_string($tblname) && !is_numeric($tblname)) return false;
$tblname = trim($tblname);
$this->ThisTable = $tblname;
$this->Columns = array();
$this->JoinColumns = array();
$this->AliasTable = array();
$this->Status = false;
if (!in_array($tblname, $this->Tables, true)) {
$this->CreateTable($tblname, $engine, $char_set, $collate, $comment);
$this->Tables[] = $tblname;
$this->Status = null;
} else {
$this->Columns = $this->TableFields($tblname, true);
$this->Status = true;
}
return true;
}
// for 1.0.0: Get the field name from either $this->Columns or $this->JoinColumns
// returns an empty array if an error occured.
function GetColumnFields($incoming, $col = "Field") {
if (!is_array($incoming)) return array();
$results = array();
foreach ($incoming as $current) if (isset($current[$col])) $results[] = $current[$col];
if (count($results) == 0) {
// maybe it's nested like JoinColumns
foreach ($incoming as $temp) {
if (is_array($temp)) {
foreach ($temp as $current) if (isset($current[$col])) $results[] = $current[$col];
}
}
}
return $results;
}
// for 1.0.0: Set an alias. Defaults to check with table names first unless $set_column is set to true.
// returns true if successful
function Alias($name, $alias, $set_column = false) {
if ($this->ThisTable === null) return false;
// MySQL limitation (which is actually in bytes, not characters)... Need to improve on this
if (strlen($alias) > 255) return false;
// catch all for aliases first to ensure no duplicates
if (in_array($alias, $this->Tables, true)) return false; // check for existing table alias
// check for existing column alias
foreach ($this->Columns as $key => $temp) if ($temp["Field"] == $alias) return false;
// check for joined aliases
foreach ($this->JoinColumns as $t1) foreach ($t1 as $t2) if ($t2["Field"] == $alias) return false;
$field = $this->CheckField($name);
if (!$field) {
// might be a MySQL function (which will need to be validated in later versions)
// in the meantime we assume the user knows what's going on here and just assign the alias
$this->Columns[] = array("Field" => $alias, "Key" => "@".$name);
return true;
}
$done = false;
if (!$set_column && count($field) == 2) {
if (in_array($field["table"], $this->Tables, true) && !in_array($alias, $this->Tables, true)) {
$this->Tables[] = $alias;
$this->AliasTable[$alias] = $field["table"];
$this->JoinColumns[$alias] = array("link" => $field["table"]);
$done = true;
}
}
if (!$done) {
// if table.column is actually for the current Identified table,
// we can safely drop the table name and just compare the column name
if (count($field) == 3 && $field["table"] == $this->ThisTable) {
unset($field["table"]);
}
if (count($field) == 2) {
$t_cols = $this->GetColumnFields($this->Columns);
if (in_array($field["field"], $t_cols, true) && !in_array($alias, $t_cols, true)) {
$this->Columns[] = array("Field" => $alias, "Key" => "@".$field["query"]);
$done = true;
}
} else {
if (isset($this->JoinColumns[$field["table"]])) {
if (isset($this->JoinColumns[$field["table"]]["link"])) {
// column is an alias
$actual = $this->JoinColumns[$field["table"]]["link"];
$done = $this->Alias("`$actual`.`".$field["field"]."`", $alias, $set_column);
} else { // check in other tables
if ( in_array( $field["field"], $this->GetColumnFields( $this->JoinColumns[$field["table"]] ), true ) ) {
$this->JoinColumns[$field["table"]][] = array("Field" => $alias, "Key" => "@".$field["field"]);
$done = true;
}
}
}
}
}
return $done;
}
// for 1.0.0: Simple checks for valid fields
// returns an array of a nicely constructed field (either `field` or `table`.`field`) and the components
// e.g.: array("`table1`.`field1`", "table1", "field1")
function CheckField($field, $check_joins = true) {
if (!is_string($field) && !is_numeric($field)) return false;
$field = trim($field).".";
preg_match_all("/(`[^\\/]+?`(?=\.))|([^\\/\.]+)/", $field, $matches);
if (count($matches[0]) > 2) return false; // at most only table.column
foreach ($matches[0] as $temp) {
if (substr_count($temp, "`") % 2 != 0) {
return false;
} else {
if (substr($temp, 0, 1) == "`" && substr($temp, -1) == "`") {
$check[] = trim(substr($temp, 1, strlen($temp) - 2));
} else {
$check[] = trim($temp);
}
}
}
if (count($check) == 2) {
if ($this->ThisTable && !in_array(str_replace("``", "`", $check[0]), $this->Tables, true)) {
// test if it's actually a column name
$t_cols = $this->GetColumnFields($this->Columns);
if ($check_joins) $t_jcols = $this->GetColumnFields($this->JoinColumns); else $t_jcols = array();
$test = (str_replace("``", "`", $check[0])).".".(str_replace("``", "`", $check[1]));
if (!in_array($test, $t_cols, true) && !in_array($test, $t_jcols, true)) {
return false;
} else {
return array("query" => "`".$check[0].".".$check[1]."`", "field" => $test); // enough checks done
}
}
$check_field = str_replace("``", "`", $check[1]);
$results = array();
$results["query"] = "`".$check[0]."`.`".$check[1]."`";
$results["table"] = str_replace("``", "`", $check[0]);
$results["field"] = str_replace("``", "`", $check[1]);
} else {
$check_field = str_replace("``", "`", $check[0]);
$results = array("query" => "`".$check[0]."`", "field" => str_replace("``", "`", $check[0]));
}
if ($this->ThisTable) {
// let's validate
$t_cols = $this->GetColumnFields($this->Columns);
if ($check_joins) $t_jcols = $this->GetColumnFields($this->JoinColumns); else $t_jcols = array();
if (count($check) == 2) {
$check_alias = isset($this->AliasTable[$results["table"]]);
$check_alias = $check_alias && $this->AliasTable[$results["table"]] == $this->ThisTable;
if (!$check_alias && $results["table"] != $this->ThisTable) {
$t = $results["table"]; // some other table
$t_cols = $this->TableFields((isset($this->AliasTable[$t]) ? $this->AliasTable[$t] : $t));
$t_jcols = array();
}
}
if (!in_array($check_field, $t_cols, true)) {
if (!in_array($check_field, $t_jcols, true)) {
// for single $field, $field might be a table name instead of the column match we did by default, re-validate...
if (count($check) == 1) {
if (!isset($this->AliasTable[$check_field]) && !in_array($check_field, $this->Tables, true)) {
return false;
} else {
$results = array("query" => "`".$check[0]."`", "table" => str_replace("``", "`", $check[0]));
}
} else {
return false;
}
}
}
}
return $results;
}
// for 1.0.0: Determine if we need to auto-prepare SQL statements
function DoReset($match) {
if (!is_string($match)) return false;
if ($this->ThisTable && substr($this->Query, 0, strlen($match)) != $match) return true; else return false;
}
// for 1.0.0: Refresh list of tables in current database
function RefreshTables() {
$result = mysql_query("SHOW TABLES", $this->Database) or $this->getError();
while ($row = mysql_fetch_array($result)) $this->Tables[] = $row[0];
return true;
}
// prepares the WHERE part for SELECT, UPDATE and DELETE
// returns true
function PrepareWhere($do_extra = true, $format = false) {
$append_bunchexp_logop = false;
foreach ($this->Expressions as $key => $curr_exp) {
if ($key == $this->Having) {
if ($this->GroupBy) {
$this->Query = $this->Query." GROUP BY ".$this->GroupBy[0].($this->GroupBy[1] ? " WITH ROLLUP " : "");
}
$append_clause = " HAVING";
$append_bunchexp_logop = false;
} else {
$append_clause = ($format ? "\n" : "")." WHERE";
}
$this->Query = $this->Query.($append_bunchexp_logop ? " ".$this->BunchExp[$key] : $append_clause)." ( ".($format ? "\n\t" : "");
$append_bunchexp_logop = true;
$append_operator_logop = false;
foreach ($curr_exp as $subkey => $curr_param) {
if ($curr_param[3] === null) $curr_param[3] = " NULL ";
$this->Query = $this->Query.($append_operator_logop ? $curr_param[0] : "")." ";
$this->Query = $this->Query.$curr_param[1].$curr_param[2].$curr_param[3]." ";
$append_operator_logop = true;
if ($format && $subkey < count($curr_exp) - 1 ) $this->Query = $this->Query."\n\t";
}
$this->Query = $this->Query.($format && $key < count($this->Expressions) ? "\n" : "")." )";
}
// if no Having() is set, we still need to set GROUP BY if that was specified
if ($this->GroupBy && $this->Having == -1) {
$this->Query = $this->Query." GROUP BY ".$this->GroupBy[0].($this->GroupBy[1] ? " WITH ROLLUP " : "");
}
if ($do_extra) {
if (count($this->Order)) {
$this->Query = $this->Query." ORDER BY ";
foreach ($this->Order as $key => $current)
$this->Query = $this->Query.$current[0].$current[1].($key < count($this->Order) - 1 ? ", " : "");
}
if (is_array($this->Limit)) {
if ($format) $this->Query = $this->Query."\n";
$this->Query = $this->Query." LIMIT ".$this->Limit[0].", ".$this->Limit[1];
} elseif ($this->Limit > 0) {
if ($format) $this->Query = $this->Query."\n";
$this->Query = $this->Query." LIMIT 0, ".$this->Limit;
}
}
return true;
}
// prepares columns for CREATE TABLE and ALTER TABLE ADD
// returns true
function PrepareColumns($format = false) {
foreach ($this->Fields as $key => $curr_var) {
$this->Query = $this->Query.($format ? "\n\t" : "");
if (substr($this->Query, 0, 3) == "ALT") {
if (isset($this->Change[$key])) {
$this->Query = $this->Query."CHANGE ".$this->Change[$key]." ";
} else {
$this->Query = $this->Query."ADD ";
}
}
$this->Query = $this->Query."`".$curr_var["name"]."` ".$curr_var["cast"];
// include length if specified
if (isset($curr_var["length"])) {
if (is_array($curr_var["length"])) {
$this->Query = $this->Query."(".$curr_var["length"][0].", ".$curr_var["length"][1].")";
} else {
if ($curr_var["vartype"] != "BLO" && $curr_var["vartype"] != "TEX") {
$this->Query = $this->Query."(".$curr_var["length"].")";
} else {
if ($curr_var["cast"] == "BLOB" || $curr_var["cast"] == "TEXT") {
$this->Query = $this->Query."(".$curr_var["length"].")";
}
}
}
}
// include default values for ENUM or SET
if ($curr_var["vartype"] == "ENS") {
$this->Query = $this->Query."(";
if (is_array($curr_var["default"])) {
$this->Query = $this->Query."'".implode("', '", $curr_var["default"])."'";
} else {
$this->Query = $this->Query.$curr_var["default"];
}
$this->Query = $this->Query.")";
}
// are the numeric data types unsigned/zerofill?
if (isset($curr_var["unsigned"]) && $curr_var["unsigned"]) {
$this->Query = $this->Query." UNSIGNED ";
if ($curr_var["unsigned"] == 2) $this->Query = $this->Query." ZEROFILL ";
}
// add character set and collate if specified
if (isset($curr_var["char_set"]) && $curr_var["char_set"]) {
$this->Query = $this->Query." CHARACTER SET ".$curr_var["char_set"]." ";
}
if (isset($curr_var["collate"]) && $curr_var["collate"]) {
$this->Query = $this->Query." COLLATE ".$curr_var["collate"]." ";
}
// are null values allowed?
if (!isset($curr_var["null"])) $this->Query = $this->Query." NOT NULL ";
// append auto_increment if specified
if (isset($curr_var["autoinc"])) $this->Query = $this->Query." AUTO_INCREMENT ";
// append default values, unless current data type is ENUM or SET
if (isset($curr_var["default"]) && $curr_var["default"] && $curr_var["vartype"] != "ENS") {
if ($curr_var["default"] === null) $default = "NULL";
if (is_string($curr_var["default"]) || is_numeric($curr_var["default"]))
$default = "'".$curr_var["default"]."'";
else
$default = "";
if (strlen($default) != 0) $this->Query = $this->Query." DEFAULT ".$default." ";
}
// check if we need to specify arrangement for ALTER TABLE ADD
if (substr($this->Query, 0, 3) == "ALT" && isset($this->Arrangement[$key])) {
$this->Query = $this->Query.$this->Arrangement[$key];
}
if ($key != count($this->Fields) - 1) $this->Query = $this->Query. ", ";
}
// add primary key, unique, index, fulltext and foreign key
$condition = array("primary key", "index", "unique", "fulltext");
foreach ($condition as $i => $clause) {
$set = false;
foreach ($this->Fields as $i => $curr) {
if (isset($curr[$clause])) {
if (!$set) {
$this->Query = $this->Query.", ".($format ? "\n\t" : "");
if (substr($this->Query, 0, 3) == "ALT") $this->Query = $this->Query."ADD ";
if (is_string($curr[$clause]) && ($clause == "primary key" || $clause == "unique"))
$this->Query = $this->Query."CONSTRAINT `".$this->MakeSafe($curr[$clause], true)."` ";
$this->Query = $this->Query.strtoupper($clause);
if (is_string($curr[$clause]) && ($clause == "index" || $clause == "fulltext"))
$this->Query = $this->Query." ".$this->MakeSafe($curr[$clause]);
$this->Query = $this->Query." (";
$set = true;
}
$this->Query = $this->Query."`".$curr["name"]."`, ";
}
}
if ($set) $this->Query = substr($this->Query, 0, strlen($this->Query) - 2).")";
}
//foreign key references (group by tables first)
$fk = array();
foreach ($this->Fields as $i => $curr) {
if (isset($curr["foreign key"]) && is_array($curr["foreign key"]) && count($curr["foreign key"]) >= 5) {
$curr_fk = $curr["foreign key"];
if ($this->ThisTable && !in_array($curr_fk[0], $this->Tables, true)) continue;
if ( $this->ThisTable && !in_array( $curr_fk[1], $this->TableFields($curr_fk[0]), true ) ) continue;
elseif (!is_string($curr_fk[1]) && !is_numeric($curr_fk[1])) continue;
if ((is_string($curr_fk[0]) || is_numeric($curr_fk[0]))) {
if ( !isset( $fk[$curr_fk[0]] ) ) {
for ($j = 2; $j <= 3; $j++) {
if (is_string($curr_fk[$j])) {
$curr_fk[$j] = strtoupper( trim( $curr_fk[$j] ) );
if ($curr_fk[$j] != "RESTRICT" && $curr_fk[$j] != "SET NULL" && $curr_fk[$j] != "NO ACTION")
$curr_fk[$j] = "CASCADE";
} else {
$curr_fk[$j] = "CASCADE";
}
}
if (!is_string($curr_fk[4]) && !is_numeric($curr_fk[4])) $curr_fk[4] = false;
if ($curr_fk[2] != "CASCADE") $fk[$curr_fk[0]]["del"] = $curr_fk[2];
if ($curr_fk[3] != "CASCADE") $fk[$curr_fk[0]]["upd"] = $curr_fk[3];
$fk[$curr_fk[0]]["name"] = $curr_fk[4];
}
$fk[$curr_fk[0]]["from"][] = $curr["name"];
$fk[$curr_fk[0]]["to"][] = $curr_fk[1];
}
}
}
foreach ($fk as $k => $ref) {
$this->Query = $this->Query." , ".($format ? "\n\t" : "");
if (substr($this->Query, 0, 3) == "ALT") $this->Query = $this->Query."ADD ";
if ($ref["name"]) $this->Query = $this->Query."CONSTRAINT `".$this->MakeSafe($ref["name"], true)."` ";
$this->Query = $this->Query."FOREIGN KEY (`".implode("`, `", $ref["from"])."`) REFERENCES `$k`";
$this->Query = $this->Query." (`".implode("`, `", $ref["to"])."`)";
if (isset($ref["del"])) $this->Query = $this->Query." ON DELETE ".$ref["del"];
if (isset($ref["upd"])) $this->Query = $this->Query." ON UPDATE ".$ref["upd"];
}
if (substr($this->Query, 0, 3) == "ALT" && count($this->Drop) > 0) $this->Query = $this->Query.", ";
}
// builds the query
// returns true
function BuildQuery($format = false) {
// "hacking" our way to make sure REPLACE statements are "redirected" to the "INSERT" case...
if (substr($this->Query, 0, 3) == "REP") $this->Query = "INS".substr($this->Query, 3, strlen($this->Query) - 3);
switch (substr($this->Query, 0, 3)) {
case "SEL": // SELECT
if ($this->ThisTable) {
// prepare our SQL query
if (count($this->TableSelect) > 0) {
foreach ($this->TableSelect as $key => $c) {
// check binded table columns and then joined table columns
$done = false;
foreach ($this->Columns as $temp) {
if ("`".$temp["Field"]."`" == $c && substr($temp["Key"], 0, 1) == "@") {
$this->Query = $this->Query.substr($temp["Key"], 1)." AS ";
$done = true;
break;
}
}
foreach ($this->JoinColumns as $tbl_key => $temp) {
foreach ($temp as $current) {
if ("`".$current["Field"]."`" == $c && substr($current["Key"], 0, 1) == "@") {
foreach ($this->AliasTable as $temp_key => $temp) {
if ($tbl_key == $temp) {
$tbl_key = $temp_key;
break;
}
}
// JoinColumns do not have any "invalid" aliases, so we can safely wrap with ` `
$this->Query = $this->Query."`".$tbl_key."`.`".substr($current["Key"], 1)."` AS ";
break;
}
}
}
$this->Query = $this->Query.$c;
$this->Query = $this->Query.($key == count($this->TableSelect) - 1 ? " " : ", ");
}
} else {
$this->Query = $this->Query." * ";
}
// FROM clause
// Do those tables that are Matched() first
$this->Query = $this->Query."FROM ".($format ? "\n\t" : "");
foreach ($this->TableJoin as $c)
if ($c["st"] == -1) $this->Query = $this->Query.$c["jt"].", ".($format ? "\n\t" : "");
$ori_table = "";
$ori_alias = "";
foreach ($this->TableJoin as $key => $c) {
if ($c["st"] == -1) continue;
if ($c["jt"] === null) $c["jt"] = $this->ThisTable;
$output_table = ($ori_table != $c["jt"]);
if ($output_table) {
$tbl_name = "`".$c["jt"]."`";
$tbl_alias = "";
foreach ($this->AliasTable as $temp_key => $temp) {
if ($temp == $c["jt"]) {
$tbl_name = $tbl_name." AS `$temp_key`";
$tbl_alias = "`".$temp_key."`";
break;
}
}
}
if ($key == 0) $this->Query = $this->Query.$tbl_name;
if ($output_table && $key != 0) {
$this->Query = $this->Query.$c["st"]." JOIN ".$tbl_name;
if (is_string($c["jf"]) || is_numeric($c["jf"])) $c["jf"][] = $c["jf"]; // just in case
if (is_string($ori_field) || is_numeric($ori_field)) $ori_field[] = $ori_field; // just in case
if (count($c["jf"]) == 1 && count($ori_field) == 1 && $c["jf"][0] == $ori_field[0]) {
$this->Query = $this->Query." USING (`".$ori_field[0]."`) ";
} else {
$this->Query = $this->Query." ON (";
if ($tbl_alias != "") $tbl_name = $tbl_alias;
if ($ori_alias != "") $ori_table = $ori_alias;
if (count($c["jf"]) >= count($ori_field)) $loop = $ori_field; else $loop = $c["jf"];
foreach ($loop as $key => $current) {
$this->Query = $this->Query.$ori_table.".`".$ori_field[$key]."` = ";
$this->Query = $this->Query.$tbl_name.".`".$c["jf"][$key]."`";
if ($key < count($loop) - 1) $this->Query = $this->Query." AND ";
else $this->Query = $this->Query." ";
}
$this->Query = $this->Query.")";
}
}
$ori_field = $c["jf"];
$ori_table = $c["jt"];
$ori_alias = $tbl_alias;
}
if (count($this->TableJoin) == 0) $this->Query = $this->Query."`".$this->ThisTable."` ";
}
if (strlen($this->View) != 0) $this->Query = "CREATE VIEW ".$this->View." AS ".$this->Query;
$this->PrepareWhere(true, $format);
$this->Query = str_replace("```", ($format ? "\n\t" : ""), $this->Query); // ``` "cheat" for formatted output
if (strlen($this->Union) != 0) {
$this->Query = $this->Query.($format ? "\n" : "")." UNION ".($format ? "\n" : "").$this->Union;
}
break;
case "UPD": // UPDATE
if (substr($this->Query, -1, 1) == "1") $do_extra = false; else $do_extra = true;
$this->Query = substr($this->Query, 0, strlen($this->Query) - 1);
foreach ($this->Set as $key => $curr_set) {
$this->Query = $this->Query.$curr_set[0]." = ".$curr_set[1].($key < count($this->Set) - 1 ? ", " : "");
}
$this->PrepareWhere($do_extra, $format);
break;
case "DEL": // DELETE
if (substr($this->Query, -1, 1) == "1") $do_extra = false; else $do_extra = true;
$this->Query = substr($this->Query, 0, strlen($this->Query) - 1);
$this->PrepareWhere($do_extra, $format);
break;
case "INS": // INSERT / REPLACE
// undo the "REPLACE" hack
if (substr($this->Query, 3, 1) == "L") $this->Query = "REP".substr($this->Query, 3, strlen($this->Query) - 3);
if (count($this->Value) == 0) {
$this->Query = $this->Query." SET ".($format ? "\n\t" : "");
} else {
if ($this->Value[0] !== null) {
// we need not append backticks ` if object is binded to a table
if ($this->ThisTable && $this->Status) $glue = ", "; else $glue = "`, `";
$this->Query = $this->Query." (".($this->ThisTable && $this->Status ? "" : "`");
$this->Query = $this->Query.implode($this->Value, $glue);
$this->Query = $this->Query.($this->ThisTable && $this->Status ? "" : "`").")";
} else {
$this->Value = $this->TableFields($this->Value[1]);
}
$this->Query = $this->Query." VALUES ".($format ? "\n\t" : "")."(";
}
$counter = 0;
if (count($this->Value) != 0) {
for ($i = 0; $i < count($this->Set) % count($this->Value); $i++) {
$this->Set[] = array(null, "''");
}
}
foreach ($this->Set as $key => $curr_set) {
if ($key == $this->Duplicate) {
$this->Query = $this->Query." ON DUPLICATE KEY UPDATE ";
$this->Value = array();
}
if ($curr_set[1] === null) $curr_set[1] = " NULL ";
if (count($this->Value) == 0) {
$output = $curr_set[0]." = ".$curr_set[1];
$output = $output.($key < count($this->Set) - 1 && $key != $this->Duplicate - 1 ? ", " : "");
$output = $output.($format ? "\n\t" : "");
} else {
$output = "";
if ($counter == count($this->Value)) {
$output = "), ".($format ? "\n\t" : "")."(";
$counter = 0;
}
$output = $output.$curr_set[1].($counter != count($this->Value) - 1 ? ", " : " ");
$counter++;
}
$this->Query = $this->Query.$output;
}
if (count($this->Value) != 0) $this->Query = $this->Query.")";
break;
case "CRE": // CREATE TABLE
$start_part = substr($this->Query, 0, strpos($this->Query, "` (") + 3);
$end_part = substr($this->Query, strlen($start_part), strlen($this->Query) - strlen($start_part));
$this->Query = $start_part;
$this->PrepareColumns($format);
$this->Query = $this->Query.($format ? "\n" : "").$end_part;
break;
case "ALT": // ALTER TABLE
// add columns (if any)
$this->PrepareColumns($format);
// drop columns
foreach ($this->Drop as $key => $curr_var) {
if (!is_array($curr_var)) {
$this->Query = $this->Query.($format ? "\n\t" : "")."DROP ".$curr_var;
if ($key != count($this->Drop) - 1) $this->Query = $this->Query. ", ";
}
}
// alter character set
if (isset($this->Drop["char_set"])) {
$this->Query = $this->Query.", ".($format ? "\n\t" : "")."CONVERT TO CHARACTER SET ".$this->Drop["char_set"][0];
$this->Query = $this->Query.($this->Drop["char_set"][1] ? " COLLATE ".$this->Drop["char_set"][1] : "");
}
break;
}
$this->Query = $this->Query." ;";
return true;
}
// pseudo-constructor for changing the current database handler
// returns true
function ChangeDatabase($dbh, $dbname = false, $tblname = false, $engine = false, $char_set = false, $collate = false, $comment = false) {
$this->Clear();
if (is_array($dbh)) {
$this->Database = mysql_connect ($dbh[0], $dbh[1], $dbh[2]) or $this->getError();
} else {
if ($dbh) $this->Database = $dbh; else $this->getError();
}
if (!$this->Fault) {
if ($dbname) {
if (!mysql_select_db($dbname, $this->Database)) $this->CreateDatabase($dbname);
}
$this->ThisTable = null;
$this->Tables = array();
$this->Columns = array();
$this->JoinColumns = array();
$this->Status = false;
$this->RefreshTables();
if (is_string($tblname)) $this->Identity($tblname, $engine, $char_set, $collate, $comment);
}
if ($this->Fault) return false; else return true;
}
function chdb($dbh, $dbname = false, $tblname = false) {
return $this->ChangeDatabase($dbh, $dbname, $tblname);
}
// creates a new database
// returns true
function CreateDatabase($dbname, $char_set = false, $collate = false, $commit = true) {
$this->Clear();
$this->Query = "CREATE DATABASE IF NOT EXISTS `".$dbname.($char_set ? "` DEFAULT CHARACTER SET ".$char_set : "`");
$this->Query = $this->Query.($collate ? " DEFAULT COLLATE ".$collate : "");
if ($commit) {
$result = mysql_query($this->Query, $this->Database) or $this->getError();
mysql_select_db($dbname);
}
return true;
}
function newdb($dbname, $char_set = false, $collate = false, $commit = true) {
return $this->CreateDatabase($dbname, $char_set, $collate, $commit);
}
// adds a new bunch of expressions, $log_op for the new bunch
// $log_op accepted inputs: AND (by default), OR, NOT, XOR
// e.g. ... AND ( "new bunch") OR ... -> $log_op will be AND, not OR
// returns true
function NewBunch($log_op = "AND", $mode = false) {
if (!is_string($log_op)) return false; else $log_op = strtoupper(trim($log_op));
if ($log_op != "AND" && $log_op != "OR" && $log_op != "NOT" && $log_op != "XOR") return false;
if ($this->DoReset("SEL") && $this->DoReset("UPD") && $this->DoReset("DEL") && $this->Status) {
// $mode is now used to specify the SQL statement to prepare
$this->Clear();
if ($mode) {
if ( strtolower(trim($mode)) == "update") $this->Update($this->ThisTable);
elseif ( strtolower(trim($mode)) == "delete") $this->Delete($this->ThisTable);
else $this->Query = "SELECT ";
} else {
$this->Query = "SELECT ";
}
}
if ($this->iterator + 1 != $this->Having) $this->iterator++; else $this->iterator = $this->iterator + 2;
$this->BunchExp[$this->iterator] = $log_op;
return true;
}
function bunch($log_op = "AND", $mode = false) {
return $this->NewBunch($log_op, $mode);
}
// closes the current bunch of expressions
// if $not_specific is true, function will always return true, else return false when $this->iterator = 0 i.e. first bunch
function CloseBunch($not_specific = false) {
if ($this->iterator != 0) {
$this->iterator--;
return true;
} else {
if ($not_specific) return true; else return false;
}
}
// sets the "flag point" for setting duplicate fields/values for INSERT... ON DUPLICATE KEY UPDATE
// if $open = false, $this->Duplicate will be set back to the initial value of -1.
// returns true
function OnDuplicate($open = true) {
if (substr($this->Query, 0, 3) != "INS") return false;
if (!$open) $this->Duplicate = -1; else $this->Duplicate = count($this->Set);
return true;
}
// returns an array of fields (column names) of $tblname
// useful for combining with the logical operator functions for setting many fields at one go
function TableFields($tblname, $get_all = false) {
if (!is_string($tblname) && !is_numeric($tblname)) return array();
$this->RefreshTables();
$table = $this->CheckField($tblname, false);
if (!$table || count($table) > 2) return array();
if (isset($table["table"]) && $table["table"] == $this->ThisTable && $this->Status == true)
if ($get_all) return $this->Columns; else return $this->GetColumnFields($this->Columns);
$result = mysql_query("SHOW COLUMNS FROM ".$table["query"], $this->Database) or $this->getError();
$field_names = array();
while ($row = mysql_fetch_assoc($result)) if ($get_all) $field_names[] = $row; else $field_names[] = $row['Field'];
mysql_free_result($result);
return $field_names;
}
// returns the query string
function GetQuery($format = false) {
if (substr($this->Query, -1, 1) != ";") $this->BuildQuery($format);
return $this->Query;
}
// sets the engine type for CREATE TABLE
// returns true
function UseEngine($engine = false) {
if ($engine) {
$this->EngineType = $engine;
return true;
} else {
return $this->Engine;
}
}
// sets Error handler
// returns true
function setError($message, $die = true, $verbose = true) {
if (!$message) {
$message = "Squeaks! There appear to be an error...<br />\nSQX_ERROR<br />\nThe offending SQL statement is:<br />\nSQX_QUERY<br />\nPlease contact the website administrator for help.\n";
}
$this->Error = array($message, $die, $verbose);
return true;
}
// displays error message
function getError() {
// replace SQX_QUERY with the generated query, SQX_ERRNO with MySQL error number, and SQX_ERROR with MySQL error message
// case sensitive
$this->Fault = true;
$msg = str_replace("SQX_ERROR", mysql_error($this->Database), $this->Error[0]);
$msg = str_replace("SQX_ERRNO", mysql_errno($this->Database), $msg);
$msg = str_replace("SQX_QUERY", $this->GetQuery(), $msg);
if ($this->Error[1]) {
die($msg);
} else {
if (PHP_VERSION < 5) {
if ($this->Error[2]) echo $msg;
} else {
eval("throw new Exception(\"$msg\");");
}
}
return true;
}
/*
// **********************************************************************
// END General Functions
// START Query Functions
// **********************************************************************
*/
// prepares a CREATE VIEW query
// if $viewname is not a string, returns false; else returns true
function CreateView($viewname) {
if (!is_string($viewname)) return false;
$this->Clear();
$this->View = $viewname;
return true;
}
// "closes" a CREATE VIEW (when user recycles the object for other SELECT statements)
// returns true
function CloseView() {
$this->View = "";
return true;
}
// prepares a SELECT query
// if $tblnames is not a string or an array, returns false; else returns true
function Select($tblnames, $no_quotes = false, $exp = false, $distinct = false) {
if ($this->ThisTable) {
// $tblnames become field names
if (is_array($tblnames)) {
foreach ($tblnames as $current) {
$check = $this->CheckField($current);
if (isset($check["field"])) $this->TableSelect[] = $check["query"];
}
} else {
if (strtoupper(trim($tblnames)) == "`DISTINCT") {
$this->Query = $this->Query."DISTINCT ";
} else {
$check = $this->CheckField($tblnames);
if (isset($check["field"])) $this->TableSelect[] = $check["query"];
}
}
return true; // we'll only append the JOIN clauses during BuildQuery()
}
if (!is_string($tblnames) && !is_numeric($tblnames) && !is_array($tblnames)) return false;
if (strlen($this->View) == 0) $this->Clear();
$this->Query = "SELECT ";
if ($distinct) $this->Query = $this->Query."DISTINCT ";
if (!$no_quotes) {
$append = "`";
$implode_term = "`, `";
} else {
$append = "";
$implode_term = ", ";
}
if (!$exp) {
$this->Query = $this->Query."* FROM ";
} else {
if (is_array($exp)) {
$this->Query = $this->Query.$append.implode($implode_term, $exp).$append." FROM ";
} else {
$this->Query = $this->Query.$append.$exp.$append." FROM ";
}
}
if (is_array($tblnames)) {
$this->Query = $this->Query.$append.implode($implode_term, $tblnames).$append;
} else {
$this->Query = $this->Query.$append.$tblnames.$append;
}
return true;
}
function sel($tblnames, $no_quotes = false, $exp = false, $distinct = false) {
return $this->Select($tblnames, $no_quotes, $exp, $distinct);
}
// prepares a UNION clause
// if $union is not a string, returns false; else returns true
function Union($union) {
if (!is_string($union)) return false;
$this->Union = $union;
return true;
}
// "closes" a UNION (when user recycles the object for other SELECT statements)
// returns true
function CloseUnion() {
$this->Union = "";
return true;
}
// prepares a UPDATE query
// if $tblnames is not a string or an array, returns false; else returns true
function Update($tblnames, $no_quotes = false) {
if (!is_string($tblnames) && !is_numeric($tblnames) && !is_array($tblnames)) return false;
$this->Clear();
$this->Query = "UPDATE ";
if (is_array($tblnames)) {
foreach ($tblnames as $key => $curr_table) {
$this->Query = $this->Query.(!$no_quotes ? "`" : "").$curr_table.(!$no_quotes ? "`" : "");
$this->Query = $this->Query.($key < count($tblnames) - 1 ? ", " : "");
}
} else {
$this->Query = $this->Query.(!$no_quotes ? "`" : "").$tblnames.(!$no_quotes ? "`" : "");
}
$this->Query = $this->Query." SET ".(is_array($tblnames) ? "1" : " ");
return true;
}
function upd($tblnames, $no_quotes = false) {
return $this->Update($tblnames, $no_quotes);
}
// prepares a DELETE query
// if $tblnames is not a string or an array, returns false; else returns true
function Delete($tblnames, $no_quotes = false) {
if (!is_string($tblnames) && !is_numeric($tblnames) && !is_array($tblnames)) return false;
$this->Clear();
$this->Query = "DELETE FROM ";
if (is_array($tblnames)) {
foreach ($tblnames as $key => $curr_table)
$this->Query = $this->Query.(!$no_quotes ? "`" : "").$curr_table.(!$no_quotes ? "`" : "");
$this->Query = $this->Query.($key < count($tblnames) - 1 ? ", " : "");
} else {
$this->Query = $this->Query.(!$no_quotes ? "`" : "").$tblnames.(!$no_quotes ? "`" : "");
}
$this->Query = $this->Query.(is_array($tblnames) ? "1" : " ");
return true;
}
function del($tblnames, $no_quotes = false) {
return $this->Delete($tblnames, $no_quotes);
}
// prepares a INSERT query
// if $tblnames is not a string, returns false; else returns true
function Insert($tblname, $no_quotes = false, $clear = true) {
if (!is_string($tblname) && !is_numeric($tblname)) return false;
if ($clear) $this->Clear();
$this->Query = "INSERT INTO ".(!$no_quotes ? "`" : "").$tblname.(!$no_quotes ? "`" : "");
return true;
}
function ins($tblname, $no_quotes = false) {
return $this->Insert($tblname, $no_quotes);
}
// prepares a REPLACE query
// if $tblnames is not a string, returns false; else returns true
function Replace($tblname, $no_quotes = false, $clear = true) {
if (!is_string($tblname) && !is_numeric($tblname)) return false;
if ($clear) $this->Clear();
$this->Query = "REPLACE INTO ".(!$no_quotes ? "`" : "").$tblname.(!$no_quotes ? "`" : "");
return true;
}
function rpl($tblname, $no_quotes = false) {
return $this->Replace($tblname, $no_quotes);
}
// prepares a TRUNCATE query
// if $tblname is not a string, returns false; else returns true
function Truncate($tblname = true, $no_quotes = false, $commit = true) {
if (!is_string($tblname) && !is_numeric($tblname) && $this->ThisTable === null) return false;
$this->Clear();
if ($this->ThisTable) $t_tblname = $this->ThisTable; else $t_tblname = $tblname;
$this->Query = "TRUNCATE ".(!$no_quotes || $this->ThisTable ? "`" : "").$t_tblname;
$this->Query = $this->Query.(!$no_quotes || $this->ThisTable ? "` ;" : ";");
if ((!$this->ThisTable && $commit) || ($this->ThisTable && $tblname))
$result = mysql_query($this->Query, $this->Database) or $this->getError();
return true;
}
// prepares a CREATE TABLE query
// returns true
function CreateTable($tblname, $engine = false, $char_set = false, $collate = false, $comment = false) {
if (!is_string($tblname) && !is_numeric($tblname)) return false;
$this->Clear();
if (!$engine) $engine = $this->EngineType;
$this->Query = "CREATE TABLE IF NOT EXISTS `".$tblname."` () ENGINE = ".trim($engine);
if ($char_set) $this->Query = $this->Query." DEFAULT CHARSET = ".trim($char_set);
if ($collate) $this->Query = $this->Query." DEFAULT COLLATE = ".trim($collate);
if ($comment) $this->Query = $this->Query." COMMENT = '".$comment."'";
return true;
}
function table($tblname, $engine = false, $char_set = false, $collate = false, $comment = false) {
return $this->CreateTable($tblname, $engine, $char_set, $collate, $comment);
}
// prepares a ALTER TABLE query
// returns true
function AlterTable($tblname = false) {
if ($tblname === false) $tblname = $this->ThisTable;
if (!is_string($tblname) && !is_numeric($tblname)) return false;
$this->Clear();
$this->Query = "ALTER TABLE `".$tblname."` ";
return true;
}
function alter($tblname) {
return $this->AlterTable($tblname);
}
// prepares a OPTIMIZE TABLE query
// if $tblname is not a string, returns false; else returns true
function OptimizeTable($tblname = true, $no_quotes = false, $commit = true) {
if (!is_string($tblname) && !is_numeric($tblname) && $this->ThisTable === null) return false;
$this->Clear();
if ($this->ThisTable) $t_tblname = $this->ThisTable; else $t_tblname = $tblname;
$this->Query = "OPTIMIZE TABLE ".(!$no_quotes || $this->ThisTable ? "`" : "").$t_tblname;
$this->Query = $this->Query.(!$no_quotes || $this->ThisTable ? "` ;" : ";");
if ((!$this->ThisTable && $commit) || ($this->ThisTable && $tblname))
$result = mysql_query($this->Query, $this->Database) or $this->getError();
return true;
}
// prepares a DROP TABLE query
// accepts arrays as well
// returns true
function DropTable($tblnames = true, $no_quotes = false, $commit = true) {
if ($this->ThisTable === null && !is_string($tblnames) && !is_numeric($tblnames) && !is_array($tblnames)) return false;
$this->Clear();
if ($this->ThisTable) $t_tblnames = $this->ThisTable; else $t_tblnames = $tblnames;
if (is_array($t_tblnames)) {
$this->Query = "DROP TABLE IF EXISTS ".(!$no_quotes ? "`" : "").implode((!$no_quotes ? "`, `" : ", "), $t_tblnames);
$this->Query = $this->Query.(!$no_quotes ? "` ;" : ";");
} else {
$this->Query = "DROP TABLE IF EXISTS ".(!$no_quotes || $this->ThisTable ? "`" : "").$t_tblnames;
$this->Query = $this->Query.(!$no_quotes || $this->ThisTable ? "` ;" : ";");
}
if ((!$this->ThisTable && $commit) || ($this->ThisTable && $tblnames))
$result = mysql_query($this->Query, $this->Database) or $this->getError();
return true;
}
// prepares a RENAME TABLE query
// accepts arrays for both as well
// returns true
function RenameTable($from_tblnames, $to_tblnames = false, $from_no_quotes = false, $to_no_quotes = false, $commit = true) {
if ($this->ThisTable === null) {
if (is_string($from_tblnames) && !is_string($to_tblnames)) return false;
elseif (is_array($from_tblnames) && !is_array($to_tblnames)) return false;
elseif (!is_string($from_tblnames) && !is_array($to_tblnames)) return false;
$f_tables = $from_tblnames;
$t_tables = $to_tblnames;
$f_nq = $from_no_quotes;
$t_nq = $to_no_quotes;
} else {
if (!is_string($from_tblnames) && !is_numeric($from_tblnames)) return false;
$f_tables = $this->ThisTable;
$t_tables = $from_tblnames;
$f_nq = false;
$t_nq = $to_tblnames;
}
$this->Clear();
$this->Query = "RENAME TABLE ";
if (is_array($f_tables)) {
foreach ($f_tables as $key => $curr_table) {
$this->Query = $this->Query.(!$f_nq ? "`" : "").$curr_table.(!$f_nq ? "`" : "")." TO ".(!$t_nq ? "`" : "");
$this->Query = $this->Query.$t_tables[$key].(!$t_nq ? "`" : "").($key < count($f_tables) - 1 ? ", " : "");
}
} else {
$this->Query = $this->Query.(!$f_nq ? "`" : "").$f_tables.(!$f_nq ? "`" : "")." TO ".(!$t_nq ? "`" : "");
$this->Query = $this->Query.$t_tables.(!$t_nq ? "`" : "");
}
$this->Query = $this->Query." ;";
if ((!$this->ThisTable && $commit) || ($this->ThisTable && $from_no_quotes))
$result = mysql_query($this->Query, $this->Database) or $this->getError();
return true;
}
/*
// **********************************************************************
// END Query Functions
// START Logical Operator Functions
// **********************************************************************
*/
// field = value, $log_op for the expression
// $log_op accepted inputs: AND, OR, NOT, XOR
// e.g. OR a = 'b' AND -> $log_op will be OR, not AND
// returns true
function Equals($log_op, $field, $value, $no_field_quotes = false, $no_value_quotes = false) {
if (!is_string($log_op)) return false; else $log_op = strtoupper(trim($log_op));
if ($log_op != "AND" && $log_op != "OR" && $log_op != "NOT" && $log_op != "XOR") return false;
// we'll need to auto-assign each value to the respective field if $value is passed as an array
if (is_array($value)) {
if ($this->ThisTable) return false; // obvious
// $field becomes the table name in this case
$field_names = $this->TableFields($field);
foreach ($field_names as $key=>$curr_field) {
if (!$no_field_quotes) $curr_field = "`".$curr_field."`";
$this->Expressions[$this->iterator][] = array($log_op, $curr_field, " = ", $this->MakeSafe($value[$key], $no_value_quotes));
}
} else {
if ($this->ThisTable) {
$check = $this->CheckField($field);
if (!isset($check["field"])) return false; else $field = $check["query"];
} else {
if (!$no_field_quotes) $field = "`".$field."`";
}
if ($this->DoReset("SEL") && $this->DoReset("UPD") && $this->DoReset("DEL") && $this->Status) {
// $no_field_quotes is now used to specify the SQL statement to prepare
$this->Clear();
if (is_string($no_field_quotes)) {
if ( strtolower(trim($no_field_quotes)) == "update") $this->Update($this->ThisTable);
elseif ( strtolower(trim($no_field_quotes)) == "delete") $this->Delete($this->ThisTable);
else $this->Query = "SELECT ";
} else {
$this->Query = "SELECT ";
}
}
$this->Expressions[$this->iterator][] = array($log_op, $field, " = ", $this->MakeSafe($value, $no_value_quotes));
}
return true;
}
// field != value, $log_op for the expression
// $log_op accepted inputs: AND, OR, NOT, XOR
// e.g. OR a != 'b' AND -> $log_op will be OR, not AND
// returns true
function NotEquals($log_op, $field, $value, $no_field_quotes = false, $no_value_quotes = false) {
if (!is_string($log_op)) return false; else $log_op = strtoupper(trim($log_op));
if ($log_op != "AND" && $log_op != "OR" && $log_op != "NOT" && $log_op != "XOR") return false;
// we'll need to auto-assign each value to the respective field if $value is passed as an array
if (is_array($value)) {
if ($this->ThisTable) return false; // obvious
// $field becomes the table name in this case
$field_names = $this->TableFields($field);
foreach ($field_names as $key=>$curr_field) {
if (!$no_field_quotes) $curr_field = "`".$curr_field."`";
$this->Expressions[$this->iterator][] = array($log_op, $curr_field, " != ", $this->MakeSafe($value[$key], $no_value_quotes));
}
} else {
if ($this->ThisTable) {
$check = $this->CheckField($field);
if (!isset($check["field"])) return false; else $field = $check["query"];
} else {
if (!$no_field_quotes) $field = "`".$field."`";
}
if ($this->DoReset("SEL") && $this->DoReset("UPD") && $this->DoReset("DEL") && $this->Status) {
// $no_field_quotes is now used to specify the SQL statement to prepare
$this->Clear();
if (is_string($no_field_quotes)) {
if ( strtolower(trim($no_field_quotes)) == "update") $this->Update($this->ThisTable);
elseif ( strtolower(trim($no_field_quotes)) == "delete") $this->Delete($this->ThisTable);
else $this->Query = "SELECT ";
} else {
$this->Query = "SELECT ";
}
}
$this->Expressions[$this->iterator][] = array($log_op, $field, " != ", $this->MakeSafe($value, $no_value_quotes));
}
return true;
}
// field < value, $log_op for the expression
// $log_op accepted inputs: AND, OR, NOT, XOR
// e.g. OR a < 'b' AND -> $log_op will be OR, not AND
// returns true
function Less($log_op, $field, $value, $no_field_quotes = false, $no_value_quotes = false) {
if (!is_string($log_op)) return false; else $log_op = strtoupper(trim($log_op));
if ($log_op != "AND" && $log_op != "OR" && $log_op != "NOT" && $log_op != "XOR") return false;
// we'll need to auto-assign each value to the respective field if $value is passed as an array
if (is_array($value)) {
if ($this->ThisTable) return false; // obvious
// $field becomes the table name in this case
$field_names = $this->TableFields($field);
foreach ($field_names as $key=>$curr_field) {
if (!$no_field_quotes) $curr_field = "`".$curr_field."`";
$this->Expressions[$this->iterator][] = array($log_op, $curr_field, " < ", $this->MakeSafe($value[$key], $no_value_quotes));
}
} else {
if ($this->ThisTable) {
$check = $this->CheckField($field);
if (!isset($check["field"])) return false; else $field = $check["query"];
} else {
if (!$no_field_quotes) $field = "`".$field."`";
}
if ($this->DoReset("SEL") && $this->DoReset("UPD") && $this->DoReset("DEL") && $this->Status) {
// $no_field_quotes is now used to specify the SQL statement to prepare
$this->Clear();
if (is_string($no_field_quotes)) {
if ( strtolower(trim($no_field_quotes)) == "update") $this->Update($this->ThisTable);
elseif ( strtolower(trim($no_field_quotes)) == "delete") $this->Delete($this->ThisTable);
else $this->Query = "SELECT ";
} else {
$this->Query = "SELECT ";
}
}
$this->Expressions[$this->iterator][] = array($log_op, $field, " < ", $this->MakeSafe($value, $no_value_quotes));
}
return true;
}
function lt($log_op, $field, $value, $no_field_quotes = false, $no_value_quotes = false) {
return $this->Less($log_op, $field, $value, $no_field_quotes, $no_value_quotes);
}
// field <= value, $log_op for the expression
// $log_op accepted inputs: AND, OR, NOT, XOR
// e.g. OR a <= 'b' AND -> $log_op will be OR, not AND
// returns true
function LessEquals($log_op, $field, $value, $no_field_quotes = false, $no_value_quotes = false) {
if (!is_string($log_op)) return false; else $log_op = strtoupper(trim($log_op));
if ($log_op != "AND" && $log_op != "OR" && $log_op != "NOT" && $log_op != "XOR") return false;
// we'll need to auto-assign each value to the respective field if $value is passed as an array
if (is_array($value)) {
if ($this->ThisTable) return false; // obvious
// $field becomes the table name in this case
$field_names = $this->TableFields($field);
foreach ($field_names as $key=>$curr_field) {
if (!$no_field_quotes) $curr_field = "`".$curr_field."`";
$this->Expressions[$this->iterator][] = array($log_op, $curr_field, " <= ", $this->MakeSafe($value[$key], $no_value_quotes));
}
} else {
if ($this->ThisTable) {
$check = $this->CheckField($field);
if (!isset($check["field"])) return false; else $field = $check["query"];
} else {
if (!$no_field_quotes) $field = "`".$field."`";
}
if ($this->DoReset("SEL") && $this->DoReset("UPD") && $this->DoReset("DEL") && $this->Status) {
// $no_field_quotes is now used to specify the SQL statement to prepare
$this->Clear();
if (is_string($no_field_quotes)) {
if ( strtolower(trim($no_field_quotes)) == "update") $this->Update($this->ThisTable);
elseif ( strtolower(trim($no_field_quotes)) == "delete") $this->Delete($this->ThisTable);
else $this->Query = "SELECT ";
} else {
$this->Query = "SELECT ";
}
}
$this->Expressions[$this->iterator][] = array($log_op, $field, " <= ", $this->MakeSafe($value, $no_value_quotes));
}
return true;
}
function lte($log_op, $field, $value, $no_field_quotes = false, $no_value_quotes = false) {
return $this->LessEquals($log_op, $field, $value, $no_field_quotes, $no_value_quotes);
}
// field > value, $log_op for the expression
// $log_op accepted inputs: AND, OR, NOT, XOR
// e.g. OR a > 'b' AND -> $log_op will be OR, not AND
// returns true
function Greater($log_op, $field, $value, $no_field_quotes = false, $no_value_quotes = false) {
if (!is_string($log_op)) return false; else $log_op = strtoupper(trim($log_op));
if ($log_op != "AND" && $log_op != "OR" && $log_op != "NOT" && $log_op != "XOR") return false;
// we'll need to auto-assign each value to the respective field if $value is passed as an array
if (is_array($value)) {
if ($this->ThisTable) return false; // obvious
// $field becomes the table name in this case
$field_names = $this->TableFields($field);
foreach ($field_names as $key=>$curr_field) {
if (!$no_field_quotes) $curr_field = "`".$curr_field."`";
$this->Expressions[$this->iterator][] = array($log_op, $curr_field, " > ", $this->MakeSafe($value[$key], $no_value_quotes));
}
} else {
if ($this->ThisTable) {
$check = $this->CheckField($field);
if (!isset($check["field"])) return false; else $field = $check["query"];
} else {
if (!$no_field_quotes) $field = "`".$field."`";
}
if ($this->DoReset("SEL") && $this->DoReset("UPD") && $this->DoReset("DEL") && $this->Status) {
// $no_field_quotes is now used to specify the SQL statement to prepare
$this->Clear();
if (is_string($no_field_quotes)) {
if ( strtolower(trim($no_field_quotes)) == "update") $this->Update($this->ThisTable);
elseif ( strtolower(trim($no_field_quotes)) == "delete") $this->Delete($this->ThisTable);
else $this->Query = "SELECT ";
} else {
$this->Query = "SELECT ";
}
}
$this->Expressions[$this->iterator][] = array($log_op, $field, " > ", $this->MakeSafe($value, $no_value_quotes));
}
return true;
}
function gt($log_op, $field, $value, $no_field_quotes = false, $no_value_quotes = false) {
return $this->Greater($log_op, $field, $value, $no_field_quotes, $no_value_quotes);
}
// field >= value, $log_op for the expression
// $log_op accepted inputs: AND, OR, NOT, XOR
// e.g. OR a >= 'b' AND -> $log_op will be OR, not AND
// returns true
function GreaterEquals($log_op, $field, $value, $no_field_quotes = false, $no_value_quotes = false) {
if (!is_string($log_op)) return false; else $log_op = strtoupper(trim($log_op));
if ($log_op != "AND" && $log_op != "OR" && $log_op != "NOT" && $log_op != "XOR") return false;
// we'll need to auto-assign each value to the respective field if $value is passed as an array
if (is_array($value)) {
if ($this->ThisTable) return false; // obvious
// $field becomes the table name in this case
$field_names = $this->TableFields($field);
foreach ($field_names as $key=>$curr_field) {
if (!$no_field_quotes) $curr_field = "`".$curr_field."`";
$this->Expressions[$this->iterator][] = array($log_op, $curr_field, " >= ", $this->MakeSafe($value[$key], $no_value_quotes));
}
} else {
if ($this->ThisTable) {
$check = $this->CheckField($field);
if (!isset($check["field"])) return false; else $field = $check["query"];
} else {
if (!$no_field_quotes) $field = "`".$field."`";
}
if ($this->DoReset("SEL") && $this->DoReset("UPD") && $this->DoReset("DEL") && $this->Status) {
// $no_field_quotes is now used to specify the SQL statement to prepare
$this->Clear();
if (is_string($no_field_quotes)) {
if ( strtolower(trim($no_field_quotes)) == "update") $this->Update($this->ThisTable);
elseif ( strtolower(trim($no_field_quotes)) == "delete") $this->Delete($this->ThisTable);
else $this->Query = "SELECT ";
} else {
$this->Query = "SELECT ";
}
}
$this->Expressions[$this->iterator][] = array($log_op, $field, " >= ", $this->MakeSafe($value, $no_value_quotes));
}
return true;
}
function gte($log_op, $field, $value, $no_field_quotes = false, $no_value_quotes = false) {
return $this->GreaterEquals($log_op, $field, $value, $no_field_quotes, $no_value_quotes);
}
// field LIKE value, $log_op for the expression
// $log_op accepted inputs: AND, OR, NOT, XOR
// if wildcard = 1, value will be appended with a % on the left, if wildcard = 2, value will be appended with a % on the right, if wildcard == 3, value will be appended with % on both sides
// e.g. OR a LIKE 'b' AND -> $log_op will be OR, not AND
// returns true
function Like($log_op, $field, $value, $no_field_quotes = false, $no_value_quotes = false, $wildcard = SQX_WILD_NONE) {
if (!is_string($log_op)) return false; else $log_op = strtoupper(trim($log_op));
if ($log_op != "AND" && $log_op != "OR" && $log_op != "NOT" && $log_op != "XOR") return false;
if ($wildcard == SQX_WILD_LEFT || $wildcard == SQX_WILD_BOTH) $value = "%".$value;
if ($wildcard == SQX_WILD_RIGHT || $wildcard == SQX_WILD_BOTH) $value = $value."%";
// we'll need to auto-assign each value to the respective field if $value is passed as an array
if (is_array($value)) {
if ($this->ThisTable) return false; // obvious
// $field becomes the table name in this case
$field_names = $this->TableFields($field);
foreach ($field_names as $key=>$curr_field) {
if (!$no_field_quotes) $curr_field = "`".$curr_field."`";
$this->Expressions[$this->iterator][] = array($log_op, $curr_field, " LIKE ", $this->MakeSafe($value[$key], $no_value_quotes));
}
} else {
if ($this->ThisTable) {
$check = $this->CheckField($field);
if (!isset($check["field"])) return false; else $field = $check["query"];
} else {
if (!$no_field_quotes) $field = "`".$field."`";
}
if ($this->DoReset("SEL") && $this->DoReset("UPD") && $this->DoReset("DEL") && $this->Status) {
// $no_field_quotes is now used to specify the SQL statement to prepare
$this->Clear();
if (is_string($no_field_quotes)) {
if ( strtolower(trim($no_field_quotes)) == "update") $this->Update($this->ThisTable);
elseif ( strtolower(trim($no_field_quotes)) == "delete") $this->Delete($this->ThisTable);
else $this->Query = "SELECT ";
} else {
$this->Query = "SELECT ";
}
}
$this->Expressions[$this->iterator][] = array($log_op, $field, " LIKE ", $this->MakeSafe($value, $no_value_quotes));
}
return true;
}
// field NOT LIKE value, $log_op for the expression
// $log_op accepted inputs: AND, OR, NOT, XOR
// if wildcard = 1, value will be appended with a % on the left, if wildcard = 2, value will be appended with a % on the right, if wildcard == 3, value will be appended with % on both sides
// e.g. OR a NOT LIKE 'b' AND -> $log_op will be OR, not AND
// returns true
function NotLike($log_op, $field, $value, $no_field_quotes = false, $no_value_quotes = false, $wildcard = SQX_WILD_NONE) {
if (!is_string($log_op)) return false; else $log_op = strtoupper(trim($log_op));
if ($log_op != "AND" && $log_op != "OR" && $log_op != "NOT" && $log_op != "XOR") return false;
if ($wildcard == SQX_WILD_LEFT || $wildcard == SQX_WILD_BOTH) $value = "%".$value;
if ($wildcard == SQX_WILD_RIGHT || $wildcard == SQX_WILD_BOTH) $value = $value."%";
// we'll need to auto-assign each value to the respective field if $value is passed as an array
if (is_array($value)) {
if ($this->ThisTable) return false; // obvious
// $field becomes the table name in this case
$field_names = $this->TableFields($field);
foreach ($field_names as $key=>$curr_field) {
if (!$no_field_quotes) $curr_field = "`".$curr_field."`";
$this->Expressions[$this->iterator][] = array($log_op, $curr_field, " NOT LIKE ", $this->MakeSafe($value[$key], $no_value_quotes));
}
} else {
if ($this->ThisTable) {
$check = $this->CheckField($field);
if (!isset($check["field"])) return false; else $field = $check["query"];
} else {
if (!$no_field_quotes) $field = "`".$field."`";
}
if ($this->DoReset("SEL") && $this->DoReset("UPD") && $this->DoReset("DEL") && $this->Status) {
// $no_field_quotes is now used to specify the SQL statement to prepare
$this->Clear();
if (is_string($no_field_quotes)) {
if ( strtolower(trim($no_field_quotes)) == "update") $this->Update($this->ThisTable);
elseif ( strtolower(trim($no_field_quotes)) == "delete") $this->Delete($this->ThisTable);
else $this->Query = "SELECT ";
} else {
$this->Query = "SELECT ";
}
}
$this->Expressions[$this->iterator][] = array($log_op, $field, " NOT LIKE ", $this->MakeSafe($value, $no_value_quotes));
}
return true;
}
// field IN value, $log_op for the expression
// $log_op accepted inputs: AND, OR, NOT, XOR
// e.g. OR a IN ('b') AND -> $log_op will be OR, not AND
// $field must be a string
// returns true
function In($log_op, $field, $value, $no_field_quotes = false, $no_value_quotes = false) {
if (!is_string($log_op)) return false; else $log_op = strtoupper(trim($log_op));
if ($log_op != "AND" && $log_op != "OR" && $log_op != "NOT" && $log_op != "XOR") return false;
if (!is_string($field)) return false;
// we'll need to auto-assign each value to the respective field if $value is passed as an array
if (is_array($value)) {
if ($this->ThisTable) return false; // obvious
$value_string = "( ";
foreach ($value as $key=>$curr_value) {
$value_string = $value_string.$this->MakeSafe($curr_value, $no_value_quotes).($key < count($value) - 1 ? ", " : "");
}
$value_string = $value_string." )";
if (!$no_field_quotes) $field = "`".$field."`";
$this->Expressions[$this->iterator][] = array($log_op, $field, " IN ", $value_string);
} else {
if ($this->ThisTable) {
$check = $this->CheckField($field);
if (!isset($check["field"])) return false; else $field = $check["query"];
} else {
if (!$no_field_quotes) $field = "`".$field."`";
}
if ($this->DoReset("SEL") && $this->DoReset("UPD") && $this->DoReset("DEL") && $this->Status) {
// $no_field_quotes is now used to specify the SQL statement to prepare
$this->Clear();
if (is_string($no_field_quotes)) {
if ( strtolower(trim($no_field_quotes)) == "update") $this->Update($this->ThisTable);
elseif ( strtolower(trim($no_field_quotes)) == "delete") $this->Delete($this->ThisTable);
else $this->Query = "SELECT ";
} else {
$this->Query = "SELECT ";
}
}
$this->Expressions[$this->iterator][] = array($log_op, $field, " IN ", $this->MakeSafe($value, $no_value_quotes));
}
return true;
}
function NotIn($log_op, $field, $value, $no_field_quotes = false, $no_value_quotes = false) {
if (!is_string($log_op)) return false; else $log_op = strtoupper(trim($log_op));
if ($log_op != "AND" && $log_op != "OR" && $log_op != "NOT" && $log_op != "XOR") return false;
if (!is_string($field)) return false;
// we'll need to auto-assign each value to the respective field if $value is passed as an array
if (is_array($value)) {
if ($this->ThisTable) return false; // obvious
$value_string = "( ";
foreach ($value as $key=>$curr_value) {
$value_string = $value_string.$this->MakeSafe($curr_value, $no_value_quotes).($key < count($value) - 1 ? ", " : "");
}
$value_string = $value_string." )";
if (!$no_field_quotes) $field = "`".$field."`";
$this->Expressions[$this->iterator][] = array($log_op, $field, " NOT IN ", $value_string);
} else {
if ($this->ThisTable) {
$check = $this->CheckField($field);
if (!isset($check["field"])) return false; else $field = $check["query"];
} else {
if (!$no_field_quotes) $field = "`".$field."`";
}
if ($this->DoReset("SEL") && $this->DoReset("UPD") && $this->DoReset("DEL") && $this->Status) {
// $no_field_quotes is now used to specify the SQL statement to prepare
$this->Clear();
if (is_string($no_field_quotes)) {
if ( strtolower(trim($no_field_quotes)) == "update") $this->Update($this->ThisTable);
elseif ( strtolower(trim($no_field_quotes)) == "delete") $this->Delete($this->ThisTable);
else $this->Query = "SELECT ";
} else {
$this->Query = "SELECT ";
}
}
$this->Expressions[$this->iterator][] = array($log_op, $field, " NOT IN ", $this->MakeSafe($value, $no_value_quotes));
}
return true;
}
// a "shortcut" for using Equals() in performing a simple join
// note that this condition is required in the WHERE clause, i.e. $log_op = "and"
// if $field1 is an array, $field2 must also be an array with at least the number of elements in $field1
// returns true
function Match($tblname1, $field1, $tblname2 = false, $field2 = false) {
if ($this->ThisTable) {
// check first: we could be joining new tables
$this->RefreshTables();
if (is_array($tblname1) && is_array($field1)) {
if (count($tblname1) < 2 || count($field1) < 2) return false;
$field2 = $field1[1];
$tblname2 = $field1[0];
$field1 = $tblname1[1];
$tblname1 = $tblname1[0];
if (!in_array($tblname1, $this->Tables, true) || !in_array($tblname2, $this->Tables, true)) return false;
if (is_array($field1)) {
$temp = $field1;
$field1 = array();
$temp_fields = $this->TableFields($tblname1);
foreach ($temp as $current) if (in_array($current, $temp_fields, true)) $field1[] = "`".$current."`";
$field1 = array_unique($field1);
if (count($field1) == 0) return false;
} else {
if (!in_array($field1, $this->TableFields($tblname1), true)) return false;
$field1 = "`".$field1."`";
}
if (is_array($field2)) {
$temp = $field2;
$field2 = array();
$temp_fields = $this->TableFields($tblname2);
foreach ($temp as $current) if (in_array($current, $temp_fields, true)) $field2[] = "`".$current."`";
$field2 = array_unique($field2);
if (count($field2) == 0) return false;
} else {
if (!in_array($field2, $this->TableFields($tblname2), true)) return false;
$field2 = "`".$field2."`";
}
// check that this setting is either the first or the immediate one following the first one
foreach ($this->TableJoin as $key => $current)
if ($current["jt"] === $tblname1 && $key != count($this->TableJoin) - 1) return false;
foreach ($this->TableJoin as $key => $current)
if ($current["jt"] === $tblname2 && $key != count($this->TableJoin) - 1) return false;
if ($this->DoReset("SEL") && $this->Status) {
$this->Clear();
$this->Query = "SELECT ";
}
$this->TableJoin[] = array("jt" => "`".$tblname1."`", "jf" => null, "st" => -1);
$this->TableJoin[] = array("jt" => "`".$tblname2."`", "jf" => null, "st" => -1);
$tblname2 = "`".$tblname2."`";
}
} else {
if (!is_string($tblname1) && !is_numeric($tblname1)) return false;
if (!is_string($tblname2) && !is_numeric($tblname2)) return false;
if (!is_string($field2) && !is_numeric($field2) && !is_array($field2)) return false;
}
if (is_array($field1)) {
if (!is_array($field2)) return false;
if (count($field2) < count($field1)) return false;
foreach($field1 as $key => $curr_field) {
$this->Equals("and", $tblname1.".".$curr_field, $tblname2.".".$field2[$key], SQX_NO_QUOTES, SQX_NO_QUOTES);
}
} else {
if (is_array($field2)) return false;
$this->Equals("and", $tblname1.".".$field1, $tblname2.".".$field2, SQX_NO_QUOTES, SQX_NO_QUOTES);
}
}
/*
// **********************************************************************
// END Logical Operator Functions
// START Insertion and Selection Functions
// **********************************************************************
*/
// assign fields and values to set
// returns true unless $value is false and we are not using VALUES for INSERT / REPLACE
function Set($field, $value = false, $no_field_quotes = false, $no_value_quotes = false) {
// we'll need to auto-assign each value to the respective field if $value is passed as an array
if (is_array($value)) {
if ($this->ThisTable) return false; // shouldn't work if object is binded to a table
// $field becomes the table name in this case
$field_names = $this->TableFields($field);
foreach ($field_names as $key=>$curr_field) {
if (!$no_field_quotes) $curr_field = "`".$curr_field."`";
$this->Set[] = array($curr_field, $this->MakeSafe($value[$key], $no_value_quotes));
}
} else {
if (count($this->Value) == 0 || $this->Duplicate != -1) {
if ($value === false) return false;
if ($this->ThisTable && $this->Status) {
$check = $this->CheckField($field);
if (!$check || !isset($check["field"])) return false;
$field = $check["query"];
} else {
// error if field is not a string
if (!is_string($field) && !is_numeric($field)) return false;
if (!$no_field_quotes) $field = "`".$field."`";
}
if ($this->DoReset("INS") && $this->DoReset("REP") && $this->DoReset("UPD") && $this->Status) {
if (is_string($no_field_quotes)) {
// $no_field_quotes becomes the $mode equivalent for Values()
// remember: UPDATE uses SET but not VALUES, so we need to permit UPDATE here
if (strtolower(trim($no_field_quotes)) == "replace") $this->Replace($this->ThisTable, false, false);
elseif (strtolower(trim($no_field_quotes)) == "update") $this->Update($this->ThisTable, false, false);
else $this->Insert($this->ThisTable, false, false);
} else {
$this->Insert($this->ThisTable, false, false);
}
}
$this->Set[] = array($field, $this->MakeSafe($value, $no_value_quotes));
} else {
// need not DoReset() as these statements takes the usage of Values() as a pre-condition already
// $field will contain values
if (is_array($field)) {
foreach ($field as $current) $this->Set[] = array(null, $this->MakeSafe($current, $value));
} else {
$this->Set[] = array(null, $this->MakeSafe($field, $value));
}
}
}
return true;
}
// prepares VALUES (...) for INSERT / REPLACE statements
// returns true unless $values is not an array or any elements of the argument is NULL
function Values($values = false, $mode = "insert") {
if (!is_array($values) && $values !== false) return false;
if ($this->DoReset("INS") && $this->DoReset("REP") && $this->Status) {
if (is_string($mode)) {
if (strtolower(trim($mode)) == "replace") $this->Replace($this->ThisTable, false, false);
else $this->Insert($this->ThisTable, false, false);
} else {
$this->Insert($this->ThisTable, false, false);
}
}
if ($values) {
if (!in_array(null, $values, true)) {
if ($this->ThisTable && $this->Status) {
$temp = array();
foreach ($values as $curr_val) {
$check = $this->CheckField($curr_val, false);
if (!$check || isset($check["table"])) return false; else $temp[] = $check["query"];
}
$values = $temp;
}
$this->Value = $values;
} else {
return false;
}
} else {
$this->Value[] = null;
$this->Value[] = str_replace("`", "", trim(substr($this->Query, strpos($this->Query, "INTO") + 5)));
}
return true;
}
// sets the USING clause for DELETE statement
// returns false if it's not a DELETE statement or does not contain multiple tables
// (since USING is only applicable for multiple tables), else true
function Using($tblnames, $no_quotes = false) {
if (substr($this->Query, 0, 3) != "DEL" || substr($this->Query, -1, 1) != "1" || $this->ThisTable) return false;
$this->Query = substr($this->Query, 0, strlen($this->Query) - 1);
$this->Query = $this->Query." USING ";
if (is_array($tblnames)) {
foreach ($tblnames as $key => $curr_table) {
$this->Query = $this->Query.(!$no_quotes ? "`" : "").$curr_table.(!$no_quotes ? "`" : "");
$this->Query = $this->Query.($key < count($tblnames) - 1 ? ", " : "");
}
} else {
$this->Query = $this->Query.(!$no_quotes ? "`" : "").$tblnames.(!$no_quotes ? "`" : "");
}
$this->Query = $this->Query."1";
return true;
}
// performs a JOIN clause
// if $join_field is not speicifed, USING will be used instead of ON [col1] = [col2]
// returns true
function Join($originating_table, $originating_field = false, $join_table = false, $join_field = false, $subtype = SQX_JOIN) {
// $originating_field becomes the subtype if object is binded to a table
switch (($this->ThisTable ? $originating_field : $subtype)) {
case SQX_LEFT:
$subtype = " ```LEFT";
break;
case SQX_RIGHT:
$subtype = " ```RIGHT";
break;
case SQX_INNER:
$subtype = " ```INNER";
break;
case SQX_LEFT_INNER:
$subtype = " ```LEFT INNER";
break;
case SQX_RIGHT_INNER:
$subtype = " ```RIGHT INNER";
break;
case SQX_OUTER:
$subtype = " ```OUTER";
break;
case SQX_LEFT_OUTER:
$subtype = " ```LEFT OUTER";
break;
case SQX_RIGHT_OUTER:
$subtype = " ```RIGHT OUTER";
break;
default:
if ($this->ThisTable) $subtype = " ```"; else $subtype = "";
break;
}
if ($this->ThisTable) {
// check first: we could be joining new tables
$this->RefreshTables();
// $originating_table is either an array from JoinTo() or this table's field to join to the previous/next table
// if it's the latter, we'll "convert" it into an array via JoinTo()
$join = $originating_table;
if (is_string($join) || is_numeric($join)) $join = $this->JoinTo($join);
if (count($join) < 2) return false;
$j_table = $join[0];
if (!in_array($j_table, $this->Tables, true)) return false;
if (is_array($join[1])) {
$j_field = array();
$temp_fields = $this->TableFields($j_table);
foreach ($join[1] as $temp) if (in_array($temp, $temp_fields, true)) $j_field[] = $temp;
$j_field = array_unique($j_field);
if (count($j_field) == 0) return false;
} else {
if (!in_array($join[1], $this->TableFields($j_table), true)) return false;
$j_field = $join[1];
}
if ($this->DoReset("SEL") && $this->Status) {
$this->Clear();
$this->Query = "SELECT ";
}
// check that this setting is either the first or the immediate one following the first one
foreach ($this->TableJoin as $k => $c) if ($c["jt"] == $j_table && $k != count($this->TableJoin) - 1) return false;
$this->TableJoin[] = array("jt" => $j_table, "jf" => $j_field, "st" => $subtype);
$this->JoinColumns[$j_table] = $this->TableFields($j_table, true); // force a "refresh" of table columns
if ($join_table) $this->Alias($j_table, $join_table); // add alias for current table if specified
if (isset($join[2]) && $join[2]) if ( !( $this->Join( array($j_table, $join[2]) ) ) ) return false;
return true; // we'll only append the JOIN clauses during BuildQuery()
}
if (!is_string($originating_table) && !is_numeric($originating_table)) return false;
if (!is_string($join_table) && !is_numeric($join_table)) return false;
if (is_array($originating_field)) {
$check_join_field = is_array($join_field) && (count($originating_field) > count($join_field));
if ($check_join_field || (!is_array($join_field) && $join_field != false)) return false;
} else {
if (is_array($join_field)) return false;
}
$this->Query = $this->Query.$subtype." JOIN `".$join_table."` ";
if ($join_field != false) {
$this->Query = $this->Query." ON ( `";
if (is_array($originating_field)) {
foreach ($originating_field as $key => $curr_field) {
$this->Query = $this->Query.$originating_table."`.`".$curr_field."` = `".$join_table."`.`";
$this->Query = $this->Query.$join_field.($key == count($originating_field) - 1 ? "" : "`, ");
}
} else {
$this->Query = $this->Query.$originating_table."`.`".$originating_field."` = `".$join_table."`.`".$join_field;
}
$this->Query = $this->Query."` ) ";
} else {
if (is_array($originating_field)) {
$this->Query = $this->Query."USING ( `".implode("`, `", $originating_field)."` ) ";
} else {
$this->Query = $this->Query."USING ( `".$originating_field."` ) ";
}
}
return true;
}
// for 1.0.0: A "friendlier" way to join tables that are binded to objects
// returns an array in the form array(ThisTable, array(field1, field2...)) if successful
function JoinTo($field1, $field2 = false) {
if ((!is_string($field1) && !is_numeric($field1) && !is_array($field1)) || $this->ThisTable === null) return false;
$results = array($this->ThisTable);
if (is_array($field1)) {
$check1 = array();
foreach ($field1 as $current) {
$c = $this->CheckField($current, false);
if (isset($c["field"])) $check1[] = $c["field"];
}
$check1 = array_unique($check1);
if (count($check1) == 0) return false;
} else {
$check1 = $this->CheckField($field1, false);
if (!isset($check1["field"])) return false;
$check1 = array($check1["field"]);
}
$results[] = $check1;
if (is_array($field2)) {
$check2 = array();
foreach ($field2 as $current) {
$c = $this->CheckField($current, false);
if (isset($c["field"])) $check2[] = $c["field"];
}
$check2 = array_unique($check2);
if (count($check2) == 0) return false;
$results[] = $check2;
} elseif (is_string($field2) || is_numeric($field2)) {
$check2 = $this->CheckField($field2, false);
if (!isset($check2["field"])) return false;
$results[] = array($check2["field"]);
}
return $results;
}
// performs a GROUP BY clause
// returns true
function GroupBy($fields, $rollup = false) {
if ($this->DoReset("SEL") && $this->Status) {
$this->Clear();
$this->Query = "SELECT ";
}
if ($this->ThisTable) {
if (is_array($fields)) {
$fields2 = array();
foreach ($fields as $current) {
$check = $this->CheckField($fields);
if (!isset($check["field"])) return false; else $fields2[] = $check["query"];
}
$fields = $fields2;
} else {
$check = $this->CheckField($fields);
if (!isset($check["field"])) return false; else $fields = $check["query"];
}
}
$this->GroupBy = array( (is_array($fields) ? implode(", ", $fields) : $fields), $rollup);
return true;
}
// performs a HAVING clause by setting $this->Having = $iterator
// returns true
function Having() {
if ($this->GroupBy === false) return false;
$this->iterator = count($this->Expressions);
if ($this->iterator == 0) $this->iterator = 1;
$this->Having = $this->iterator;
return true;
}
// "closes" the conditions used for the Having clause
// returns true
function CloseHaving() {
if ($this->Having > 0) $this->iterator = $this->Having - 1; else $this->iterator = 0;
return true;
}
// sets an order by field in ascending order
// returns true
function OrderAsc($field, $no_quotes = false, $mode = false) {
if ($this->DoReset("SEL") && $this->DoReset("UPD") && $this->DoReset("DEL") && $this->Status) {
$this->Clear();
if ($mode) {
if ( strtolower(trim($mode)) == "select" ) $this->Query = "SELECT ";
elseif ( strtolower(trim($mode)) == "update") $this->Update($this->ThisTable);
elseif ( strtolower(trim($mode)) == "delete") $this->Delete($this->ThisTable);
} else {
$this->Query = "SELECT ";
}
}
if (is_array($field)) {
foreach ($field as $current) {
if ($this->ThisTable) {
$check = $this->CheckField($current);
if (!isset($check["field"])) return false; else $current = $check["query"];
$no_quotes = true;
}
$this->Order[] = array((!$no_quotes ? "`" : "").$current.(!$no_quotes ? "`" : ""), " ASC");
}
} else {
if ($this->ThisTable) {
$check = $this->CheckField($field);
if (!isset($check["field"])) return false; else $field = $check["query"];
$no_quotes = true;
}
$this->Order[] = array((!$no_quotes ? "`" : "").$field.(!$no_quotes ? "`" : ""), " ASC");
}
return true;
}
function asc($field, $no_quotes = false, $mode = false) {
return $this->OrderAsc($field, $no_quotes, $mode);
}
// sets an order by field in descending order
// returns true
function OrderDesc($field, $no_quotes = false) {
if ($this->DoReset("SEL") && $this->DoReset("UPD") && $this->DoReset("DEL") && $this->Status) {
$this->Clear();
if ($no_quotes) {
if ( strtolower(trim($no_quotes)) == "select" ) $this->Query = "SELECT ";
elseif ( strtolower(trim($no_quotes)) == "update") $this->Update($this->ThisTable);
elseif ( strtolower(trim($no_quotes)) == "delete") $this->Delete($this->ThisTable);
} else {
$this->Query = "SELECT ";
}
}
if (is_array($field)) {
foreach ($field as $current) {
if ($this->ThisTable) {
$check = $this->CheckField($current);
if (!isset($check["field"])) return false; else $current = $check["query"];
$no_quotes = true;
}
$this->Order[] = array((!$no_quotes ? "`" : "").$current.(!$no_quotes ? "`" : ""), " DESC");
}
} else {
if ($this->ThisTable) {
$check = $this->CheckField($field);
if (!isset($check["field"])) return false; else $field = $check["query"];
$no_quotes = true;
}
$this->Order[] = array((!$no_quotes ? "`" : "").$field.(!$no_quotes ? "`" : ""), " DESC");
}
return true;
}
function desc($field, $no_quotes = false) {
return $this->OrderDesc($field, $no_quotes);
}
// sets the upper limit (lower limit defaults to 0) if $limit is an integer > 0
// if $limit is an array, a check is done to make sure the first element >= 0 and the second element is >= 1
// if $limit is valid, function returns true, else false
function Limit($limits, $limit2 = false, $mode = false) {
if ($limit2 && is_array($limits)) return false;
if (is_array($limits)) {
if (count($limits) < 2) return false;
if (intval($limits[0]) < 0 || intval($limits[1]) < 1) return false; else $set_limit = $limits;
} else {
if (!$limit2) {
if (intval($limits) < 1) return false; else $set_limit = $limits;
} else {
if (intval($limits) < 0 || intval($limit2) < 1) return false; else $set_limit = array($limits, $limit2);
}
}
if ($this->DoReset("SEL") && $this->DoReset("UPD") && $this->DoReset("DEL") && $this->Status) {
// $mode is now used to specify the SQL statement to prepare
$this->Clear();
if (is_string($mode)) {
if ( strtolower(trim($mode)) == "update") $this->Update($this->ThisTable);
elseif ( strtolower(trim($mode)) == "delete") $this->Delete($this->ThisTable);
else $this->Query = "SELECT ";
} else {
$this->Query = "SELECT ";
}
}
$this->Limit = $set_limit;
return true;
}
function lim($limits, $limit2 = false, $mode = false) {
return $this->Limit($limits, $limit2, $mode);
}
/*
// **********************************************************************
// END Insertion and Selection Functions
// START Data Type Functions
// **********************************************************************
*/
// Sets last field as the primary key (unles $set = false), overrides AllowNull()
// Will define the field with the auto_increment attribute by default
// returns true
function PrimaryKey($auto_increment = true, $name = false) {
if (count($this->Fields) == 0) return false;
if ($this->DoReset("ALT") && !(strlen($this->Query) > 9 && substr($this->Query, 3, 6) == "ATE TA")) $this->AlterTable();
$this->Fields[count($this->Fields) - 1]["primary key"] = $name;
if ($auto_increment) $this->Fields[count($this->Fields) - 1]["autoinc"] = true;
return true;
}
function pk($auto_increment = true, $name = false) {
return $this->PrimaryKey($auto_increment, $name);
}
// Sets last field to be unique
// Accepts an optional parameter to set field as auto_increment
// returns true
function Unique($name = false) {
if (count($this->Fields) == 0) return false;
if ($this->DoReset("ALT") && !(strlen($this->Query) > 9 && substr($this->Query, 3, 6) == "ATE TA")) $this->AlterTable();
$this->Fields[count($this->Fields) - 1]["unique"] = $name;
return true;
}
// Sets last field to have an index
// returns true
function Index($name = false) {
if (count($this->Fields) == 0) return false;
if ($this->DoReset("ALT") && !(strlen($this->Query) > 9 && substr($this->Query, 3, 6) == "ATE TA")) $this->AlterTable();
$this->Fields[count($this->Fields) - 1]["index"] = $name;
return true;
}
// Sets last field to allow null (by default NOT NULL)
// returns false if last field was primary key, else returns true
function AllowNull() {
if (count($this->Fields) == 0) return false;
if (isset($this->Fields[count($this->Fields) - 1]["primary_key"])) return false;
if ($this->DoReset("ALT") && !(strlen($this->Query) > 9 && substr($this->Query, 3, 6) == "ATE TA")) $this->AlterTable();
$this->Fields[count($this->Fields) - 1]["null"] = true;
return true;
}
// Sets last field's Length
// if last field type is Double, $length must be an array: [0] = total number of digits (M), [1] = decimal places (D)
// if last field type is Float, $length can also not be an array, i.e. equiv. to FLOAT(p); from documentation:
// p represents the precision in bits,
// but MySQL uses this value only to determine whether to use FLOAT or DOUBLE for the resulting data type.
// If p is from 0 to 24, the data type becomes FLOAT with no M or D values.
// If p is from 25 to 53, the data type becomes DOUBLE with no M or D values.
// returns false if last field type is Enum/Set, else returns true
function SetLength($length) {
if (count($this->Fields) == 0) return false;
if ($this->Fields[count($this->Fields) - 1]["vartype"] == "ENS") return false;
if (is_array($length)) {
$check = $this->Fields[count($this->Fields) - 1]["cast"] == "DOUBLE";
$check = $check || $this->Fields[count($this->Fields) - 1]["cast"] == "FLOAT";
$check = $check || $this->Fields[count($this->Fields) - 1]["cast"] == "DECIMAL";
if ($check) $this->Fields[count($this->Fields) - 1]["length"] = $length; else return false;
} elseif (is_numeric($length)) {
if ($this->Fields[count($this->Fields) - 1]["cast"] == "DOUBLE") {
return false;
} else {
$this->Fields[count($this->Fields) - 1]["length"] = intval($length);
}
} else {
return false;
}
if ($this->DoReset("ALT") && !(strlen($this->Query) > 9 && substr($this->Query, 3, 6) == "ATE TA")) $this->AlterTable();
return true;
}
function length($length) {
return $this->SetLength($length);
}
// Sets last field as a foreign key, referencing to the $field under the $table
// by default, ON DELETE and ON UPDATE will CASCADE
// returns true
function ForeignKey($table, $field = false, $on_delete = false, $on_update = false, $name = false) {
if (count($this->Fields) == 0) return false;
if (!is_array($table) && !is_string($table) && !is_numeric($table)) return false;
if (is_array($table)) {
if (count($table) < 2) return false;
$name = $on_update;
$on_update = $on_delete;
$on_delete = $field;
$field = $table[1][0];
if (!is_string($field) && !is_numeric($field)) return false;
$table = $table[0];
if (!is_string($table) && !is_numeric($table)) return false;
if (!in_array($field, $this->TableFields($table), true)) return false;
}
if ($this->DoReset("ALT") && !(strlen($this->Query) > 9 && substr($this->Query, 3, 6) == "ATE TA")) $this->AlterTable();
$this->Fields[count($this->Fields) - 1]["foreign key"] = array($table, $field, $on_delete, $on_update, $name);
return true;
}
function fk($table, $field = false, $on_delete = false, $on_update = false, $name = false) {
return $this->ForeignKey($table, $field, $on_delete, $on_update, $name);
}
// Sets a new Primary Key of Integer type with auto_increment
// returns true
function PKI($field, $name = false) {
if (!is_string($field) && !is_numeric($field)) return false;
if ($this->DoReset("ALT") && !(strlen($this->Query) > 9 && substr($this->Query, 3, 6) == "ATE TA")) $this->AlterTable();
$this->Fields[]["vartype"] = "INT";
$this->Fields[count($this->Fields) - 1]["cast"] = "INT";
$this->Fields[count($this->Fields) - 1]["name"] = $field;
$this->Fields[count($this->Fields) - 1]["primary key"] = $name;
$this->Fields[count($this->Fields) - 1]["autoinc"] = true;
return true;
}
// Sets a new "unpacked" Decimal field
// $unsigned = 1: unsigned (default), 2: unsigned zerofill
// returns true
function TypeDecimal($name, $default = false, $unsigned = false) {
if ($this->DoReset("ALT") && !(strlen($this->Query) > 9 && substr($this->Query, 3, 6) == "ATE TA")) $this->AlterTable();
$this->Fields[]["vartype"] = "DEC";
$this->Fields[count($this->Fields) - 1]["cast"] = "DECIMAL";
$this->Fields[count($this->Fields) - 1]["name"] = $name;
$this->Fields[count($this->Fields) - 1]["default"] = $default;
$this->Fields[count($this->Fields) - 1]["unsigned"] = $unsigned;
return true;
}
function dec($name, $default = false, $unsigned = false) {
return $this->TypeDecimal($name, $default, $unsigned);
}
// Sets a new Integer field
// $subtype = 1: tiny, 2: small, 3: medium, 5: big, 6: normal (default)
// $unsigned = 1: unsigned (default), 2: unsigned zerofill
// returns true
function TypeInteger($name, $subtype = SQX_DEF, $default = false, $unsigned = false) {
if ($this->DoReset("ALT") && !(strlen($this->Query) > 9 && substr($this->Query, 3, 6) == "ATE TA")) $this->AlterTable();
$this->Fields[]["vartype"] = "INT";
switch ($subtype) {
case SQX_TINY:
$this->Fields[count($this->Fields) - 1]["cast"] = "TINYINT";
break;
case SQX_SMALL:
$this->Fields[count($this->Fields) - 1]["cast"] = "SMALLINT";
break;
case SQX_MEDIUM:
$this->Fields[count($this->Fields) - 1]["cast"] = "MEDIUMINT";
break;
case SQX_BIG:
$this->Fields[count($this->Fields) - 1]["cast"] = "BIGINT";
break;
default:
$this->Fields[count($this->Fields) - 1]["cast"] = "INT";
break;
}
$this->Fields[count($this->Fields) - 1]["name"] = $name;
$this->Fields[count($this->Fields) - 1]["default"] = $default;
$this->Fields[count($this->Fields) - 1]["unsigned"] = $unsigned;
return true;
}
function intg($name, $subtype = SQX_DEF, $default = false, $unsigned = false) {
return $this->TypeInteger($name, $subtype, $default, $unsigned);
}
// Sets a new Float/Double field
// $subtype = 1: float, 2: double (default)
// $unsigned = 1: unsigned (default), 2: unsigned zerofill
// returns true
function TypePrecision($name, $subtype = SQX_DOUBLE, $default = false, $unsigned = false) {
if ($this->DoReset("ALT") && !(strlen($this->Query) > 9 && substr($this->Query, 3, 6) == "ATE TA")) $this->AlterTable();
$this->Fields[]["vartype"] = "PRE";
if ($subtype == SQX_FLOAT) {
$this->Fields[count($this->Fields) - 1]["cast"] = "FLOAT";
} else {
$this->Fields[count($this->Fields) - 1]["cast"] = "DOUBLE";
}
$this->Fields[count($this->Fields) - 1]["name"] = $name;
$this->Fields[count($this->Fields) - 1]["default"] = $default;
$this->Fields[count($this->Fields) - 1]["unsigned"] = $unsigned;
return true;
}
function precision($name, $subtype = SQX_DOUBLE, $default = false, $unsigned = false) {
return $this->TypePrecision($name, $subtype, $default, $unsigned);
}
// Sets a new Date field
// $subtype = 1: datetime, 2: date, 3: time, 4: year, 5: timestamp (default)
// $default used to set the format for timestamp
// $length applicable for $subtype = 5 (timestamp)
// returns true
function TypeChrono($name, $subtype = SQX_TS, $default = false) {
if ($this->DoReset("ALT") && !(strlen($this->Query) > 9 && substr($this->Query, 3, 6) == "ATE TA")) $this->AlterTable();
$this->Fields[]["vartype"] = "CHR";
switch ($subtype) {
case SQX_DT:
$this->Fields[count($this->Fields) - 1]["cast"] = "DATETIME";
break;
case SQX_DATE:
$this->Fields[count($this->Fields) - 1]["cast"] = "DATE";
break;
case SQX_TIME:
$this->Fields[count($this->Fields) - 1]["cast"] = "TIME";
break;
case SQX_YEAR:
$this->Fields[count($this->Fields) - 1]["cast"] = "YEAR";
break;
default:
$this->Fields[count($this->Fields) - 1]["cast"] = "TIMESTAMP";
break;
}
$this->Fields[count($this->Fields) - 1]["name"] = $name;
$this->Fields[count($this->Fields) - 1]["default"] = $default;
return true;
}
function chrono($name, $subtype = SQX_TS, $default = false) {
return $this->TypeChrono($name, $subtype, $default);
}
// Sets a new Binary field
// $subtype = 1: binary, 2: varbinary (default)
// returns true
function TypeBinary($name, $subtype = SQX_VAR, $default = false) {
if ($this->DoReset("ALT") && !(strlen($this->Query) > 9 && substr($this->Query, 3, 6) == "ATE TA")) $this->AlterTable();
$this->Fields[]["vartype"] = "BIN";
if ($subtype == SQX_NORM) {
$this->Fields[count($this->Fields) - 1]["cast"] = "BINARY";
} else {
$this->Fields[count($this->Fields) - 1]["cast"] = "VARBINARY";
}
$this->Fields[count($this->Fields) - 1]["name"] = $name;
$this->Fields[count($this->Fields) - 1]["default"] = $default;
return true;
}
function bin($name, $subtype = SQX_VAR, $default = false) {
return $this->TypeBinary($name, $subtype, $default);
}
// Sets a new Blob field
// $subtype = 1: tiny, 3: medium, 4: long, 6: normal (default)
// $length applicable only for $subtype = 2 (normal)
// returns true
function TypeBlob($name, $subtype = SQX_DEF) {
if ($this->DoReset("ALT") && !(strlen($this->Query) > 9 && substr($this->Query, 3, 6) == "ATE TA")) $this->AlterTable();
$this->Fields[]["vartype"] = "BLO";
switch ($subtype) {
case SQX_TINY:
$this->Fields[count($this->Fields) - 1]["cast"] = "TINYBLOB";
break;
case SQX_MEDIUM:
$this->Fields[count($this->Fields) - 1]["cast"] = "MEDIUMBLOB";
break;
case SQX_LONG:
$this->Fields[count($this->Fields) - 1]["cast"] = "LONGBLOB";
break;
default:
$this->Fields[count($this->Fields) - 1]["cast"] = "BLOB";
break;
}
$this->Fields[count($this->Fields) - 1]["name"] = $name;
return true;
}
function blob($name, $subtype) {
return $this->TypeBlob($name, $subtype);
}
// Sets a new Char field
// $subtype = 1: char, 2: varchar (default)
// returns true
function TypeChar($name, $subtype = SQX_VAR, $default = false, $char_set = false, $collate = false, $fulltext = false) {
if ($this->DoReset("ALT") && !(strlen($this->Query) > 9 && substr($this->Query, 3, 6) == "ATE TA")) $this->AlterTable();
$this->Fields[]["vartype"] = "CHA";
if ($subtype == SQX_NORM) {
$this->Fields[count($this->Fields) - 1]["cast"] = "CHAR";
} else {
$this->Fields[count($this->Fields) - 1]["cast"] = "VARCHAR";
}
$this->Fields[count($this->Fields) - 1]["name"] = $name;
$this->Fields[count($this->Fields) - 1]["default"] = $default;
$this->Fields[count($this->Fields) - 1]["char_set"] = $char_set;
$this->Fields[count($this->Fields) - 1]["collate"] = $collate;
if (is_string($fulltext) || is_numeric($fulltext)) $this->Fields[count($this->Fields) - 1]["fulltext"] = $fulltext;
return true;
}
function char($name, $subtype = SQX_VAR, $default = false, $char_set = false, $collate = false, $fulltext = false) {
return $this->TypeChar($name, $subtype, $default, $char_set, $collate, $fulltext);
}
// Sets a new Text field
// $subtype = 1: tiny, 3: medium, 4: long, 6 = normal (default)
// $length applicable only for $subtype = 2 (normal)
// returns true
function TypeText($name, $subtype = SQX_DEF, $char_set = false, $collate = false, $fulltext = false) {
if ($this->DoReset("ALT") && !(strlen($this->Query) > 9 && substr($this->Query, 3, 6) == "ATE TA")) $this->AlterTable();
$this->Fields[]["vartype"] = "TEX";
switch ($subtype) {
case SQX_TINY:
$this->Fields[count($this->Fields) - 1]["cast"] = "TINYTEXT";
break;
case SQX_MEDIUM:
$this->Fields[count($this->Fields) - 1]["cast"] = "MEDIUMTEXT";
break;
case SQX_LONG:
$this->Fields[count($this->Fields) - 1]["cast"] = "LONGTEXT";
break;
default:
$this->Fields[count($this->Fields) - 1]["cast"] = "TEXT";
break;
}
$this->Fields[count($this->Fields) - 1]["name"] = $name;
$this->Fields[count($this->Fields) - 1]["char_set"] = $char_set;
$this->Fields[count($this->Fields) - 1]["collate"] = $collate;
if (is_string($fulltext) || is_numeric($fulltext)) $this->Fields[count($this->Fields) - 1]["fulltext"] = $fulltext;
return true;
}
function text($name, $subtype = SQX_DEF, $char_set = false, $collate = false, $fulltext = false) {
return $this->TypeText($name, $subtype, $char_set, $collate, $fulltext);
}
// Sets a new Enum/Set field
// $subtype = 1: Enum (default), 2: Set
// returns true
function TypeEnumSet($name, $subtype = SQX_ENUM, $default = array("S", "Q", "X"), $char_set = false, $collate = false) {
if ($this->DoReset("ALT") && !(strlen($this->Query) > 9 && substr($this->Query, 3, 6) == "ATE TA")) $this->AlterTable();
$this->Fields[]["vartype"] = "ENS";
if ($subtype == SQX_SET) {
$this->Fields[count($this->Fields) - 1]["cast"] = "SET";
} else {
$this->Fields[count($this->Fields) - 1]["cast"] = "ENUM";
}
$this->Fields[count($this->Fields) - 1]["name"] = $name;
$this->Fields[count($this->Fields) - 1]["default"] = $default;
$this->Fields[count($this->Fields) - 1]["char_set"] = $char_set;
$this->Fields[count($this->Fields) - 1]["collate"] = $collate;
return true;
}
function enums($name, $subtype = SQX_ENUM, $default = array("S", "Q", "X"), $char_set = false, $collate = false) {
return $this->TypeEnumSet($name, $subtype, $default, $char_set, $collate);
}
/*
// **********************************************************************
// END Data Type Functions
// START Alter Table Functions
// **********************************************************************
*/
// arranges added columns for ALTER TABLE
// returns false if $column is not a string
// else returns true
function Arrange($column = false) {
if ($this->ThisTable) {
if ($this->Status === null) return false; // obvious
$check = $this->CheckField($column);
if (!$check || !isset($check["field"])) return false; // obvious
}
if (!$column) {
$this->Arrangement[count($this->Fields) - 1] = "FIRST ";
} else {
if (!is_string($column) && !is_numeric($column)) return false;
$this->Arrangement[count($this->Fields) - 1] = "AFTER `".trim($column)."` ";
}
if ($this->DoReset("ALT")) $this->AlterTable();
return true;
}
// performs DROP for ALTER TABLE
// checks for any non-string argument for $column (except for false when SQX_PRIMARY_KEY is passed)
// and non-numeric argument for $drop_type, if so returns false
// else returns true
function Drop($column, $drop_type = -1) {
if ($this->ThisTable) {
if ($this->Status === null) return false; // obvious
$check = $this->CheckField($column);
if (!$check || !isset($check["field"])) return false; // obvious
}
if ($this->DoReset("ALT")) $this->AlterTable();
if (!$column) {
$this->Drop[] = "PRIMARY KEY";
} else {
if (!is_string($column) && !is_numeric($column)) {
return false;
} else {
switch ($drop_type) {
case SQX_COLUMN:
$this->Drop[] = "COLUMN `".trim($column)."`";
break;
case SQX_INDEX:
$this->Drop[] = "INDEX ".trim($column);
break;
case SQX_FOREIGN_KEY:
$this->Drop[] = "FOREIGN KEY ".trim($column);
break;
}
}
}
return true;
}
// performs CHANGE for ALTER TABLE
// usage: Change("oldcol"); TypeInteger("newcol");
// will give ... CHANGE oldcol newcol INT NOT NULL
// returns true unless $colname is not a string.
function Change($column) {
if ($this->ThisTable) {
if ($this->Status === null) return false; // obvious
$check = $this->CheckField($column);
if (!$check || !isset($check["field"])) return false; // obvious
}
if (!is_string($column) && !is_numeric($column)) return false;
if ($this->DoReset("ALT")) $this->AlterTable();
$this->Change[count($this->Fields)] = "`".$column."`";
return true;
}
// changes character set for ALTER TABLE
// checks for any non-string arguments for both parameters (with the exception for $collate = false, the default assignment),
// returns false if so, else returns true
function AlterCharacterSet($char_set, $collate = false) {
if ($this->ThisTable && $this->Status === null) return false; // obvious
if (!is_string($char_set)) return false;
if ($collate && !is_string($collate)) return false;
if ($this->DoReset("ALT")) $this->AlterTable();
$this->Drop["char_set"] = array($char_set, $collate);
return true;
}
/*
// **********************************************************************
// END
// Alter Table Functions
// **********************************************************************
*/
}
/*
// **********************************************************************
// END SqueaksCore Class
// START MySqueaks Class
// **********************************************************************
*/
class MySqueaks extends SqueaksCore {
function Tabs($v) { return ($v ? "\t".$this->Tabs(--$v) : ""); }
function toString($format = false) { return $this->GetQuery($format); }
// runs the query
// note since 0.9.3 beta: SQX_NONE should be gracefully deprecated now that we have extra tests for checking
// keeping it for backwards compatibility purposes only
// $return_type = 0: Fetch_Assoc (default); 1: NumRows; 2: true, 3: Fetch_Row, 4: Fetch_Object
function Submit($return_type = SQX_FETCH_ASSOC) {
if (substr($this->Query, -1, 1) != ";") $this->BuildQuery();
if ($this->Query == "000 ;")
if ($this->ThisTable && $this->Status) $this->Query = "SELECT * FROM ".$this->ThisTable.";"; else return false;
$this->Fault = false;
$submit_query = str_replace("\n", "", $this->Query);
$submit_query = str_replace("\t", "", $submit_query);
$result = mysql_query($submit_query, $this->Database) or $this->getError();
if ($this->Fault == true) return false;
if ($return_type == SQX_NONE) {
// update columns if user did ALTER or CREATE TABLE with bindings
if ($this->ThisTable) {
if (substr($this->Query, 0, 3) == "ALT") $this->Columns = $this->TableFields($this->ThisTable, true);
if (strlen($this->Query) > 12 && substr($this->Query, 3, 7) == "ATE TAB") {
$this->Columns = $this->TableFields($this->ThisTable, true);
$this->Status = true;
}
}
return true;
} elseif ($return_type == SQX_NUM_ROWS) {
return mysql_num_rows($result);
} elseif ($return_type == SQX_AFFECTED_ROWS) {
if ($this->ThisTable) {
if (substr($this->Query, 0, 3) == "ALT") $this->Columns = $this->TableFields($this->ThisTable, true);
if (strlen($this->Query) > 12 && substr($this->Query, 3, 7) == "ATE TAB") {
$this->Columns = $this->TableFields($this->ThisTable, true);
$this->Status = true;
}
}
return mysql_affected_rows($result);
} else {
if (substr($this->Query, 0, 3) == "SEL") {
$return = array(); // ADDED THIS
if ($return_type == SQX_FETCH_ASSOC) while ($row = mysql_fetch_assoc($result)) $return[] = $row;
if ($return_type == SQX_FETCH_ROW) while ($row = mysql_fetch_row($result)) $return[] = $row;
if ($return_type == SQX_FETCH_OBJECT) while ($row = mysql_fetch_object($result)) $return[] = $row;
mysql_free_result($result);
return $return;
} else {
// update columns if user did ALTER or CREATE TABLE with bindings
if ($this->ThisTable) {
if (substr($this->Query, 0, 3) == "ALT") $this->Columns = $this->TableFields($this->ThisTable, true);
if (strlen($this->Query) > 12 && substr($this->Query, 3, 7) == "ATE TAB") {
$this->Columns = $this->TableFields($this->ThisTable, true);
$this->Status = true;
}
}
}
return true;
}
}
// Generates a HTML table with an optional header row
// returns HTML output as a string
function Tabulate($tblname = false, $print_col_name = true) {
$results = $this->Submit();
if ($this->Fault) return false;
$show = "\n<!-- Table generated using MySqueaks -->\n<div";
if ($tblname) {
if (substr($tblname, 0, 1) == ".") $show = $show." class=\"".substr($tblname, 1)."\"";
else $show = $show." id=\"".$tblname."\"";
}
$show = $show.">\n\t<table>\n";
foreach ($results as $key => $value) {
if ($print_col_name) {
$show = $show."\t\t<tr>\n";
foreach (array_keys($value) as $col_name) $show = $show."\t\t\t<td>".$col_name."</td>\n";
$show = $show."\t\t</tr>\n";
$print_col_name = false;
}
$show = $show."\t\t<tr>\n";
foreach ($value as $result_row) {
$show = $show."\t\t\t<td>\n\t\t\t\t".str_replace("\n", "\n\t\t\t\t", $result_row)."\n\t\t\t</td>\n";
}
$show = $show."\t\t</tr>\n";
}
$show = $show."\t</table>\n</div>\n<!-- End of Table -->\n";
return $show;
}
// Generates XML output
// returns as a string, with wrappers for all the rows
function XML($pad = 0, $sematic = false, $wrapper = "result") {
if (!is_string($wrapper)) return false;
$results = $this->Submit();
if ($this->Fault) return false;
foreach ($results as $key => $value) {
$show = $show.$this->Tabs($pad + 1)."<$wrapper>\n";
foreach ($value as $name => $result_row) {
$show = $show.$this->Tabs($pad + 2).($sematic ? "<$name>\n" : "<field name=\"".$name."\">\n");
$show = $show.$this->Tabs($pad + 3).str_replace("\n", $this->Tabs($pad + 3)."\n", htmlentities($result_row));
$show = $show."\n".$this->Tabs($pad + 2).($sematic ? "</$name>\n" : "</field>\n");
}
$show = $show.$this->Tabs($pad + 1)."</$wrapper>\n";
}
return $show;
}
}
/*
// **********************************************************************
// END MySqueaks Class
// START ASCII Art
// **********************************************************************
*/
/*
__ __ ________ __ ___ ________
| \ / | / ____/ _____ | | / / / ____/
| \ / | ___ __ | /___ / _ | _ _ ____ ______ | |/ / | /___
| v | \ \ / / \___ \ \ (_) | | | | | / _ \ / _ | | / \___ \
| |\_/| | \ v / \ \ `--- | | | | | | (_) _/ | (_) | | |\ \ \ \
| | | | / / ____/ / | |_ | \_/ | | /__ | | | | \ \ ____/ /
-- -- /____/ |______/ |__/ \_____/ \______\ \___/\_| |__| \__\ |______/HJK
*/
/*
// **********************************************************************
// END
// ASCII Art
// **********************************************************************
*/
?>
|
Download
