Download 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());
}
|