, (*1)
About BITbuilder
BITbuilder is a MySQL querybuilder created by software development interns at Brainstorm IT and uses the PHP Data Objects extension (PDO) for accessing databases.
This querybuilder is designed to be as simple as possible with almost no learning curve attached.
Laravels querybuilder has been an inspiration to how we wanted the BITbuilder syntax to look like, so syntax wise a few comparisons can be made., (*2)
Requirements
Installation
You can install BITbuilder through composer:, (*3)
composer require 'brainstormit/bitbuilder'
Get started!
First things first! We need a PDO Database object for BITbuilder to work.
The Database helper class can easily create one (You don't necessarily need to use this).
Navigate to src/helpers/Database.php and edit your database configurations:, (*4)
$this->db_type = 'mysql';
$this->db_host = 'localhost';
$this->db_name = 'qbtest';
$this->db_username = 'root';
$this->db_password = '';
With the database configurations all set up we can create our database object,
and even more important: our first BITbuilder object!, (*5)
$db = new Database();
// BITbuilder object
$b = new Builder($db);
Selecting your table
Before we start with the fun stuff we need to select
the table we want to work with.
Let's assume we want to select the users table:, (*6)
$tbl = $b->table('users');
SELECT statements
If you want to select all users in the users table we can perform an
SELECT * with the following:, (*7)
$record = $tbl->select('*')->fetchAll();
It's also possible to provide an array with all the fields you want to select:, (*8)
$fields = array('first_name', 'last_name', 'email');
$record = $tbl->select($fields)->fetchAll();
WHERE clauses
You can add a WHERE clause to your statement with the following:, (*9)
$record = $tbl->select(['first_name', 'email'])
->where('id', 89)
->fetchAll();
A different operator can be provided as second parameter.
The third parameter then becomes the value:, (*10)
$record = $tbl->select(['first_name', 'email'])
->where('id', '<=', 51)
->fetch();
Valid operators: = != < > >= <=, (*11)
AND & OR operators
OR & AND operators can be added to your clauses with the following:, (*12)
// AND operator
$record = $tbl->select(['first_name', 'email'])
->where('id', 23)
->and_('email', '!=', 'johndoe@example.com')
->fetchAll();
// OR operator
$record = $tbl->select(['first_name', 'email'])
->where('id', 69)
->or_('last_name', '=', 'Smith')
->fetchAll();
and_ and or_ have an underscore after their method name because PHP doesn't allow PHP reserved names
to be used as method names., (*13)
ORDER BY keyword
Ordering a selected record can be done with the following:, (*14)
$record = $tbl->select('*')
->orderBy('id')
->fetchAll();
The default order is ascending. This can easily be changed by adding DESC as second parameter:, (*15)
->orderBy('id', 'DESC')
GROUP BY keyword
Grouping a selected record can be done with the following:, (*16)
$record = $tbl->select('*')
->GroupBy('email')
->fetchAll();
LIMIT keyword
Limiting the amount of items in a record can be done by adding a LIMIT to your query:, (*17)
$record = $tbl->select('*')
->limit(5)
->fetchAl();
INSERT statements
Inserting a new record into the users table would look similar to this:, (*18)
$insert = [
'first_name' => 'John',
'last_name' => 'Doe',
'email' => 'johndoe@example.com'
];
$tbl->insert($insert);
The array which contains your insert info has to be associative.
The array key represents the table field, and the array value represents
the value you want to insert into your table., (*19)
DELETE statements
Deleting a record from the users table would look similar to this:, (*20)
$tbl->delete('id', 44);
The second and third parameter represents the WHERE clause.
A where clause can also be added manually:, (*21)
$tbl->delete()
->where('id', 44)
->exec()
The exec() method is needed to manually execute the query., (*22)
UPDATE statements
Updating a record in the users table would look similar to this:, (*23)
$update = array('first_name' => 'Crazy', 'last_name' => 'Frog');
$tbl->update($update, 'id', 59);
The array which contains your update info has to be associative, just like the insert() method.
The array key represents the table field you want to update, and the array value represents
the value.
, (*24)
Just like the delete() method it's possible to manually add a WHERE clause if you'd like:, (*25)
$update = array('first_name' => 'Crazy', 'last_name' => 'Frog');
$tbl->update($update)
->where('id', 59)
->exec();
Joins
Let's assume we want to develop a platform where users can post pictures.
If you want to select all pictures that belong to a certain user,
your join would look similar to this:, (*26)
// pictures table
$tbl = $b->table('pictures AS p');
$join = $tbl->select('*')
->join('users AS u', 'p.userid', 'u.id')
->fetchAll();
The table yould want to join with should be passed as the first parameter.
The second and third parameter represent the ON of the join, (*27)
Available joins: INNER JOIN (join()), LEFT JOIN (leftJoin()), RIGHT JOIN (rightJoin()), OUTER JOIN (outerJoin()), (*28)
Executing raw queries
Raw queries can be executed as well:, (*29)
$tbl->raw('SELECT COUNT(*) FROM users')->fetchAll();
or:, (*30)
$tbl->raw('DELETE FROM users WHERE id = 77')->exec();
License
BITbuilder is open-sourced software licensed under the MIT license., (*31)
Wallogit.com
