Database and Models

Configuring Database

If you haven't already done so, fill the required details in config/database.php. All options in file are well commented and are self explanatory.

Running raw queries

In your controller (or from any part of your app, even a view, although not recommended) you can execute any SQL query using the below syntax

//if your query will return a resultset
$result = DB::sel("SELECT * FROM table");

//simply to execute a query
DB::query("SELECT * FROM table");

//Insert query
$insert_id = DB::insert("INSERT INTO user (name, age) VALUES ('John', 15)");

//Update query
$no_of_affected_rows = DB::update("UPDATE user SET name='Ashley' WHERE id=6");

//Delete query
$no_of_affected_rows = DB::delete("DELETE FROM users WHERE id=6");
Recommended to use DB class in Controller and Model only
PHPasap uses an alias loader class and an autoloader to load the DB class. Technically you can use the DB class anywhere in your app. Be it a view or library or any helper file that too namespaced under anything. The autoloader will take care of loading it for you. However as per the standards of MVC it is pretty much a bad practise to make a database query anywhere other than a Controller or a model. So unless and until there is no option avoid doing so.

Binding data

If any part of your query comes form user input you can bind the data using the ? operator. See example below

//Select query
$name = $_GET["name"];
$age = $_GET["age"];
$query = DB::sel("SELECT * FROM user WHERE name=? AND age=?",[$name, $age]);

//Insert query with bind data
$insert_id = DB::insert("INSERT INTO user (name, age) VALUES (?,?)",[$name, $age]);

//Update query
$no_of_affected_rows = DB::update("UPDATE user SET name=? WHERE id=?", ["Ashley", 6]);

//Delete query
$no_of_affected_rows = DB::delete("DELETE FROM users WHERE id=?", [6]);

DB::query("INSERT INTO user (name, age) VALUES (?,?)",[$name, $age]);

What happends here is that PHPasap crates a PDO prepared statement and then executes it. This rules out any chances of SQL injection.

Select query

To run a SELECT query like SELECT * FROM users you would use the below syntax.

DB::table("users")->get();

If no results then get() returns empty array

Select where

To run a SELECT query like SELECT * FROM users WHERE name="John" you would use the below syntax.

DB::table("users")->where("name", "=", "John")->get();

Note that you can pass raw data inside where since all the statements are executed using PDO prepare. So below is usually how you will use this syntax in real world application.

$name = $_GET["name"];
DB::table("users")->where("name", "=", $name)->get();

Select with multiple where

DB::table("users")->where("name", "=", "John")->where("dob", ">", "2015-02-01")->get();
//all conditions in where will be AND'ed

Select where with limit

DB::table("users")->where("name", "=", "John")->get(5);
//SELECT * FROM users WHERE name = "John" LIMIT 0, 5

Select where with limit and offset

DB::table("users")->where("name", "=", "John")->get(5,10);
//SELECT * FROM users WHERE name = "John" LIMIT 10, 5

Select first

DB::table("users")->first();
DB::table("users")->where("name", "=", "John")->first();

If no results then first() returns empty array.

Select count

DB::table("users")->count();
DB::table("users")->where("name", "=", "John")->count();

Select where in

DB::table("users")->where_in("name", ["John", "Barry", "Ashley"])->get();

Select where like

DB::table("users")->where("name", "=". "%ohn%")->get();

Select where between

DB::table("users")->where_between("registered_on", ["2015-12-01", "2016-02-02"])->get();

//you can combine where and where_between. Remember they will be AND'ed
DB::table("users")->where("name", "=", "John")->where_between("registered_on", ["2015-12-01", "2016-02-02"])->get();

Select or where

DB::table("users")->where("name", "=", "John")->or_where("name", "=", "Ashley")->get();

Select specific columns

DB::table("users")->select("name, age")->get();

DB::table("users")->where("name", "=", "John")->or_where("name", "=", "Ashley")->select("name, age")->get();
DB::table("users")->where("name", "=", "John")->or_where("name", "=", "Ashley")->select("name, age")->first();

Insert

$insert_id = DB::table("users")->insert(["name"=>"John", "dob"=>"2000-10-08", "age"=>13]);

insert() will return primary key id of the inserted row

Update

$affected_rows = DB::table("users")->where("name", "=", "John")->update(["age" => 15]);

$affected_rows = DB::table("users")->where("name", "=", "John")->or_where("name", "=", "Ashley")->update(["age" => 15]);

update() will return no of affected rows

Delete

$no_of_deleted_rows = DB::table("users")->where("name", "=", "John")->delete();

$no_of_deleted_rows = DB::table("users")->where("name", "=", "John")->or_where("name", "=", "Ashley")->delete();

delete() will return no of deleted rows