README

Note: This repository requires the use of the BasePHP framework. If you would like to learn more about the framework, visit BasePHP.

BasePHP Component - Database

Database and Query Builder for BasePHP.

Quick Links:

BasePHP Framework
Example Application
BasePHP Component - Database

Installation

(1) Add sensitive information in your .env file

DB_USER=admin
DB_PASS=password
DB_HOST=127.0.0.1
DB_NAME=database
DB_PORT=3306
DB_DRIVER=MySQLi

(2) If you do not have the /config/db.php, then copy the db.example.php from this repo and rename it.

Examples

use \Base\Support\Facades\DB;

Results:

// get a single user from the database
$user = DB::table('users')->where('id',912864)->first();

// get all users in the database, order the results
$users = DB::table('users')->order('id ASC')->get();

// get all users that have gmail email address (writing custom SQL)
$users = DB::table('users')->where('email LIKE "%gmail.com" ')->get();

// count how many users are enabled
$enabledUsers = DB::table('users')->where(['status'=>'enabled'])->count();

// get most recent 10 enabled users
$users = DB::table('users')
            ->select(['id','name'])
            ->where(['status'=>'enabled'])
            ->limit(10)
            ->order('id DESC')
            ->get();

// get all the cities that have over 1,000,000 population
$cities = DB::table('postal_codes')
            ->group('city')
            ->having('population > 1000000')
            ->get();

// get the average price of all ebooks from the products table
$avgPrice = DB::table('products')->where('category','ebooks')->avg('price');

Update items:

// change user's name
DB::table('users')
    ->where('id',912864)
    ->update([
        'name' => 'John Smith'
    ]);

// increase this users "page view" count
DB::table('users')->where('id',9983287)->increment('page_view',1);

Insert items:

// add a new user to the table, and return the new ID
$newUserId =  DB::table('users')
                ->insert([
                    'name' => 'John Smith',
                    'email' => 'jsmith@email.com'
                ]);

Delete items:

// delete a user by id
DB::table('users')
    ->where('id',912864)
    ->delete();

// delete all users with deleted = 1
DB::table('users')
    ->where(['deleted' => 1])
    ->delete();

RAW SQL:

// Writing a RAW SQL query to get 10 products from the database.
$products = DB::query("SELECT * FROM products WHERE status = 'enabled' LIMIT 10");
foreach($products as $product)
{
    // display products here
}

// get a single products
$product = DB::query("SELECT * FROM products WHERE id = '$productId'")->first();

// writing an update query
DB::query("UPDATE products SET price = 61.41 WHERE id = '$id' ");

// writing raw queries (without the query builder)
$newUserId = DB::query("INSERT INTO users WHERE name = 'John Smith', email = 'jsmith@email.com' ");

Query Builder

These methods are stackable

Note: For every new query, first use the table() method.

Execute Queries

These methods execute "read" queries and return database results

These methods execute "write" queries

Write a raw query

Note: Raw Queries will return results into a Collection, unless you are "writing" to the database.

Note: Queries that use INSERT will return the insert_id automatically

Utility Methods:

Note: all values passed into query methods (not custom SQL's) automatically run through escape()

Database Support

Currently only supports MySQL connections

basephp / database

Maintainers

Details