<?
/**
* Create sql query
*
* @author Nguyen Quoc Bao <quocbao.coder@gmail.com>
*/
class SQL
{
/**
* Remove unnecessary string in a sql query
* @param string $s String 1
* @param string $s2 String need to be removed
* @return string
* @access private
**/
function trim($s,$s2) {
if (substr($s , strlen($s) - strlen($s2)) == $s2) $s = substr($s , 0 , strlen($s) - strlen($s2));
return $s;
}
/**
* Quote a SQL value
* @param string $s String need to be quoted
* @return string
**/
function quote($s) {
return "'".str_replace('\\"', '"', addslashes($s))."'";
}
/**
* Generate SQL Insert Query
* @param string $table Target table name
* @param array $data SQL Data (ColumnName => ColumnValue)
* @return string
**/
function insert($table,$data) {
if (is_string($data)) {
return "INSERT INTO $table $data;";
}
$field = '';
$col = '';
foreach ($data as $k => $v) {
$field .= "`" . $k . "`,";
$col .= SQL::quote($v) . ",";
}
$field = SQL::trim($field , ',');$col = SQL::trim($col , ',');
return "INSERT INTO $table ($field) VALUES ($col);";
}
/**
* Generate SQL Update Query
* @param string $table Target table name
* @param array $data SQL Data (ColumnName => ColumnValue)
* @param string $cond SQL Condition
* @return string
**/
function update($table,$data,$cond='')
{
$sql = "UPDATE $table SET ";
if (is_string($data)) {
$sql .= $data;
} else {
foreach ($data as $k => $v) {
$sql .= "`" . $k . "`" . " = " . SQL::quote($v) . ",";
}
$sql = SQL::trim($sql , ',');
}
if ($cond != '') $sql .= " WHERE $cond";
$sql .= ";";
return $sql;
}
/**
* Generate SQL Delete Query
* @param string $table Target table name
* @param string $cond SQL Condition
* @return string
**/
function delete($table,$cond='')
{
$sql = "DELETE FROM $table";
if ($cond != '') $sql .= " WHERE $cond";
$sql .= ";";
return $sql;
}
/**
* Generate SQL replace query
* @param string $table Target table name
* @param array $data SQL Data (ColumnName => ColumnValue)
* @param bool $update_sytac Use SET sytac or VALUES ()
* @return string
**/
function replace($table , $data , $update_sytac = true) {
$sql = "REPLACE $table ";
if (is_string($data)) {
$sql .= $data . ";";
return $sql;
}
if ($update_sytac) {
$sql .= "SET ";
foreach ($data as $k => $v) {
$sql .= "`" . $k . "`" . " = " . SQL::quote($v) . ",";
}
$sql = SQL::trim($sql , ',');
$sql .= ";";
} else {
$field = '';
$col = '';
foreach ($data as $k => $v) {
$field .= "`" . $k . "`" . ",";
$col .= SQL::quote($v) . ",";
}
$field = SQL::trim($field , ',');$col = SQL::trim($col , ',');
$sql .="($field) VALUES ($col);";
}
return $sql;
}
/**
* Return SQL Time
* @return string
**/
function time($value,$format="DATE") {
$f = '';
switch (strtoupper($format)) {
case 'DATE':
$f = 'Y-m-d';
break;
case 'TIME':
$f = 'H:i:s';
break;
case 'DATETIME':
default:
$f = 'Y-m-d H:i:s';
break;
}
return date($f , $value);
}
/**
* Render simple equal condition
*
*/
function condition($conditions,$compare='AND')
{
foreach ($conditions as $key => $value)
{
$conditions[$key] = "`$key` = " . SQL::quote($value);
}
$sql = implode(" $compare " , $conditions);
return $sql;
}
/**
* Render simple in syntax
*
* @param unknown_type $value
*/
function in($column,$values)
{
$sql = " `$column` IN ";
if (!is_array($values)) $values = array($values);
foreach ($values as $key => $value)
{
$values[$key] = sql::quote($value);
}
return $sql . "(" . implode("," , $values) . ")";
}
/**
* Function from phpMyAdmin (http://phpwizard.net/projects/phpMyAdmin/)
*
* Removes comment and splits large sql files into individual queries
*
* Last revision: September 23, 2001 - gandon
*
* @param string the sql commands
* @param bool Fetch SQL from file
* @return array sqls
*/
function split($sql,$file=true) {
$ret = array();
if ($file) $sql = implode('' , file($sql));
$sql = trim($sql);
$sql_len = strlen($sql);
$char = '';
$string_start = '';
$in_string = false;
for ($i = 0; $i < $sql_len; ++$i) {
$char = $sql[$i];
// We are in a string, check for not escaped end of
// strings except for backquotes that can't be escaped
if ($in_string) {
for (;;) {
$i = strpos($sql, $string_start, $i);
// No end of string found -> add the current
// substring to the returned array
if (!$i) {
$ret[] = $sql;
return $ret;
}
// Backquotes or no backslashes before
// quotes: it's indeed the end of the
// string -> exit the loop
else if ($string_start == '`' || $sql[$i-1] != '\\') {
$string_start = '';
$in_string = false;
break;
}
// one or more Backslashes before the presumed
// end of string...
else {
// first checks for escaped backslashes
$j = 2;
$escaped_backslash = false;
while ($i-$j > 0 && $sql[$i-$j] == '\\') {
$escaped_backslash = !$escaped_backslash;
$j++;
}
// ... if escaped backslashes: it's really the
// end of the string -> exit the loop
if ($escaped_backslash) {
$string_start = '';
$in_string = false;
break;
}
// ... else loop
else {
$i++;
}
} // end if...elseif...else
} // end for
} // end if (in string)
// We are not in a string, first check for delimiter...
else if ($char == ';') {
// if delimiter found, add the parsed part to the returned array
$ret[] = substr($sql, 0, $i);
$sql = ltrim(substr($sql, min($i + 1, $sql_len)));
$sql_len = strlen($sql);
if ($sql_len) {
$i = -1;
} else {
// The submited statement(s) end(s) here
return $ret;
}
} // end else if (is delimiter)
// ... then check for start of a string,...
else if (($char == '"') || ($char == '\'') || ($char == '`')) {
$in_string = true;
$string_start = $char;
} // end else if (is start of string)
// for start of a comment (and remove this comment if found)...
else if ($char == '#' || ($char == ' ' && $i > 1 && $sql[$i-2] . $sql[$i-1] == '--')) {
// starting position of the comment depends on the comment type
$start_of_comment = (($sql[$i] == '#') ? $i : $i-2);
// if no "\n" exits in the remaining string, checks for "\r"
// (Mac eol style)
$end_of_comment = (strpos(' ' . $sql, "\012", $i+2)) ? strpos(' ' . $sql, "\012", $i+2) : strpos(' ' . $sql, "\015", $i+2);
if (!$end_of_comment) {
// no eol found after '#', add the parsed part to the returned
// array and exit
// RMV fix for comments at end of file
$last = trim(substr($sql, 0, $i-1));
if (!empty($last)) {
$ret[] = $last;
}
return $ret;
} else {
$sql = substr($sql, 0, $start_of_comment) . ltrim(substr($sql, $end_of_comment));
$sql_len = strlen($sql);
$i--;
} // end if...else
} // end else if (is comment)
} // end for
// add any rest to the returned array
if (!empty($sql) && trim($sql) != '') {
$ret[] = $sql;
}
return $ret;
}
}
?>
|
Download
