PHP Classes

File: samples/index.php

Recommend this page to a friend!
  Classes of Tom Schaefer   SQL Parse and Compile   samples/index.php   Download  
File: samples/index.php
Role: Example script
Content type: text/plain
Description: sample
Class: SQL Parse and Compile
Parse and compose SQL queries programatically
Author: By
Last change: add example for subselect
Date: 15 years ago
Size: 3,075 bytes
 

Contents

Class file image Download
<?php
################################################
// just replace the filename by another sample sql
$sql = file_get_contents("select.sql");
################################################
include_once("config.inc.php");

########################
// parse
########################
$sqlObject = new Sql_Parser($sql);
$parsedSQL = $sqlObject->parse();

pdbg($parsedSQL, "orange", __LINE__,__FILE__,100);

########################
// compile
########################
$sqlObject2 = new Sql_Compiler();
pdbg($sqlObject2->compile($parsedSQL), "orange", __LINE__,__FILE__,100);

$parsedSQL = $sqlObject->parse();

########################
// using wrapper class
########################
$sqlDef = new Sql();
$sqlDef->parse($sql);

########################
// adding a left join
########################
$sqlDef->setJoinLeft(
    array(
       
'Left'=> array("Value"=>"employees.employeeID", "Type" => "ident"),
       
'Op'=> '=',
       
'Right'=> array("Value"=>1, "Type" => "int_val"),
    )
);
########################
pdbg($sqlDef, "red", __LINE__,__FILE__,100);
pdbg($sqlDef->compile(), "red", __LINE__,__FILE__,100);



########################
##NEW added on 2008-12-12
########################

// create an insert statement from the scratch
$insertObject = new Sql();
$insertObject
   
->setCommand("insert")
    ->
addTableNames("employees")
    ->
addColumnNames(array("LastName","FirstName"))
    ->
addValues(
        array(
            array(
"Value"=>"Davolio","Type"=>"text_val"),
            array(
"Value"=>"Nancy","Type"=>"text_val"),
        )
    );

pdbg($insertObject, "green", __LINE__,__FILE__,100);
pdbg($insertObject->compile(), "green", __LINE__,__FILE__,100);

// create an update statement from the scratch
$updateObject = new Sql();
$updateObject
   
->setCommand("update")
    ->
addTableNames("employees")
    ->
addColumnNames(array("LastName","FirstName"))
    ->
addValues(
        array(
            array(
"Value"=>"Davolio","Type"=>"text_val"),
            array(
"Value"=>"Nancy","Type"=>"text_val"),
        )
    );
$updateObject->setAndWhere( Sql::whereHelper("id",1) );

pdbg($updateObject, "lime", __LINE__,__FILE__,100);
pdbg($updateObject->compile(), "lime", __LINE__,__FILE__,100);


// create a delete statement from the scratch
$deleteObject = new Sql();
$deleteObject
   
->setCommand("delete")
    ->
addTableNames("employees");
$deleteObject
   
->setAndWhere(Sql::whereHelper("id",50,">"))
    ->
setAndWhere(Sql::whereHelper("id",100,"<"));

pdbg($deleteObject, "red", __LINE__,__FILE__,100);
pdbg($deleteObject->compile(), "red", __LINE__,__FILE__,100);


################################################
# showing how a subselect works
################################################
$sql = 'SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop)';
// parse
$sqlObject = new Sql_Parser($sql);
$parsedSQL = $sqlObject->parse();
// compile
$sqlObject2 = new Sql_Compiler();
// output
pdbg($parsedSQL, "magenta", __LINE__,__FILE__,100);
pdbg($sqlObject2->compile($parsedSQL), "magenta", __LINE__,__FILE__,100);