Welcome to Marei's DB class V 1.0

MareiDB class is a simple query builder class in PHP to increase your productivity, you can't imagine how much time you're gonna save if you're using this class! .

Features

• Totally Secured : This DB class uses PDO prepared statements to provide high levels of protection against SQL Injection attacks
• Easy Usage : The syntax is really simple, and there are many ways to do the same query, so you can use the way you like ;)
• Well Documented : Everything you wanna know about this class is here and organized very well, so you can find it easily.

Usage

After downloading the class from here save it into your root directory and then open it to adjust the basic configurations for your DB connection like host, database name, DB username and DB password. And also you can easily define your current development environment to development or production.

//current development environment
"env" => "development",
//Localhost
"development" => [
					"host" => "localhost",
					"database" => "test",
					"username" => "root",
					"password" => ""
				 ],
//Server
"production"  => [
					"host" => "",
					"database" => "",
					"username" => "",
					"password" => ""
				 ]

To use the class, just include it into your project files like this

include 'DB.php';

Then you have to instantiate the class like this

$db = DB::getInstance();

Now, $db object is a new instance of DB class, we're gonna use this object to deal with our database, and you can create many objects as you want (don't worry about connections because i'm using Singleton design pattern so whenever you create new objects it returns the same connection) .

Insert values to a table

use the insert() method to insert values to a table, and it takes 2 parameters : the first one is $table_name and the second one is an associative array $fields[] so the key of that array is the column name in the table and the value of that array is the value that you wanna insert at that column.

$db->insert('mytable',
	[
		'first_name' => 'Marei',
		'last_name' => 'Morsy',
		'age'	=> 22
	]);

To see the SQL query that have executed, use the getSQL() Method like this:

echo $db->getSQL();

Output :

INSERT INTO `mytable` (`first_name`, `last_name`, `age`) VALUES (?, ?, ?)

Get the last ID inserted :

You can get the last ID inserted using lastId() method, or you can get the return of insert() method like this :

$lastID = $db->insert('mytable',
	[
		'first_name' => 'Marei',
		'last_name' => 'Morsy',
		'age'	=> 22
	]);
echo $lastID;

And here is how to use lastId() after using update() method :

echo $db->lastId();

Update table values

To update the table use update() method it holds 3 parameters : the first one is the table name, the second one is an associative array of the table values that you wanna update and the third parameter is optional, you can use it to state the update condition like WHERE clause in SQL. DB class provides so many ways to do the same queries for example : the third parameter in update() method you can do one of the following methods : ####Passing the id You can pass the $id as a third parameter and DB class will understand that there's a field in the table called id and you wanna update the record that its id is the value of $id like this :

$db->update('mytable',
	[
		'first_name' => 'Mohammed',
		'last_name' => 'Gharib',
		'age'	=> 24
	],1);

SQL Query :

UPDATE `mytable` SET `first_name` = ?, `last_name` = ?, `age` = ? WHERE `mytable`.`id` = ?

but, what if the column name was not id? ####Passing the column name and value you can pass an array of two items to update method as a third parameter : the first item in the array is the column name and the second item is the column value. The update() method in DB class will understand that you wanna update the table where the column name is equal to the value. Like this :

$db->update('mytable',
	[
		'first_name' => 'Ahmed',
		'last_name' => 'Hendy',
		'age'	=> 23
	],['id',1]);

SQL Query :

UPDATE `mytable` SET `first_name` = ?, `last_name` = ?, `age` = ? WHERE `mytable`.`id` = ?

but, what if we need to use another operator? ####Passing column name, operator and value You can pass an array of three items to update() method as a third parameter.The first item of the array is the column name as string, the second one is the operator as a string and the third item is the value, like this :

$db->update('mytable',
	[ 
		'first_name' => 'Zizo',
		'last_name' => 'Atia',
		'age'	=> 23
	],['age','>',22]);

SQL Query :

UPDATE `mytable` SET `first_name` = ?, `last_name` = ?, `age` = ? WHERE `mytable`.`age` > ?

you can also do the same query by only 2 items in the array like this :

$db->update('mytable',
	[ 
		'first_name' => 'Ahmed',
		'last_name' => 'Mansour',
		'age'	=> 27
	],['age >= ',22]);

SQL Query :

UPDATE `mytable` SET `first_name` = ?, `last_name` = ?, `age` = ? WHERE age >= ?

but, what if we wanna add more than one where condition? ####passing more than one where condition You can pass an array of arrays(nested array) as a third parameter to update() method, each array holds three items : the column name as a string, the operator and the value. The second and the third items are optional, so you can pass only the id as an array, or you can pass an array of two items : the column name and the value. And here is some examples of passing an array :

Example 1 :

$db->update('mytable',
	[
		'first_name' => 'Omar',
		'last_name' => 'Saqr',
		'age'	=> 23
	],[ [1] ]);

SQL Query :

UPDATE `mytable` SET `first_name` = ?, `last_name` = ?, `age` = ? WHERE `mytable`.`id` = ?

Example 2 :

$db->update('mytable',
	[
		'first_name' => 'Ahmed',
		'last_name' => 'Helmy',
		'age'	=> 21
	],[ ['age',18], [1] ]);

SQL Query :

UPDATE `mytable` SET `first_name` = ?, `last_name` = ?, `age` = ? WHERE `mytable`.`age` = ? AND `mytable`.`id` = ?

Example 3 :

$db->update('mytable',
	[
		'first_name' => 'Ahmed',
		'last_name' => 'Ashraf',
		'age'	=> 21
	],[ ['age','>=', 18], [1] ]);

SQL Query :

UPDATE `mytable` SET `first_name` = ?, `last_name` = ?, `age` = ? WHERE `mytable`.`age` >= ? AND `mytable`.`id` = ?

Or you can do [ ['age >= ', 18], [1] ] to get the same result.

Another way to update using where() method

where() method holds three parameters the second and the third are optional, if you passed only one parameter, the where() method will understand that there's a field called id and you wanna update the table where its id equals to that parameter like this :

$db->update('mytable',
	[
		'first_name' => 'Ashraf',
		'last_name' => 'Hefny',
		'age'	=> 28
	])->where(1)->exec();

SQL Query :

UPDATE `mytable` SET `first_name` = ?, `last_name` = ?, `age` = ? WHERE `mytable`.`id` = ?

We use exec() method to execute the query, that means you can use getSQL() method to check the query before you execute it without exec(). You can use more than one where() method the same way like this :

$db->update('mytable',
	[
		'first_name' => 'Osama',
		'last_name' => 'El-Zero',
		'age'	=> 30
	])->where(1)->where('first_name','Ashraf')->exec();

SQL Query :

UPDATE `mytable` SET `first_name` = ?, `last_name` = ?, `age` = ? WHERE `mytable`.`id` = ? AND `mytable`.`first_name` = ?

As you see, if you provide the where method with 2 parameters it will understand that you wanna update the table where the column name is the first parameter where it is equal to the value of the second parameter. And also if you noticed that the second where becomes 'AND' in the query.

$db->update('mytable',
	[
		'first_name' => 'Ali',
		'last_name' => 'Hamdy',
		'age'	=> 30
	])->where(1)->where('age','>',20)->exec();

SQL Query :

UPDATE `mytable` SET `first_name` = ?, `last_name` = ?, `age` = ? WHERE `mytable`.`id` = ? AND `mytable`.`age` > ?

Now what if we wanted to add OR to our where clause? ###How to use orWhere() method? orWhere() acts exactly like where() method and it takes the same parameters it's like 'OR' in SQL and you can use both methods together like this :

$db->update('mytable',
	[
		'first_name' => 'Muhammad',
		'last_name' => 'Mustafa',
		'age'	=> 21
	])->where('age','<=',20)->orWhere(1)->exec();

SQL Query :

UPDATE `mytable` SET `first_name` = ?, `last_name` = ?, `age` = ? WHERE `mytable`.`age` <= ? OR `mytable`.`id` = ?

And also you can pass an array of where clauses to where() or orWhere() method like this :

->where([ ['first_name', 'Marei'], ['age >=', 18], [1] ])->exec();

SQL would be like this :

WHERE `first_name` = ? AND age >= ? AND id = ?

You can also use a combination of where() and orWhere() methods whith single caluse or with a group of where clauses like this :

->where([ ['first_name', 'Marei'], ['age >=', 18]])->where(1)->orWhere([ [5], ['last_name', 'Morsy'] ])->exec();

SQL would be like this :

WHERE `first_name` = ? AND age >= ? AND `id` = ? OR `id` = ? Or `last_name` = ?

As you notice that you can use where() and orWhere() not only with upadte() method, but also with other query methods such as delete(), update() and table(). ###Delete values from table use delete() method to delete rows from table, it holds 2 parameters, the first one is table name and the second one is optional, it acts exactly like the third parameter in update() method so, you can pass only the id as integer value, you can pass an array of the field name and the value, you can pass an array of the field name and parameter and value, you can pass an array of arrays of where clauses. And here are some exampl