Menu
Leaf DB
Leaf Docs
Getting Started New in v1.5.0
Versions
Version 1.2.0 Version 1.3.0 Version 1.4.1 Version 1.4.2 Version 1.5.0
Introduction
Re-routing to index Your First Leaf App
Config
Database App Headers
Core Modules
Simple field Validation Date Manipulation Database Leaf Auth Request Response Session File System Leaf Token Core Api Controller Core Controller Core Model Simple Authentication
Routing
Simple routing 404 handling Sub routing Named parameters PCRE based patterns Optional Sub-patterns Using Controllers Integrating with other libraries Using Middleware Sub-folder Support
Helpers
Constants JWT

Leaf DB
Leaf's simple database query builder

Leaf DB Overview

Introduction

Leaf's "simple query builder" provides a convenient but usual way to quickly create and run database queries. It can be used to perform most database operations in your app.

Leaf's "simple query builder" currently supports Mysqli and PDO connections, though we still recommend using Mysqli. There's no need to worry about SQL injection as parameter binding is also supported and easy to use😉💪

Init

Initialising Leaf DB

Leaf DB has 2 different packages, 1 for mysqli and the other for PDO. So you can import which ever package you wish to use. Leaf recommends using the mysqli package.

use Leaf\Core\DB\Mysqli; $db = new Mysqli();
use Leaf\Core\DB\PDO; $db = new PDO();

Both DB:PDO and DB:Mysqli use the same methods, so all the code below works the same for whichever you're using. We'll alert you if something works differently.

DB connection

The first thing you need to do to use Leaf DB is to connect to your database. This can be achieved with connect()

use Leaf\Core\DB\Mysqli; $db = new Mysqli(); $db->connect($host, $user, $password, $dbname);
use Leaf\Core\DB\PDO; $db = new PDO(); $db->connect($host, $dbname, $user, $password);
This will set the connection for use within Leaf DB, but if you also want to save this connection in a variable, you can do so.
$connection = $db->connect($host, $dbname, $user, $password);

Queries

Making simple queries

Queries with with Leaf DB are much like what you're used to. Though a query builder, we wan't to maintain the flexibility of normal database queries, hence, we provided the query() method to make your normal database queries.

$db = new Leaf\Core\DB\Mysqli(); $db->connect($host, $user, $password, $dbname); $leaf->post('/users/all', function() use($db) { $users = $db->query("SELECT username FROM users")->fetchAll(); });

As normal as this seems, we take it a step further by providing you with a much simpler way to use prepared statements.

$db = new Leaf\Core\DB\Mysqli(); $db->connect($host, $user, $password, $dbname); $leaf->post('/users/{id}', function($id) use($db) { $users = $db->query("SELECT username FROM users WHERE id = ?", [$id])->fetchAll(); });

We've looked at making queries, but then query() still makes you type out whatever query you need to use. It's certainly easier than raw queries, but it's nothing impressive. Below are some of Leaf DB's handy methods to make queries even easier.💪😉

Retrieving Data

If you're attempting to use this, you've probably seen or used SELECT statements before. Leaf DB has provided an even easier way to use select.

$db->select();

Getting all rows from a table

To do this, we use the select() method we saw above. All that we have to do is to pass in the table we want to retrieve. For example, to get all users from the "users" table, we simply do:

$db->select("users");

To actually get the results, we'll have to chain fetchAll() to the select method. fetchAll() does the same thing that mysqli_fetch_all() does to an mysqli result

$db->select("users")->fetchAll();

To add a limit to the query, just use:

$db->select("users", "*", "LIMIT 10")->fetchObj();

Getting a column from a table

Getting a single column, eg: getting all usernames from the users table

$db->select("users", "username")->fetchAll();

This is like saying SELECT username FROM users. You can also pass in multiple options

$db->select("users", "username, email")->fetchAll();

You can get all columns with:

$db->select("users")->fetchAll(); # or $db->select("users", "*")->fetchAll();

Getting a particular row from a table

Getting a particular row, eg: getting the user with the id of 1 from the users table. You acan achieve this with:

$db->select("users", "*", "WHERE id = 2")->fetchObj();

fetchObj does the same thing as mysqli_fetch_obj and fetch(PDO::FETCH_OBJ)

If you don't need the whole row, you can use:

$db->select("users", "username, email", "WHERE id = 2")->fetchObj();

Inserting Data

This operation uses INSERT. With Leaf DB:

$db->insert();

Inserting Data

We use Leaf DB's insert method which takes in a "table" to insert data, a "column" and it's "value"

$db->insert("posts", "title", "This is post One");

You can also add multiple columns like so:

$db->insert("posts", "title, body", "post One, This is the body of post One");

Updating Data

This operation uses UPDATE. With Leaf DB:

$db->update();

Update

We use Leaf DB's update method which takes in a "table", a "column-value" to update and "conditions".

$db->update("posts", "title = 'Post 1'", "title = 'Post One'");

This will look for a post with the title of "Post One" and change it to "Post 1".
You can also have multiple options:

$db->update("posts", "title = 'Post 1'", "id = '1'");

Deleting Data

This operation uses DELETE. With Leaf DB:

$db->delete();

Delete

We use Leaf DB's delete method which takes in a "table", and "conditions".

$db->delete("posts", "title = 'Post 1'");

This will look for a post with the title of "Post 1" and delete it.
You can also have multiple conditions:

$db->update("posts", "title = 'Post 1' AND author = 'Mychi Darko'");

Others

Row Count

Get the number of rows from select

$db->select("posts")->count();

Connection Close

Close the connection

$db->close();

Next Steps

Re-routing to index.php
Simple Routing
Request
Response
pic
Free & Open Source (MIT)