PHP Classes

EndSql: Build and execute SQL queries using PDO

Recommend this page to a friend!
  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
2024-01-09 (2 months ago) RSS 2.0 feedNot enough user ratingsTotal: 395 This week: 1All time: 6,607 This week: 571Up
Version License PHP version Categories
endsql 1.0.13BSD 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: 32
All time rank: 355837 in China China
Week rank: 420 Up4 in China China Up

Example

<?php
/**
 * EndSQL
 *
 * Database abstract layer.
 *
 *
 * Copyright (c) 2013-2014, Woestler
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without modification, are
 * permitted provided that the following conditions are met:
 *
 * * Redistributions of source code must retain the above copyright notice, this list of
 * conditions and the following disclaimer.
 *
 * * Redistributions in binary form must reproduce the above copyright notice, this list
 * of conditions and the following disclaimer in the documentation and/or other materials
 * provided with the distribution.
 *
 * * Neither the name of the EndSQL Team nor the names of its contributors may be used
 * to endorse or promote products derived from this software without specific prior
 * written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS
 * OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
 * AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS
 * AND CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
 * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
 * OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGE.
 *
 * @package EndSQL
 * @version 1.0.2
 * @copyright 2013-2014 Woestler
 * @author Woestler
 * @link http://EndSQL.org/ EndSQL
 * @license http://www.opensource.org/licenses/bsd-license.php BSD License
 */
require 'Autoload.php';
$start = microtime(true);
$db = EndSql::getInstance();
// $insert = $db->insert("type");
// $pdo = $db->getPdo();

// $pdo->prepare("INSERT INTO type (type_id,type) VALUES (?,?)")->execute(array(NULL,'test1'));


$delete = $db->delete();
$update = $db->update();
$select = $db->select();
$insert = $db->insert();

$data = array(NULL,'TEST_Y');
// $select->from("type");
// $select->group("type");
// $select->group("type");


$select->from("context");
$subselect = $db->select("tumblr")->columns(array("type_id"));
$subselect->where()->equal(array('type_id' => 333 ));
$subselect->limit(1);
$select->where()->equal(array("type_id" => $subselect));
$select->limit(2);
$select->order(array("id DESC","caption DESC"));

echo
$select->getSql();
//SELECT * FROM context WHERE ( type_id = (SELECT type_id FROM tumblr WHERE ( type_id = 333) LIMIT 1)) ORDER BY id DESC,caption DESC LIMIT 2

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

// echo $select->getSql();
// print_r($select->exec());


// $delete->where()->greaterThan(array("type_id" => 60));
// $delete->from("type");
// echo $delete->exec();
// $insert->into("tumblr");
// $insert->values(array('woestler@gmail.com','dongyuhan',"jffjowjfowfo","1324535322","233522"));
// $insert->exec();
// for($i=0;$i<100;$i++) {
// $insert->values(array(NULL,"TEST1".$i));
// $insert->exec();
// }
//
// $update->where()->equal(array("type_id" => 300));
// $update->where()->isNull(array("type"));
// $update->table("type");

// echo $update->set(array("type" => "new"))->exec();

$end = microtime(true);
 
// usleep(1000000);




Details

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

$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

$data = array(array("admin","Woestler","Dom"),array("admin","Forsan","Co"));
$insert->values($data)->exec();

Or you can insert mutiple lines like this

$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;

$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;

$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

$select->from("admin")->where()->equal(array("id" => 4));
$data = $select->exec();

Delete Query

delete example 1

$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:

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

Regular == operator

$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

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

Regular NOT LIKE operator

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

BETWEEN

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

NOT NULL comparison:

$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

$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:

$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:

$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.

    $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:395
This week:1
All time:6,607
This week:571Up