Iriven PhpDbal

A powerful PHP multi driver database Abstraction and Access Layer using PDO

Requirements

• php_pdo

/*
* NOTE: To use this class, the PDO extension must be installed and active on your web server.
* The same is true of the PDO modules of each driver you used in your project (eg pdo_mysql for MySQL)
*/

Dependencies

• Iriven\ConfigManager
• Iriven\OptionsResolver

Usage:

Installation And Initialisation

To utilize PhpDbal, first import and require PhpDBAL.php file in your project.

Installation

require_once 'PhpDBAL.php';

Example Configuration File (setting.php)

The configuration file must adhere to the psr-4 standard:

return  array (
      'default' =>
          array (
              'driver' => 'mysql',
              'host' => 'localhost1',
              'dbname' => 'mydatabase1',
              'user' => 'myusername1',
              'password' => 'mypassword',
              'prefix'=>'DB1_',
              'port' => 3306,
              'persistent' => 1,
              'fetchmode' => 'object',
              'prepare' => 1
          ),
      'project2' =>
          array (
              'driver' => 'pgsql',
              'host' => 'localhost2',
              'dbname' => 'mydatabase2',
              'user' => 'myusername2',
              'password' => 'mypassword',
              'port' => 5432
          )
  );

Initialisation

$DBInstance = new Iriven\PhpDBAL(
                                  new Iriven\Libs\DatabaseConfiguration($PoolName='default')
                                  ); //Initialisation
/* 
* NOTE: to load a different database instance, just change the "poolname" value. 
* According to the setting.php file content, you can set the poolname value to "project2",
* in order to connect to the second database.
*/

QueryBuilder Usage

- Basic Usage:

List all active members informations in the 'users' database table:

$Members = $DBInstance->QueryBuilder()
                ->select()
                ->from('users','u')
                ->where('u.isactive = :active')
                ->andWhere('u.isbanned = :banned')
                ->setParameters([':active'=>1,':banned'=>0])
                ->execute();
 if(!$Members)
     echo  'No active member found';
 else 
    print_r($Members);

Insert a new User or Update user data:

      $data = [
                  'id'=>null,
                  'username'=>null,
                  'email'=>null,
                  'password'=>null,
                  'activationKey'=>null,
                  'banned'=>null,
                  'active'=>null
              ];
      extract($data);
      $values = [];
      $params = [];
      if(!empty($username))   { $values['u.usersusername']        = ':pseudo';   $params[':pseudo'] = $username;}
      if(!empty($email))      { $values['u.usersemail']           = ':email';    $params[':email']  = $email;}
      if(!empty($password))   { $values['u.userspassword']        = ':passwd';    $params[':passwd']  = $password;}
      if(!empty($token))      { $values['u.usersactivationkey']   = ':token';    $params[':token'] = $token;}
      if(!empty($banned))     { $values['u.usersisactive']        = ':banned';   $params[':banned'] = $banned;}
      if(!empty($active))     { $values['u.usersisactive']        = ':active';   $params[':active'] = $active;}
      if(!empty($id) and $id>=1)
      {
          $QueryBuilder = $this->QueryBuilder()
              ->update('users','u')
              ->where('u.idusers = :uid')
              ->setParameter(':uid',$id);
          if($values)
              $QueryBuilder->set($values)->setParameters($params);
      }
      else
      {
          $QueryBuilder = $this->QueryBuilder()
              ->insert('users u')
              ->values($values)
              ->setParameters($params);
      }
      $uid = $QueryBuilder->execute(); 

- Advanced Usage:

retrieve user login informations. here a user can login using a couple of (username + password) or (email + password)

$data = [
            'uid'=>null,
            'username'=>null,
            'email'=>null,
            'banned'=>false,
            'active'=>1
        ];
        extract($data);
        $clause = [];
        $params = [];
        $User = $DBInstance->QueryBuilder()
                ->select('u.id AS id, u.username AS username, u.password AS password, u.email AS email, u.isactive AS active, u.isbanned AS banned')
                ->from('users','u');
            if(!empty($username))
            {
                $expr = $User->expr();
                $clause[] = $expr->orX($expr->eq('username',':pseudo'),$expr->eq('email',':pseudo'));   
                $params[':pseudo'] = $username;
            }
            if(!empty($uid))        { $clause[] = 'id = :uid';                      $params[':uid']    = $uid;}
            if(!empty($banned))     { $clause[] = 'banned= :banned';                $params[':banned'] = $banned;}
            if(!empty($active))     { $clause[] = 'active = :active';               $params[':active'] = $active;}
            if($clause)
                $User->where(implode(' AND ',$clause))->setParameters($params);
            if(!$User->execute())
                echo 'User not found';
            else 
                  print_r($User);

Compatibility:

This project handles most of the well-known database vendors including:

• MySQL
• SQLite
• PgSQL
• Oracle
• SQLServer
• SQLsrv
• MsSQL
• DB2
• IBM
• ODCB
• Sysbase

Authors

• Alfred TCHONDJO - Project Initiator - iriven France

License

This project is licensed under the GNU General Public License V3 - see the LICENSE file for details

Donation

If this project help you reduce time to develop, you can give me a cup of coffee :)

Disclaimer

If you use this library in your project please add a backlink to this page by this code.

<a href="https://github.com/iriven/PhpDbal" target="_blank">This Project Uses Alfred's TCHONDJO  PHPDbal Library.</a>

Issues Repport

Repport issues Here