Database management library bundled with Zest Framework
Database management
Configuration
In order to use a database in Zest framework you need config it properly
http://zest.readthedocs.io/en/latest/configuration/#database
Simple example
Here is simple example:
<?php
namespace App\Models;
use Zest\Database\Db as DB;
use Config\Database;
class Post extends DB
{
/*
* Store database name
*/
protected static $db_name = Database::DB_NAME;
/*
* Store database table name
*/
protected static $db_tbl = 'posts';
public function name()
{
$db = new DB();
$db->db()->method(); //example code
}
}
Insert
For insert data into the database, you need to used insert();
method with parameters, The insert
method return id on success boolean false on fail take a look at an example:
// rest code ...
/*
* Store database name
*/
protected static $db_name = Database::DB_NAME;
/*
* Store database table name
*/
protected static $db_tbl = 'posts';
public function create($field1,$field2,$field3)
{
$db = new DB();
//Prototype: $db->insert(table,dbName,params);
$id = $db->db()->insert(static::$db_tbl,static::$db_name,['field1'=>$field1,'field2'=>$field2,'field3'=>$field3]);
//close the database connection, its recommanded
$db->db()->close();
return $id;
}
// rest code ...
Select
For selecting record you need to use select()
method, take a look at following example:
// rest code ...
/*
* Store database name
*/
protected static $db_name = Database::DB_NAME;
/*
* Store database table name
*/
protected static $db_tbl = 'posts';
public function select()
{
$db = new DB();
//Prototype: $db->db()->insert(['db_name'=>'value','table'=>'value']);
$result = $result = $db->db()->select(['db_name'=>static::$db_name,'table'=>static::$db_tbl]);
//close the database connection, its recommanded
$db->db()->close();
return $result;
}
// rest code ...
Select by where clause
For select records at the specific condition or using where clause the method is same you just need to passed wheres parameter take a look at an example:
// rest code ...
/*
* Store database name
*/
protected static $db_name = Database::DB_NAME;
/*
* Store database table name
*/
protected static $db_tbl = 'posts';
public function select($id)
{
$db = new DB();
//Prototype: $db->db()->insert(['db_name'=>'value','table'=>'value']);
$result = $result = $db->db()->select(['db_name'=>static::$db_name,'table'=>static::$db_tbl,'wheres'=>['id = '.$id]]);
//close the database connection, its recommanded
$db->db()->close();
return $result;
}
// rest code ...
For multiple conditions
For using multiple wheres clauses take a look at an example:
// rest code ...
$result = $result = $db->db()->select(['db_name'=>static::$db_name,'table'=>static::$db_tbl,'wheres'=>['id = '.$id,'userId ='.$userId]]);
// rest code ...
OrderBy
For sorting orders, you need to passed orderby
parameter take a look at an example:
// rest code ...
$result = $result = $db->db()->select(['db_name'=>static::$db_name,'table'=>static::$db_tbl,'order_by'=> 'id DESC']);
//You can also passed id ASC , or you can also passed other then id column
// rest code ...
Limits
For selecting limited records you need to passed limit
parameter take a look at an example:
// rest code ...
$result = $result = $db->db()->select(['db_name'=>static::$db_name,'table'=>static::$db_tbl,'limit' => ['start' => 3 , 'end' => 0]]);
//start is the limit like 10
//end is where to start 0 means start from beganning
// rest code ...
Debug
For debuging or seeing the query you need to passe debug
parameter take a look at example:
// rest code ...
$result = $result = $db->db()->select(['db_name'=>static::$db_name,'table'=>static::$db_tbl,'debug'=>'on']);
//then it will var dump your query
//like: 'SELECT * FROM users WHERE id = 4';
// rest code ...
Delete
For deleting arecord in mysql we need to used delete()
method with id parameter, Note: If it is not provided all record form specific table will be erased/deleted and this action never be undone.
Here is the following example:
// rest code ...
/*
* Store database name
*/
protected static $db_name = Database::DB_NAME;
/*
* Store database table name
*/
protected static $db_tbl = 'posts';
public function delete($id)
{
$db = new DB();
//Prototype: $db->db()->insert(['db_name'=>'value','table'=>'value','wheres'=>[]]);
$result = $result = $db->db()->delete(['db_name'=>static::$db_name,'table'=>static::$db_tbl,'wheres'=>['id = '.$id]]);
$db->db()->close();
return $result;
}
// rest code ...
Update
For updating records in a database we need to used update
method take a look at an example:
// rest code ...
/*
* Store database name
*/
protected static $db_name = Database::DB_NAME;
/*
* Store database table name
*/
protected static $db_tbl = 'posts';
public function update($id,$uId)
{
$db = new DB();
//Prototype: $db->db()->insert(['db_name'=>'value','table'=>'value','columns'=>[],'wheres'=>[]]);
$result = $result = $db->db()->update(['db_name'=>static::$db_name,'table'=>static::$db_tbl,'columns'=>['useId'=>$uId,'update'=>time()],'wheres'=>['id = '.$id]]);
$db->db()->close();
return $result;
}
// rest code ...
Count
For count record in database we need to used count()
method take a look at example
// rest code ...
$result = $result = $db->db()->count(['db_name'=>static::$db_name,'table'=>static::$db_tbl]);
// rest code ...
Create the database
For creating the database we need to used createDb()
method take a look at an example
// rest code ...
$result = $result = $db->db()->createDb('phone');
// rest code ...
Delete the database
For deleting the database we need to usedeleteDb()
method take a look at an example
// rest code ...
$result = $result = $db->db()->deleteDb('phone');
// rest code ...
Create the table
For creating the table we need to use createTbl
take a look at an example:
// rest code ...
$result = $result = $db->db()->deleteTbl('db|_name','sql');
// rest code ...
Delete the table
For deleting the database we need to use the deleteTbl()
method. Take a look at an example:
// rest code ...
$result = $result = $db->db()->deleteTbl('db|_name','users');
// rest code ...