Database

Presently, PDO is used in this framework to cope up with different databases. In configuration.env file, change the values of the following as per your configuration. Default PDO driver is : 'mysql'.

   DB_PDO = your-pdo-db-driver

   DB_HOST = your-host-name

   DB_PORT = your-host-port

   DB_USERNAME = your-db-username

   DB_PASSWORD = your-db-password

   DB_NAME = your-dbname

1. SELECT command

With all fields

      use App\Database\Query;

      $db = Query::table('table_name')->select()->get(); [This will display all the records]

      OR

      $db = Query::table('table_name')->select()->single(); [This will display single record]

With all specific fields

      use App\Database\Query;

      $db = Query::table('table_name')->select('table_name.field1, table_name.field2, ......')->get(); [This will display all the records]

       OR

      $db = Query::table('table_name')->select('table_name.field1, table_name.field2, ......')->single(); [This will display single record]

Count rows of a table

      use App\Database\Query;

      $db = Query::table('table_name')->select()->count();

With WHERE clause

      use App\Database\Query;

      $db= Query::table('table_name')->select()->where("fieldname=? AND/OR ......", [value1,value2,.....])->get()/single()/count();

JOIN TABLES

      use App\Database\Query;

     $db = Query::table('table_name')->select()->join("join-table-name", "join-type", "join-condition")->get();

GROUP BY

      use App\Database\Query;

      $db = Query::table('table_name')->select("field1, field2, .....")->group("field1, field2, .....")->get();

HAVING

      use App\Database\Query;

      $db = Query::table('table_name')->select("field1, field2, .....")->group("field1, field2, .....")->having("fieldame","operator","value")->get();

ORDER BY

      use App\Database\Query;

      $db = Query::table('table_name')->select()->order("field1, field2 DESC, .....")->get();

LIMIT

      use App\Database\Query;

      $db = Query::table('table_name')->select()->limit($offset, $limit)->get(); // $offset-> starting index, $limit -> limiting index

PAGINATION

      use App\Database\Query;

      $db = Query::table('table_name')->select()->pagination($pageNo, $records)->get(); // $pageNo-> starting index, $records -> limiting index

2. INSERT command

  use App\Database\Query;

 $db = Query::table('table_name')->insert(["fieldname1" => value1, "fieldname2" => value2, .....]); [Dont call get()/single()/count()/save()]

 $db->save(); [This will return true(1) or false(0)]

3. UPDATE command

  use App\Database\Query;

 $db = Query::table('table_name')->update(["fieldname1" => value1, "fieldname2" => value2, ......])->where("fieldname=? AND/OR ......", [value1,value2,.....]); [Dont call get()/single()/count()/save()]

 $db->save(); [This will return true(1) or false(0)]

4. DELETE command

 use App\Database\Query;

 $db = Query::table('table_name')->delete()->where("fieldname=? AND/OR ......", [value1, value2,.....]); [Dont call get()/single()/count()/save()]

 $db->save(); [This will return true(1) or false(0)]

5. CUSTOM QUERY command

 use App\Database\Query;

 $sql="your sql query here"; [to define any variable just type like this -> ?]

 $db = Query::customQuery($sql, [value1, value2, .....]); [Dont call get()/single()/count()/save()]

 $db->save(); [This will return true(1) or false(0), for INSERT/UPDATE/DELETE queries]

 $db->get(); [This will return all the records for SELECT query]

N.B. :

1. To display query :

   use App\Database\Query;

   $db = Query::.......; [Dont call get()/single()/count()/save()]

   $db->show(); [This will display the entire query]

---

2. You must follow the order to do CRUD operation :

i. C : Query::table()->insert()->save()

ii. R : Query::table()->select()->join()->where()->group()->having()->order()->limit()/pagination()->get()/single()/count()

iii. U : Query::table()->update()->where()->save()

iv. D : Query::table()->delete()->where()->save()

---

3. You must follow the rules to define variables in queries :

Ex.: SELECT * FROM users WHERE id=? AND name=? .

These '?' marks are use to define variables. When you enter the parameters in any queries (INSERT/ UPDATE/DELETE/SELECT), be sure to pass according to sequence.

In the above example, we need to pass the values sequentially : id, name. Not name, id