Location: PHPKode > scripts > My One Line SQL > my-one-line-sql/MyOneLineSQL.class.php
<?php
/**
 * @package myOneLineSQL
 */
/**
 *class MyOneLineSQL
 *
 * A composition of DBQuery and RunDBQuery objects to execute queries in one line of code.
 * 
 * Each method in this class is static and always return a RunDBQuery object.
 * The object returned contains info about the query executed which can be retrieved via the protocol of RunDBQuery class
 * @package myOneLineSQL
 * @author Carlo Tasca
 * @version 1.0
 */
class MyOneLineSQL
{
   /**
    * No constructor
    * 
    * Only static methods. Throws fatal error alerting class cannot have instances
    *
    */
   public function __construct()
   {
      trigger_error ( "Class SQLQuery cannot be instantiated.", E_USER_ERROR );
   }
   /**
    * Does SELECT {WHAT} FROM {TABLE_NAME}
    * 
    * Keyword for DBQuery Object: SELECT
    *
    * @param string $dbTableName
    * @param db connection $dbc
    * @param array $dbFields
    * @return RunDBQuery
    */
   public static function doSelect($dbTableName, $dbc, $dbFields = array())
   {
      $q = new DBQuery ( 'SELECT' );
      $s = "";
      for($i = 0; $i < count ( $dbFields ); $i ++)
      {
         if ($i !== (count ( $dbFields ) - 1))
         {
            $s .= $dbFields [$i] . ", ";
         } else
         {
            $s .= $dbFields [$i] . " ";
         }
      }
      $q->SetParameters ( "WHAT", $s );
      $q->SetParameters ( "TABLE_NAME", $dbTableName );
      $q->queryBuilder ();
      $r = new RunDBQuery ( $q, $dbc );
      $r->runQuery ();
      return $r;
   }
   
   /**
    * SELECT {WHAT} FROM {TABLE_NAME} WHERE {WHERE_EXP}
    * 
    * Keyword for DBQuery Object: SELECT_WHERE
    *
    * @param string $dbTableName
    * @param db connection $dbc
    * @param string $where
    * @param array $dbFields
    * @return RunDBQuery object
    */
   public static function doSelectWhere($dbTableName, $dbc, $where, $dbFields = array())
   {
      $q = new DBQuery ( 'SELECT_WHERE' );
      $s = "";
      for($i = 0; $i < count ( $dbFields ); $i ++)
      {
         if ($i !== (count ( $dbFields ) - 1))
         {
            $s .= $dbFields [$i] . ", ";
         } else
         {
            $s .= $dbFields [$i] . " ";
         }
      }
      $q->SetParameters ( "WHAT", $s );
      $q->SetParameters ( "TABLE_NAME", $dbTableName );
      $q->SetParameters ( "WHERE_EXP", $where );
      $q->queryBuilder ();
      $q->getThisQuery ();
      $r = new RunDBQuery ( $q, $dbc );
      $r->runQuery ();
      $r->getQueryResultInfo ();
      return $r;
   }
   
   /**
    * Runs SELECT ... ORDER BY query
    * 
    * SELECT {WHAT} FROM {TABLE_NAME} ORDER BY {ORDER_BY} {ASC_DESC}
    * 
    * Keyword for DBQuery Object: SELECT_ORDER
    * 
    * Takes a table name, a dbconnetion, a order expresssion, order mode (ASC, DESC)
    * and an array of fields to select as arguments, runs query and returns RunDBQuery object
    *
    * @param string $dbTableName
    * @param dbconnect $dbc
    * @param string $order
    * @param string $asc_desc
    * @param array $dbFields
    * @return RunDBQuery
    */
   public static function doSelectOrder($dbTableName, $dbc, $order, $asc_desc = "", $dbFields = array())
   {
      $q = new DBQuery ( 'SELECT_ORDER' );
      $s = "";
      for($i = 0; $i < count ( $dbFields ); $i ++)
      {
         if ($i !== (count ( $dbFields ) - 1))
         {
            $s .= $dbFields [$i] . ", ";
         } else
         {
            $s .= $dbFields [$i] . " ";
         }
      }
      $q->SetParameters ( "WHAT", $s );
      $q->SetParameters ( "TABLE_NAME", $dbTableName );
      $q->SetParameters ( "ORDER_BY", $order );
      $q->SetParameters ( "ASC_DESC", $asc_desc );
      $q->queryBuilder ();
      $r = new RunDBQuery ( $q, $dbc );
      $r->runQuery ();
      return $r;
   }
   
   /**
    * Runs SELECT, WHERE, ORDER BY
    * 
    * SELECT {WHAT} FROM {TABLE_NAME} WHERE {WHERE_EXP} ORDER BY {ORDER_BY} {ASC_DESC}
    * 
    * Keyword for DBQuery Object: SELECT_WHERE_ORDER
    * 
    * Takes a table name, a db connetion reference, a where expression, a order expression, a order mode (ASC, DESC) and an array of fields to select as arguments, runs query and returns RunDBQuery Objet
    *
    * @param string $dbTableName
    * @param dbconnect $dbc
    * @param string $where
    * @param string $order
    * @param string $asc_desc
    * @param array $dbFields
    * @return RunDBQuery
    */
   public static function doSelectWhereOrder($dbTableName, $dbc, $where, $order, $asc_desc = "", $dbFields = array())
   {
      $q = new DBQuery ( 'SELECT_WHERE_ORDER' );
      $s = "";
      for($i = 0; $i < count ( $dbFields ); $i ++)
      {
         if ($i !== (count ( $dbFields ) - 1))
         {
            $s .= $dbFields [$i] . ", ";
         } else
         {
            $s .= $dbFields [$i] . " ";
         }
      }
      $q->SetParameters ( "WHAT", $s );
      $q->SetParameters ( "TABLE_NAME", $dbTableName );
      $q->SetParameters ( "WHERE_EXP", $where );
      $q->SetParameters ( "ORDER_BY", $order );
      $q->SetParameters ( "ASC_DESC", $asc_desc );
      $q->queryBuilder ();
      $r = new RunDBQuery ( $q, $dbc );
      $r->runQuery ();
      return $r;
   }
   
   /**
    * Runs SELECT, WHERE, ORDER BY, LIMIT
    * 
    * SELECT {WHAT} FROM {TABLE_NAME} WHERE {WHERE_EXP} ORDER BY {ORDER_BY} {ASC_DESC} LIMIT {QUERY_LIMITS}
    * 
    * Keyword for DBQuery Object: SELECT_WHERE_ORDER_LIMIT
    * 
    * Takes a table name, a db connection reference, a where expression, a order expression, a limit definition , a order mode (ASC, DESC) and an array of fields to select as arguments, runs the query and returns a RunDBQuery object
    *
    * @param string $dbTableName
    * @param dbconnect $dbc
    * @param string $where
    * @param string $order
    * @param string $limit
    * @param string $asc_desc
    * @param array $dbFields
    * @return uRunDBQuery
    */
   public static function doSelectWhereOrderByLimit($dbTableName, $dbc, $where, $order, $limit, $asc_desc = "", $dbFields = array())
   {
      $q = new DBQuery ( 'SELECT_WHERE_ORDER_LIMIT' );
      $s = "";
      for($i = 0; $i < count ( $dbFields ); $i ++)
      {
         if ($i !== (count ( $dbFields ) - 1))
         {
            $s .= $dbFields [$i] . ", ";
         } else
         {
            $s .= $dbFields [$i] . " ";
         }
      }
      $q->SetParameters ( "WHAT", $s );
      $q->SetParameters ( "TABLE_NAME", $dbTableName );
      $q->SetParameters ( "WHERE_EXP", $where );
      $q->SetParameters ( "ORDER_BY", $order );
      $q->SetParameters ( "ASC_DESC", $asc_desc );
      $q->SetParameters ( "QUERY_LIMITS", $limit );
      $q->queryBuilder ();
      $r = new RunDBQuery ( $q, $dbc );
      $r->runQuery ();
      return $r;
   }
   
   /**
    * Runs UPDATE ... WHERE
    * 
    * UPDATE {TABLE_NAME} SET {WHICH_SET} WHERE {WHERE_EXP}
    * 
    * Keyword for DBQuery Object: UPDATE
    * 
    * Takes a table name, a db connection reference, a where expression, and array
    * of set expressions as argument.
    * Each value of sets array is separated by a comma (apart the very last one)
    * Runs query and returns RunDBQuery object
    *
    * @param string $dbTableName
    * @param dbconnect $dbc
    * @param string $where
    * @param array $sets
    * @return RunDBQuery
    */
   public static function doUpdateWhere($dbTableName, $dbc, $where, $sets = array())
   {
      $query = new DBQuery ( 'UPDATE' );
      $s = "";
      for($i = 0; $i < count ( $sets ); $i ++)
      {
         if ($i !== (count ( $sets ) - 1))
         {
            $s .= $sets [$i] . ", ";
         } else
         {
            $s .= $sets [$i] . " ";
         }
      }
      $query->SetParameters ( "TABLE_NAME", $dbTableName );
      $query->SetParameters ( "WHICH_SET", $s );
      $query->SetParameters ( "WHERE_EXP", $where );
      $query->queryBuilder ();
      $r = new RunDBQuery ( $query, $dbc );
      $r->runQuery ();
      return $r;
   }
   
   /**
    * Runs INSERT query
    * 
    * INSERT INTO {TABLE_NAME} VALUES ({WHICH_VALUES})
    * 
    * Keyword for DBQuery Object: INSERT
    * 
    * Takes a table name, db connection reference and an array of values to be inserted as argument, runs the query and returns RunDBQuery object
    *
    * @param string $dbTableName
    * @param dbconnect $dbc
    * @param array $values
    * @return RunDBQuery
    */
   public static function doInsert($dbTableName, $dbc, $values = array())
   {
      $query = new DBQuery ( 'INSERT' );
      $s = "";
      for($i = 0; $i < count ( $values ); $i ++)
      {
         if ($i !== (count ( $values ) - 1))
         {
            $s .= "'" . $values [$i] . "'" . ", ";
         } else
         {
            $s .= "'" . $values [$i] . "'" . " ";
         }
      }
      $query->SetParameters ( "TABLE_NAME", $dbTableName );
      $query->SetParameters ( "WHICH_VALUES", $s );
      $query->queryBuilder ();
      $query->getThisQuery ();
      $r = new RunDBQuery ( $query, $dbc );
      $r->runQuery ();
      return $r;
   }
   
   /**
    * Runs CREATE TABLE query
    * 
    * CREATE TABLE {TABLE_STRUCTURE}
    * 
    * Keyword for DBQuery Object: CREATE
    * 
    * Takes a table structure and a database connection reference as arguments
    * runs the query and returns RunDBQuery object
    *
    * @param string $tableStructure
    * @param dbconnect $dbc
    * @return RunDBQuery
    */
   public static function doCreateTable($tableStructure, $dbc)
   {
      $query = new DBQuery ( 'CREATE' );
      $query->SetParameters ( "TABLE_STRUCTURE", $tableStructure );
      $query->queryBuilder ();
      $r = new RunDBQuery ( $query, $dbc );
      $r->runQuery ();
      return $r;
   }
   
   /**
    * Runs ALTER query
    * 
    * ALTER TABLE {TABLE_NAME} ADD {ADD_WHAT} AFTER {AFTER_WHAT}
    * 
    * Keyword for DBQuery Object: ALTER
    * 
    * Runs ALTER query on table name passed as argument.
    * Arguments to the method are also a database connection reference,
    * the definition of the field to add, and the field after which the new field must be added
    *
    * @param string $dbTableName
    * @param dbconnect $dbc
    * @param string $add_what
    * @param string $after_what
    * @return RunDBQuery
    */
   public static function doAlterTable($dbTableName, $dbc, $add_what, $after_what)
   {
      $query = new DBQuery ( 'ALTER' );
      $query->SetParameters ( "TABLE_NAME", $dbTableName );
      $query->SetParameters ( "ADD_WHAT", $add_what );
      $query->SetParameters ( "AFTER_WHAT", $after_what );
      $query->queryBuilder ();
      $r = new RunDBQuery ( $query, $dbc );
      $r->runQuery ();
      return $r;
   }
   
   /**
    * Runs DROP TABLE query
    * 
    * DROP TABLE {TABLE_NAME}
    * 
    * Keyword for DBQuery Object: DROP_TABLE
    * 
    * Takes a table name and db connection as arguments runs the query and returns RunDBQuery object
    *
    * @param string $dbTableName
    * @param dbconnect $dbc
    * @return RunDBQuery
    */
   public static function doDropTable($dbTableName, $dbc)
   {
      $query = new DBQuery ( 'DROP_TABLE' );
      $query->SetParameters ( "TABLE_NAME", $dbTableName );
      $query->queryBuilder ();
      $r = new RunDBQuery ( $query, $dbc );
      $r->runQuery ();
      return $r;
   }
   
   /**
    * Runs DELETE FROM, WHERE
    * 
    * DELETE FROM {TABLE_NAME} WHERE {WHERE_EXP}
    * 
    * Keyword for DBQuery Object: DELETE_WHERE
    * 
    * Takes a table name, db connection reference and where expression
    * as argument and returns RunDBQuery obejct
    *
    * @param string $dbTableName
    * @param dbconnect $dbc
    * @param string $where
    * @return RunDBQuery
    */
   public static function doDeleteWhere($dbTableName, $dbc, $where)
   {
      $query = new DBQuery ( 'DELETE_WHERE' );
      $query->SetParameters ( "TABLE_NAME", $dbTableName );
      $query->SetParameters ( "WHERE_EXP", $where );
      $query->queryBuilder ();
      $r = new RunDBQuery ( $query, $dbc );
      $r->runQuery ();
      return $r;
   }
   
   /**
    * Runs SHOW TABLES query
    * 
    * SHOW TABLES
    * 
    * Keyword for DBQuery Object: SHOW
    * 
    * Takes a db connection reference as argument, runs query and returns RunDBQuery object
    *
    * @param dbconnect $dbc
    * @return RunDBQuery
    */
   public static function doShowTables($dbc)
   {
      $query = new DBQuery ( 'SHOW' );
      $query->queryBuilder ();
      $r = new RunDBQuery ( $query, $dbc );
      $r->runQuery ();
      return $r;
   }
   
   /**
    * Runs OPTIMIZE query
    * 
    * OPTIMIZE TABLE {TABLE_NAME}
    * 
    * Keyword for DBQuery Object: OPTIMIZE
    * 
    * Takes a table name and db connection as arguments, runs query and returns RunDBQuery object
    *
    * @param string $dbTableName
    * @param dbconnect $dbc
    * @return RunDBquery
    */
   public static function doOptimize($dbTableName, $dbc)
   {
      $query = new DBQuery ( 'OPTIMIZE' );
      $query->SetParameters ( "TABLE_NAME", $dbTableName );
      $query->queryBuilder ();
      $r = new RunDBQuery ( $query, $dbc );
      $r->runQuery ();
      return $r;
   }
   /**
    * Runs OPTIMIZE query on every table in database
    * 
    * 
    * Takes db connection as argument, runs optimize query for each database table in DB
    *
    * @param dbconnect $dbc
    */
   public static function doOptimizeAll($dbc)
   {
      $query = new DBQuery ( 'SHOW' );
      $query->queryBuilder ();
      $r = new RunDBQuery ( $query, $dbc );
      $r->runQuery ();
      $r->getQueryResultArray ();
      foreach ( $r->getQueryResultArray () as $value )
      {
         foreach ( $value as $v )
         {
            SQLQuery::doOptimize ( $v, $dbc );
         }
      }
   }
}
?>
Return current item: My One Line SQL