Create Your Own Backend API With PHP Part 4

Database connection

There should be only a single db connection each time there is a request. This is why we load the global environmental variables at the beginning of the script, use the db credentials to connect to the database only a single time. If you visit global.php you will see

$global_connection = \Doctrine\DBAL\DriverManager::getConnection($connectionParams);

This connection variable later used in the database.php under modules,

   public function __construct(){
        global $global_connection;
        
        $this->connection = $global_connection;
    }

Database object later is called on every time the system object is called. On system.php:

$this->db = new database;
$this->connection = $this->db->connect();

Is located on the __construct function and the public $db variable is assigned for easy access on modules. If you write your own custom module and extend the system module, $this->db will give you access to the database layer immediately.

We can also use $queryBuilder since this is basically DBAL (read the documentation here https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/query-builder.html#sql-query-builder)

       $this->queryBuilder
            ->select('*')
            ->from(“table_name”)
            ->where('id = ?')
            ->setParameter(0, $id);

And fetch the data:

$data = $this->queryBuilder->execute()->fetchAll(\PDO::FETCH_OBJ);

The $data will have the results stored in it as $data[0]->field_name

The framework also has built in methods to easily insert, list, update and delete.

Insert

$this->db->insert([array key values],”table_name”);

Update

$this->db->update([array key values],[array identifier key values],”table name”);

List

$this->db->list(“table name”,”number of results”,”start from record number”);

Delete

$this->db->delete([array identifier key values],”table name”);

Select

$this->db->select(“table name”,[array identifier key values],”number of results”,”start from record number”):

The listed functions are for easy usage, for more complicated sql queries, you should use the queryBuilder.