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.

Leaf has provided a new method to make retrieving data even simpler and more organised. choose

db select #

Getting all rows from a table #

To do this, we use the select() methode. 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();

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", "*", "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", "id = 2")->fetchObj();

Limit data #

Limiting data is also very simple with Leaf DB

// get the latest 10 posts
$users = $db->select("posts ORDER BY id DESC LIMIT 10")->fetchAll();

// with parameters
$books = $db->select("books", "*", "author = ? ORDER BY id DESC LIMIT 5", [$author])->fetchAll();

Using Prepared Statements #

Prepared statements help protect against SQL injection,...

$db->select("users", "*", "username = ? AND password = ?", [$username, $password])->fetchObj();

Db choose #

choose simply offers a more consice, powerful way to retrieve data from a database. It also uses prepared statements by default, so you're safe in that respect.

For basic uses, we'll recommend you use select:

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

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

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

Getting a particular row from a table #

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

$db->choose("users", "*", ["id" => 2])->fetchObj();

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

$db->choose("users", "username, mobile", ["email" => "mychi@leafphp.dev"])->fetchObj();

Data Options #

So unlike select, choose takes in an array, which is much clearner than writing partial SQL queries, also, the params you pass in are safe from SQL injection.

Also, unlike select, you seperate data options like LIMIT and ORDER into a 4th parameter

$db->choose("books", "*", ["author" => "mychi.darko", "published" => "2019"], "LIMIT 5");

Validation #

choose also has inbuilt validation which validates parameters according to set rules. This uses the Leaf\Form->validate method. You can check it out for more information on validation.

choose takes in a fifth parameter which is a boolean, this is whether of not to validate the data passed into choose using the default checks.

By default, choose validates values with the keys: email, username and any other field is marked as required. If any of the validations fail, an error is raised. You can turn this feature off:

$db->choose("books", "*", ["author" => "mychi.darko", "published" => "2019"], "LIMIT 5", false);

Custom Validation #

This is the sixth parameter of choose. These are custom rules that you set to validate.

$db->choose("books", "*", ["author" => "mychi.darko", "published" => "2019"], "LIMIT 5", false, [
  "author" => "validUsername",
  "published" => "number"
]);

Here, we're telling choose that the author parameter should be a valid username, and the published param should contain only numbers. If any of these conditions(rules) are not met, the application throws an error and breaks.

You can view all validation rules here

$db->choose($table, $fields, $params, $options, $defaultChecks, $validation);