PHP Classes
elePHPant
Icontem

EndSql: Build and execute SQL queries using PDO

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Info   View files Example   View files View files (17)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Last Updated Ratings Unique User Downloads Download Rankings  
2014-08-16 (11 months ago) RSS 2.0 feedNot yet rated by the usersTotal: 346 All time: 6,441 This week: 1,173Up
Version License PHP version Categories  
endsql 1.0.4BSD License5.0PHP 5, Databases
Description Author  

This package can build and execute SQL queries using PDO.

It provides several classes to build SQL SELECT, INSERT, UPDATE and DELETE queries by the means of a fluent interface that allows you to define several parameters of the queries.

The parameters that can be configured are the table names, field names and values, condition and limit clauses, sorting and grouping clauses, etc..
The SQL queries that are build can be executed using PDO objects.

Picture of woestler
Name: woestler <contact>
Classes: 1 package by
Country: China China
Age: 23
All time rank: 336839 in China China
Week rank: 1577 Up11 in China China Up

Details provided by the author  

EndSql <hr>

Table of Contents

Initialization Insert Query Update Query Select Query Delete Query Generic Query Where Conditions Order Conditions Group Conditions Limit Conditions Joining Tables Subqueries Helpers

Initialization

To utilize this class, first import Autoload.php into your project, and require it. You may need to edit the username and password of your database server in local.php. And you need to enable the pdo extension for your php.

require_once ('Autoload.php');

After that, create a new instance of the class.

$db = EndSql::getInstance();

Next, get a new instance of query method which you want by call the relevant methods.

Insert Query

Simple example `php $insert = $db->insert(); $data = Array ("login" => "admin", "firstName" => "John", "lastName" => 'Doe' ); $count = $insert->into("user")->values($data)->exec(); if($count) echo $count.' user was created.';

$insert->clear(); $insert->into("user"); $data = array("admin","John","Doe"); $insert->values($data)->exec(); `

Insert mutiple lines `php $data = array(array("admin","Woestler","Dom"),array("admin","Forsan","Co")); $insert->values($data)->exec(); ` Or you can insert mutiple lines like this `php $data = array(array("firstName"=>"woestler","lastName"=>"Dom"), array("firstName"=>"forsan","lastName"=>"Co")); $insert->values($data)->exec(); // Gives: INSERT INTO user ("firstName","lastName") VALUES ("Woestelr","Dom"),("Forsan","Co"); `

Update Query

$data = array("username" => "Woestler");
$update = $db->update();
$update->table("admin"); 
// or you could pass the table name to constructer $db->update("admin");
$update->where()->equal(array("id"=>3));
$count = $update->set($data)->exec();

if ($count)
    echo $count . ' records were updated';
else
    echo 'update failed: ' . $update->getLastError();

Select Query

select from single table; `php $select = $db->select(); $admin = $select->from("admin")->exec(); ` select with custom columns set. Functions also could be used

$cols = array ("id", "name", "email");
$select->from("admin")->columns($cols);
$data = $select->exec();

select from mutiple tables; `php $select->from(array("admin","user")); $select->columns(array("admin.firstName","user.firstName")); $select->where()->equal(array("admin.firstName"=>"user.firsName")); $data = $select->exec(); `

select with 'where' condition example `php $select->from("admin")->where()->equal(array("id" => 4)); $data = $select->exec(); `

Delete Query

delete example 1 `php $delete = $db->delete(); $delete->from("admin")->where()->less(array("id"=>4)); $delete->where()->equal(array("id" => 3),EndSql::SQL_OR); $delete->exec(); // delete from admin where (id<=4) or (id=3);

delete example 2

$delete->from("admin")->where()->equal(array("user"=>"woestler")); $delete->where()->less(array("id"=>40,"priviledge"=>10),EndSql::SQL_OR,EndSql::SQL_AND); $delete->exec(); //delete from admin where (user="woestler") or (id<=40 and priviledge<=10) `

Generic Query Method


$db = EndSql::getInstance();
$data = $db->query("select * from user");

Where Method

This method allows you to specify where parameters of the query. WARNING: In order to use column to column comparisons only raw where conditions should be used as column name or functions cant be passed as a bind variable.

Regular == operator with variables: `php $select->from("users")->where()->equal(array("id"=>1,"login"=>"woestler")); $select->exec(); // Gives: SELECT * FROM users WHERE (id=1 AND login='woestler'); `

Regular == operator `php $select->from("users")->where()->equal(array("id"=>1,"login"=>"woestler")); $select->where()->lessThan(array("u_id"=>4,"t_id"=>5),EndSql::SQL_OR,EndSql::SQL_AND); $select->exec() // Gives: SELECT * FROM users WHERE (id=1 AND login='woestler') OR (u_id<4 AND t_id<5); ` Regular NOT IN operator

$select->from("users")->where()->notIn(array("id"=>array(1,2,3)));
// Gives: SELECT * FROM users WHERE id NOT IN (1,2,3);

Regular IN operator

$select->from("users")->where()->in(array("id"=>array(1,2,3)));
// Gives: SELECT * FROM users WHERE id IN (1,2,3);

Regular LIKE operator `php $select->from("users")->where()->like(array("name"=>"%woestler%")); // Gives: SELECT * FROM users WHERE name LIKE '%woestler%'; ` Regular NOT LIKE operator `php $select->from("users")->where()->notLike(array("name"=>"%woestler%")); // Gives: SELECT * FROM users WHERE name NOT LIKE '%woestler%'; `

BETWEEN `php $select->from("users")->where()->between("id",array(4,20)); // Gives: SELECT * FROM users WHERE id BETWEEN 4 AND 20 `

NOT NULL comparison: `php $select->from("user")->where()->notNull("lastName"); $results = $db->get("users"); // Gives: SELECT * FROM users where lastName NOT NULL $select->from("user")->where()->notNull(array("firstName","lastName"),EndSql::SQL_OR); // Gives: SELECT * FROM users where firstName NOT NULL OR lastName NOT NULL `

IS NULL comparison `php $select->from("user")->where()->isNull("lastName"); $results = $db->get("users"); // Gives: SELECT * FROM users where lastName IS NULL $select->from("user")->where()->isNull(array("firstName","lastName"),EndSql::SQL_OR); // Gives: SELECT * FROM users where firstName IS NULL OR lastName IS NULL `

You can call where method multiple times.

$select->from("user")->where()->equal(array("username"=>"woestler"));
$select->where()->lessThan(array("id"=>4));
// GIVES: SELECT * FROM user where username="woestler" AND id<4;

Ordering method

$select = $db->select("user");
$select->order("id DESC");
// Gives: SELECT * FROM user ORDER BY id DESC;
$select->clear();
$select->from("user")->order(array("id DESC","login DESC"));
//Gives: SELECT * FROM user ORDER BY id DESC,login DESC

Grouping method

$select->from("users")->group("name");
// Gives: SELECT * FROM users GROUP BY name;

limit-method

$select->from("users")->limit(1);
$select->exec();
// Gives: SELECT * FROM users LIMIT 1;

$select->clear();
$select->from("users")->limit(array(1,3))->exec();
//Gives: SELECT * FROM users LIMIT 1,3

JOIN method

JOIN_LEFT | JOIN_RIGHT | JOIN_INNER

$select->from("user")->join("comment",array("comment.user_id" => "user.user_id"),EndSql::JOIN_LEFT);
//Gives: SELECT * FROM user LEFT JOIN comment ON comment.user_id = user.user_id;

Subqueries

Subquery in selects example1: `php $select = $db->select(); $subSelect = $db->select(); $subSelect->from("products")->columns(array("userId")) ->where()->greaterThan(array("qty"=>2)); $select->where()->in(array("id"=>$subSelect));

// Gives SELECT * FROM users WHERE id IN (SELECT userId FROM products WHERE qty > 2) `

Subquery in selects example2: `php $select = $db->select(); $subSelect = $db->select(); $subSelect->from("products")->columns(array("userId")) ->where()->equal(array("qty"=>2)); $select->where()->equal(array("id"=>$subSelect));

// Gives SELECT * FROM users WHERE id = (SELECT userId FROM products WHERE qty = 2) `

helpers

Get last SQL query. Please note that function returns SQL query only for debugging purposes as its execution most likely will fail due missing quotes around char variables. `php $db = EndSql::getInstance(); $select = $db->select("user"); $select->getSql(); // return : SELECT * FROM user; ` Error handing

  $data = $select->from("user")->exec();
  if(!$data) {
      print_r($select->getLastError());
  }

  Files folder image Files  
File Role Description
Files folder imagedemo (4 files)
Files folder imagelibrary (1 file, 1 directory)
Plain text file Autoload.php Class Class source
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file LICENSE.txt Lic. Documentation
Accessible without login Plain text file local.php Conf. Configuration script
Accessible without login Plain text file readme.md Data Auxiliary data
Accessible without login Plain text file test.php Example Example script

  Files folder image Files  /  demo  
File Role Description
  Accessible without login Plain text file delete.php Example Example script
  Accessible without login Plain text file insert.php Example Example script
  Accessible without login Plain text file select.php Example Example script
  Accessible without login Plain text file update.php Example Example script

  Files folder image Files  /  library  
File Role Description
Files folder imageEndSql (1 directory)
  Plain text file EndSql.php Class Class source

  Files folder image Files  /  library  /  EndSql  
File Role Description
Files folder imageSql (6 files)

  Files folder image Files  /  library  /  EndSql  /  Sql  
File Role Description
  Plain text file AbstractSql.php Class Class source
  Plain text file Delete.php Class Class source
  Plain text file Insert.php Class Class source
  Plain text file Select.php Class Class source
  Plain text file Update.php Class Class source
  Plain text file Where.php Class Class source

 Version Control Unique User Downloads Download Rankings  
 100%Total:346All time:6,441
 This week:0This week:1,173Up