Handling Database

Database connection

Database connections are handled by default using the dbconfig file (i.e icore/dbconfig). However, this can be updated using specific predefined classes. It is worth mentioning that only sql database systems are currently supported

Setting up a new connection using Database (DB) Class

There are different ways of opening a new connection and we'll be looking at a few examples

Method 1

  $dbc = (new DB);
  $dbh = $dbc->openDB();

Method 2

  $dbc = (new DB);
  $dbh = $dbc->openDB(DBNAME);

Method 3

  $dbc = (new DB);
  $dbh = $dbc->openDB(DBNAME, DBUSER, DBPASS, DBPORT, DBSERVER, DBSOCKET);

In method (1) above, no arguments were supplied. This makes the database class to assume the default configurations already defined in the dbconfig.php file

In method (2) above, only one database argument is supplied. This makes the database class to assume only the default selected database is needed to be updated. Hence, it switches to a new defined database using default configurations.

In method (3) above, all arguments were supplied. SOCKET is optional. This makes the database class to overide the default configuration settings.

Footnote:

It is recommended to configure the default database connection parameters in the dbconfig file. This may however be updated later.
Top level connection parameters will only affect subsequent connection when strictly defined. This is further discussed under User Account Control
For the purpose of this tutorial, $dbh will be referred to as $db

Running Database Queries (CRUD)

Database queries are handled using database crud and non-crud operators which are listed below:

SQL setters:

These are methods responsible for setting sql up queries

CRUD Operators:

These are query executors. They tell database on how to process predefined sql queries.

Helper Operators:

Other query executors are helper method which helps to reduce the time frame for performing simple tasks. These are listed and explained below:

table_exists()

This method returns true if a table exists in the database

  $db = (new DB)->openDB();
  
  if($db->table_exists('table_name')) {
      
    //run this code ...
      
  }

column_exist()

This method returns true if a column exists in the database table name

  $db = (new DB)->openDB();
  
  if($db->column_exists('table_name', 'column_name')) {
      
    //run this code ...
      
  }

addColumn()

This method adds a column to database table. The syntax is shown below:

  $db = (new DB)->openDB();
  
  if($db->addColumn([table_name => column_name], type, pipe, definition, default)) {
  
  
    where: 

     table_name  : name of table where column will be added 

     column_name : name of column to be added 

     type        : type of column e.g ( decimal(2,5); varchar(200), e.t.c)

     pipe        : FIRST | AFTER FIELDNAME (After can be replaced with a pipe e.g "|Email" means AFTER Email )

     definition  : field definition (e.g NOT NULL, UNIQUE)

     default     : field default value.
  
  
  }

Note: The type (datetime) will set a default of 1970-01-01 00:00:00 as the default datetime which still translates as zero.

drop()

This method drops a database, database field or column. Examples are shown below

  $db = (new DB)->openDB();
  
  if($db->drop(true)) {
  
    //currently connected database dropped successfully!
  
  }
  
  if($db->drop('table_name', true)) {
  
    //selected table_name of current database dropped successfully!
  
  }
  
  if($db->drop('table_name', 'column_name')) {
  
    //relative column dropped successfully
  
  }

Handling Errors:

When using any of the helper methods, the DBHandler error_exists() and error() method must be supplied an argument of true in order to function as expected. However DBStatus::err() will still return the last error encountered. Errors are discussed below.

Spoova is a silent framework. Most errors are not displayed unless requested.

checking errors

Database Status:

Database status tracker class DBStatus helps to keep track of last executed sql queries and error responses.

database status

Database Relationships:

The database information can be communicated with under three database relationships which are the one-to-one, one to many and many to many database relationships. These relationships are handled through the database model class which determines how information are processed and returned. More information is provided about database relationships here. It may also be important to learn about working with Session, Forms and User classes before proceeding with database models because these classes have some form of close relationship with each other.