Table of Contents
This is a secure and robust PHP query builder that uses PDO to interact with the database. The query builder is designed to prevent SQL injection attacks and ensure that all user input is properly sanitized and validated. An easy-to-use interface that can be customized to suit your specific needs. Whether you're building a small application or a large-scale enterprise system this query builder provides the flexibility and scalability you need to manage your database queries efficiently. With its reliable performance and user-friendly interface this PHP query builder is the perfect tool for anyone who wants to build powerful and efficient applications using PDO.
Note: This query builder is inspired from codeigniter so the functions may be familiar to you if you have used codeigniter query builder before but under the hood they are different
Here're some of the project's best features:
- Secure and robust PHP query builder
- Uses PDO for safe and convenient database interaction
- Prevents SQL injection attacks using prepared statements and parameterized queries
- Provides error handling and debugging features to help identify and fix issues quickly
- Easy to use and customize for your specific needs
Upcoming feature and function still about to come:
- Support for multiple database drivers.
- Where_in(), where_not_in(), having(), not_like() function.
- Helper functions like distinct(),max(),sum(),avg() etc functions.
- Insert_branch(), Update_branch for multiple insert and update.
- DB Transaction
- Empty,Truncate Table
- Cache system
1. Clone The Repository
git clone https://github.com/S4F4Y4T/pdoQueryHunter
2. Open app/Congig/Config.php and update database information
3. Create model inside app/Model and extend Main_model
Call this function to retrieve all the records from table
$data = $this->db->execute('table');
//output: select * from table
The first parameter accept the table name and second parameter enable you to set limit. Here is an example:
$data = $this->db->execute('table', 20);
$data = $this->db->execute('table', 10, 20);
//output: SELECT * FROM table LIMIT 20
//output: SELECT * FROM table LIMIT 10,20
Go to Query Result to see how to use this query to show result
Generate the select query like $this->db->execute() but does not run the query but return the query as string:
$data = $this->db->buildFetchQuery('table', 20);
//output string: select * from table LIMIT 20
Call this function which enable you to use the select column of query. Here is an example:
$this->db->select('id,name');
$data = $this->db->execute('table');
//output: select id,name from table
By default all (*) column is selecting
Let you select the table you want to fetch records from. Here is an example:
$this->db->select('id,name');
$this->db->table('table2');
$data = $this->db->execute();
//output: select id,name from table2
You can select the table on $this->db->table() or $this->db->execute(), you can use method whichever you prefer
Let you join table. Here is an example:
$this->db->select('id,name');
$this->db->table('table');
$this->db->join('table2', 'table2.id = table.table2_id');
$data = $this->db->execute('table');
//output: SELECT id, name FROM table, table INNER JOIN table2 ON table2.id = table.table2_id
You can call this function multiple time if you need to join multiple table
If you need specific type of join you can define it on third parameter, Options are: inner, outer, left, right, cross
$this->db->select('id,name');
$this->db->table('table');
$this->db->join('table2', 'table2.id = table.table2_id', 'left');
$data = $this->db->execute('table');
output: SELECT id, name FROM table, table LEFT JOIN table2 ON table2.id = table.table2_id
This function enables you to set WHERE clauses using one of these methods::
$this->db->where('id', 1);
//output: WHERE id = :id
If you use multiple function calls they will be chained together with AND between them:
$this->db->where('id', 1);
$this->db->where('name', 'safayat');
//output: WHERE id = :id AND name = :name
$this->db->where('id >', 1);
$this->db->where('id !=', 3);
//output: WHERE id > :id AND id != :id
Operator options are: =, !=, <>, <, >, <=, >=
You can also pass associative array to the function
$cond = [
'id' => 1,
'id !=' => 3
];
$this->db->where($cond);
//output: WHERE id = :id AND id != :id
This function is same as the above one except multiple functions are join by OR. Here is an example:
$this->db->where('id', 1);
$this->db->or_where('id', 2);
//output: WHERE id = :id OR id = :id
This function let you order your query result. Here is an example:
$this->db->order('id', 'desc');
//output: ORDER BY id desc
You can also call this function multiple time to join them. Options for ordering are: asc, desc, random
This function let you limit number or result return by the query query. Here is an example:
$this->db->limit(5);
//output: LIMIT 5
Second parameter lets you set a result offset.
$this->db->limit(5, 10);
//output: LIMIT 5,10
This function let you search through your data. Here is an example:
$this->db->like('name', 'safayat');
//output: WHERE `name` LIKE '%safayat%'
You can use the third parameter to define where the wild card will be place. Options are: first,last,none,both
$this->db->like('name', 'safayat', 'first');
$this->db->like('name', 'safayat', 'last');
$this->db->like('name', 'safayat', 'none');
$this->db->like('name', 'safayat', 'both');
//output: WHERE `name` LIKE '%safayat'
//output: WHERE `name` LIKE 'safayat%'
//output: WHERE `name` LIKE 'safayat'
//output: WHERE `name` LIKE '%safayat%'
You can also pass associative array. Here is an example
Same as above function but join the function with OR
$data = [
'name' => 'safayat'
];
$this->db->like($data);
//output: WHERE `name` LIKE '%safayat%'
Query grouping allows you to create groups of WHERE clauses by enclosing them in brackets. This will allow you to create queries with complex WHERE clauses. Here is an example:
$this->db->where('category', 'fruit');
$this->db->group_start();
$this->db->where('price <', 10);
$this->db->or_where('quantity >', 20);
$this->db->group_end();
$this->db->where('value <', 10);
$this->db->execute('table');
//output: WHERE category = :category AND (price < :price OR quantity > :quantity) AND value < :value
This function let you use group by in your query. Here is an example:
$this->db->group_by('id,name');
//output: GROUP BY id,name
The query is assigned to a variable $data which can be used to output results.
This method return result as an array of object. normally you will use this to foreach loop. Here is an example
foreach($data->fetch() as $result)
{
echo $result->value1.'<br>';
echo $result->value2'<br>';
echo $result->value3'<br>';
}
This method return result as an array. Here is an example
foreach($data->fetch_array() as $result)
{
echo $result->value1.'<br>';
echo $result->value2'<br>';
echo $result->value3'<br>';
}
This method return the number of rows return by the query. Here is an example
$data = $this->db->execute('table');
echo "number of rows:". $data->count();
Generate the insert query string and run based on the data you provided. Here is an example:
$data = [
'key1' => 'value1',
'key2' => 'value2',
'key3' => 'value3'
];
$this->db->insert('table', $data);
//query: INSERT INTO table(key1,key2,key3) VALUES(:key1, :key2, :key3)
The first parameter will contain the table name as string and second parameter will contain associative array
Generate the insert query like $this->db->insert() but does not run the query but return the query as string:
$data = [
'key1' => 'value1',
'key2' => 'value2',
'key3' => 'value3'
];
$this->db->buildInsertQuery('table', $data);
//output string: INSERT INTO table(key1,key2,key3) VALUES(:key1, :key2, :key3)
This function enables you to set values for inserts or updates instead of passing the data directly to the function. If you call the function multiple times they will assemble properly for insert or update as well.
$this->db->set('key1', 'value1');
$this->db->set('key2', 'value2');
$this->db->set('key3', 'value3');
$this->db->insert('table', $data);
You can also pass associative array
$data = [
'key1' => 'value1',
'key2' => 'value2',
'key3' => 'value3'
];
$this->db->set($data);
$this->db->insert('table', $data);
This function return you the last inserted id. Here is an example
$this->db->set('key1', 'value1');
$this->db->set('key2', 'value2');
$this->db->set('key3', 'value3');
$this->db->insert('table', $data);
echo $this->db->insert_id();
//output: 1
Generate the update query string and run based on the data you provided. Here is an example:
$data = [
'key1' => 'value1',
'key2' => 'value2',
'key3' => 'value3'
];
$this->db->update('table', $data, ['id' => 1]);
//query: UPDATE table SET key1=:key1,key2=:key2,key3=:key3 WHERE id = :id
The first parameter will contain the table name as string, second parameter will contain associative array of data and the third parameter will contain the where condition as associative array
You can also use $this->db->where() function which will allow you to set the WHERE condition. Here is a example:
$data = [
'key1' => 'value1',
'key2' => 'value2',
'key3' => 'value3'
];
$this->db->where('id', 1);
$this->db->update('table', $data);
//output: UPDATE table SET key1=:key1,key2=:key2,key3=:key3 WHERE id = :id
Generate the update query like $this->db->update() but does not run the query but return the query as string:
$data = [
'key1' => 'value1',
'key2' => 'value2',
'key3' => 'value3'
];
$this->db->where('id', 1);
$this->db->buildUpdateQuery('table', $data);
//output: UPDATE table SET key1=:key1,key2=:key2,key3=:key3 WHERE id = :id
Generate the delete query and run based on the data you provided. Here is an example:
$this->db->delete('table', ['id' => 1]);
Query: DELETE FROM table WHERE id = :id
The first parameter will contain the table name as string, second parameter will contain the where condition as associative array. You can also use the where() or or_where() functions instead of passing the data to the second parameter of the function.
$this->db->where('id', 1);
$this->db->where('id <', 5);
$this->db->delete('table');
Generate the delete query like $this->db->delete() but does not run the query but return the query as string:
$this->db->where('id', 1);
$this->db->buildDeleteQuery('table');
//output: DELETE FROM table WHERE id = :id
Method chaining allows you to simplify your syntax by connecting multiple functions. Here is an example
$query = $this->db->select('title')
->where('id', 1)
->limit(1, 10)
->execute('mytable');
Distributed under the MIT License. See LICENSE.txt for more information.