PHP Classes

File: class_sql.php

Recommend this page to a friend!
  Classes of Han Jun Kwang   MySqueaks   class_sql.php   Download  
File: class_sql.php
Role: Class source
Content type: text/plain
Description: MySqueaks source
Class: MySqueaks
Manipulate MySQL database tables
Author: By
Last change: Bug fixes for Drop() and RenameTable(). Updated code for ChangeDatabase() to Clear() as well. Updated code for DropTable() to do DROP TABLE IF EXISTS....
Date: 15 years ago
Size: 110,847 bytes
 

Contents

Class file image Download
<?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 // ********************************************************************** */ ?>