Login   Register  
PHP Classes
elePHPant
Icontem

File: samples/index.php

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  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: 2008-12-13 05:03
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);