A lightweight and flexible PHP query builder for simplifying database operations like SELECT, WHERE, LIMIT, ORDER BY, BETWEEN, and more. The class allows you to build complex queries using method chaining while maintaining SQL injection protection through prepared statements.
- Download the project files.
- Extract the files to your server directory.
- Include the
autoload.phpfile located inside theDBreezefolder in your project.
Define your Database
You can do this by editing the app.env file inside the DBreeze/config folder, you can find 2 settings there, one for local development and the other for live development. This makes database definition easy either you're developing locally or you just host your project, you won't have to update the database settings every time.
Once that is done, you can start development.
Creating tables has also been made easy with DBreeze, you won't have to manually create tables and its structures, and you can do all that within your project
Example:
- Determine your table name.
- Determine the table structure.
- Create a class to handle the table requests.
Inside DBreeze/AppTables, create a file with your table name as the file name e.g users.php for the user's table
Inside the file paste the following code
<?php
class Users extends BaseModel
{
// Define the table name
protected static $table = 'users';
// Define the table structure
protected static $tableStructure = [
'id' => 'INT(100) AUTO_INCREMENT PRIMARY KEY',
'username' => 'varchar(100) NOT NULL',
'email' => 'varchar(100) NOT NULL',
'password' => 'varchar(500) NOT NULL',
'password_reset' => 'varchar(200) NOT NULL',
'created_at' => 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP',
'updated_at' => 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'
];
}
?>- The class name should be your table name, and also assign your table name to
$table $tableStructureshould contain your desired table structure, this allows you to update or create the table and its structure within your project
Once the above has been done, go ahead and run the below code in your index.php
<?php
$create_users_table = Users::TableStructure()->create();
print_r($create_users_table);
?>This returns either true or false.
Others
$create_users_table = Users::TableStructure()->update(); to update the table structure in the database if you made any changes to the table structure in the table class.
$create_users_table = Users::TableStructure()->drop(); to delete the table.
There are several ways to build queries using DBreeze
SELECT
<?php
// First example without using table classes
$users = $db->table('users')->select('*')->run();
print_r($users);
exit();
// Second example with table classes
$users = Users::select('*')->run();
print_r($users);
exit();
?>Both examples select all rows from the user's table.
INSERT
<?php
$password = md5('password');
// Insert into the user's table
$users = $db->table('users')->insert([
'username' => 'John',
'email' => 'john@gmail.com',
'password' => $password
])->run();
print_r($users);
exit();
// Insert into the user's table
$users = Users::insert([
'username' => 'John',
'email' => 'john@gmail.com',
'password' => $password
])->run();
print_r($users);
exit();
?>When inserting, you do not need to provide created_at and updated_at column values, those columns get filled when inserting new records.
UPDATING
<?php
// Update the user's table
$users = Users::update(['username' => 'JohnDoe'], ['id' => 1])->run();
print_r($users);
exit();
?>update() takes 2 arguments, 1st is the data to update, 2nd is the condition for updating.
The query would look like UPDATE users SET username = 'John Doe' WHERE id = 1 ORDER BY id DESC
DELETE
<?php
// Delete from users table
$user = Users::delete()->where(['id' => 1])->run();
?>FIND
<?php
// Find id = 1 from the user's table
$user = Users::find(1)->run();
// Find also takes an array
$user = Users::find(['username' => 'yemi'])->run();
?>DISTINCT
<?php
// Select distinct values
$users = Users::select()->distinct('email')->run();
// Query: SELECT DISTINCT email FROM users ORDER BY id DESC
?>BETWEEN
<?php
// Between method takes a column name as the first argument
$users = Users::select()->between('age', [20, 30])->run();
?>LIMIT
<?php
// Limit the results been fetched
$users = Users::select()->limit(5)->run();
// Limit with offset. Retrieve 5 records starting from the 10th record (offset of 10).
$users = Users::select()->limit(10, 5)->run();
?>ORDER
<?php
// Default order uses ORDER BY id DESC
$users = Users::select('*')->run();
// Specify a order using order()
$users = Users::select()->order('username', 'ASC')->run();
$users = Users::select()->order('username', 'DESC')->run();
?>RAW
<?php
// Use raw SQL for more advanced queries.
$users = Users::select()->raw("id > 5 AND username = 'yemi'")->run();
?>COUNT
<?php
// count() takes a parameter as a condition to perform the count
$users = Users::count(['id' => '> 1'])->run();
// Query: SELECT COUNT(*) as total FROM users WHERE id > 1 ORDER BY id DESC
?>SUM
<?php
// sum() takes 2 parameters, the column to sum and the condition
$users = Users::sum('id', ['id' => '> 1'])->run();
// Query = SELECT SUM(id) as total FROM users WHERE id > 1 ORDER BY id DESC
?>COMPARISON OPERATORS like >=, <=, >, <, !=, =, ||, %value%
<?php
// '>'
$users = Users::select()->where(['id' => '> 4'])->run();
// Query: SELECT * FROM users WHERE id > 4 ORDER BY id DESC
// ------------------------------------------------------------------
// '<'
$users = Users::select()->where(['id' => '< 4'])->run();
// Query: SELECT * FROM users WHERE id < 4 ORDER BY id DESC
// ------------------------------------------------------------------
// '<='
$users = Users::select()->where(['id' => '<= 4'])->run();
// Query: SELECT * FROM users WHERE id <= 4 ORDER BY id DESC
// ------------------------------------------------------------------
// '>='
$users = Users::select()->where(['id' => '>= 4'])->run();
// Query: SELECT * FROM users WHERE id >= 4 ORDER BY id DESC
// ------------------------------------------------------------------
// '!='
$users = Users::select()->where(['id' => '!= 4'])->run();
// Query: SELECT * FROM users WHERE id != 4 ORDER BY id DESC
// ------------------------------------------------------------------
// '='
$users = Users::select()->where(['id' => '= 4'])->run();
// Query: SELECT * FROM users WHERE id = 4 ORDER BY id DESC
// ------------------------------------------------------------------
// '||'
$users = Users::select()->where(['id' => '4 || 5'])->run();
// Query: SELECT * FROM users WHERE id = 4 OR id = 5 ORDER BY id DESC
// ------------------------------------------------------------------
// '%value%' => LIKE
$users = Users::select()->where(['username' => '%yemi%'])->run();
// Query: SELECT * FROM users WHERE user_name LIKE %yemi% ORDER BY id DESC
?>From all the examples above, we can see DBreeze allows method chaining which can come in very handy and saves a lot of time when making a query and you also do not have to worry about SQL injection
More elaborate query
<?php
$users = Users::select()
->where(['id' => '> 1'])
->between('id', [3, 10])
->order('id', 'ASC')
->limit(5)
->run();
// Query: SELECT * FROM users WHERE id > 1 AND id BETWEEN 3 AND 5 ORDER BY id ASC LIMIT 5
$users = $db->table('users')
->select()
->where(['id' => '> 1'])
->between('id', [3, 10])
->order('id', 'ASC')
->limit(5)
->run();
// Query: SELECT * FROM users WHERE id > 1 AND id BETWEEN 3 AND 5 ORDER BY id ASC LIMIT 5
?>Contributions to DBreeze are welcomed! Feel free to submit pull requests or open issues if you encounter any problems or have suggestions for improvement.
This project does not rely on any external dependencies, making it easy to set up and use.
This project is licensed under the MIT License.