PHP Classes

File: class.sql.php

Recommend this page to a friend!
  Classes of Bao Nguyen Quoc   SQL Generator   class.sql.php   Download  
File: class.sql.php
Role: Class source
Content type: text/plain
Description: Class file
Class: SQL Generator
Generate common SQL queries from parameter lists
Author: By
Last change: update source
Date: 18 years ago
Size: 7,579 bytes
 

Contents

Class file image Download
<?

/**
 * 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;
    }
}



?>