Database

Permet les requêtes PDO MySQL/MariaDB.

• En début de page PHP (voir aussi : classe Autoload)

require('config.php');
require('classes/_Autoload_.php');

• Dans config.php

config.php

$CONFIG = array (
 
    'databases' => array (
        'database1' => array (
            'host' => 'localhost',
            'name' => 'your_database1_name',
            'user' => 'your_database1_user',
            'pass' => 'your_database1_password',
            'tables' => array (
                'your_database1_table_name_in_php_code' => 'your_database1_table_name_in_database'
            )
        ),
 
        'database2' => array (
            'host' => 'localhost',
            'name' => 'your_database2_name',
            'user' => 'your_database2_user',
            'pass' => 'your_database2_password',
            'tables' => array (
                'your_database2_table_name_in_php_code' => 'your_database2_table_name_in_database'
            )
        )
    )
);

Database::get('default')->selectOne(array(
    'table'     => $CONFIG['databases']['database1']['tables']['table1'],
    'fields'    => 'id,name,email',
    'condition' => "id = 1"
));

La valeur du champ fields peut être remplacée par *

Database::get('database1')->insert(array(
    'table'     => $CONFIG['databases']['database1']['tables']['table1'],
    'values'    => array('field' => 'value')
));

Database::get('database1')->update(array(
    'table'     => $CONFIG['databases']['database1']['tables']['table1'],
    'fields'    => array('field' => 'value'),
    'condition' => "id = 1"
));

Database.php

# Updated 2018-02-24

class Database
{
 
    private static $databases = Array();
 
    public static function get ($name)
    {
        global $CONFIG;
 
        return new Database($CONFIG['databases'][$name]);
 
#        if (!isset(self::$databases[$name]))
#        {
#            self::$databases[$name] = new Database($CONFIG['databases'][$name]);
#        }
#        return self::$databases[$name];
    }
 
    private $dbh;
 
    public function __construct($dbconfig)
    {
        try
        {
            $dns = "mysql:host=".$dbconfig['host'].";dbname=".$dbconfig['name'];
 
            $options = array(
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",
                PDO::ATTR_EMULATE_PREPARES   => false,
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_PERSISTENT         => TRUE
            );
 
            $this->dbh = new PDO($dns, $dbconfig['user'], $dbconfig['pass'], $options);;
        }
 
        catch ( Exception $e )
        {
            echo "Cannot connect to database ".@$dbconfig['database']." : ".$e->getMessage();
            die();
        }
    }
 
    private function dbExpression ($sql,&$params)
    {
        foreach ($params as $key => $value)
        {
            if (stripos($value,'dbexpr:') === 0)
            {
                unset($params[$key]);
                $sql = str_replace($key, substr($value, 7), $sql);
            }
        }
 
        return $sql;
    }
 
    public function exec ($sql)
    {
        $stmt = $this->dbh->prepare($sql); $stmt->execute();
 
        return $this->dbh->lastInsertId();
    }
 
    public function query ($sql)
    {
        $stmt = $this->dbh->prepare($sql); $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
 
    public function selectOne ($params)
    {
        $sql  = "SELECT ".$params['fields']." FROM ".$params['table'];
        if (!empty($params['condition'])) $sql .= " WHERE ".$params['condition'];
 
        $stmt = $this->dbh->prepare($sql); $stmt->execute();
 
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
 
    public function selectColumn ($params)
    {
        $sql  = "SELECT ".$params['fields']." FROM ".$params['table'];
        if (!empty($params['condition'])) $sql .= " WHERE ".$params['condition'];
 
        $stmt = $this->dbh->prepare($sql); $stmt->execute();
 
        return $stmt->fetch(PDO::FETCH_COLUMN);
    }
 
    public function selectAll ($params)
    {
        $sql  = "SELECT ".$params['fields']." FROM ".$params['table'];
        if (!empty($params['condition'])) $sql .= " WHERE ".$params['condition'];
 
        $stmt = $this->dbh->prepare($sql); $stmt->execute();
 
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
 
    public function insert ($args)
    {
        $params = array();
        $values = array();
 
        foreach ($args['values'] as $key => $value)
        {
            $params[":$key"] = $value;
            $values[] = ":$key";
        }
 
        $fields = implode(',',array_keys($args['values']));
        $values = implode(',',$values);
 
        $sql = "INSERT INTO {$args['table']} ($fields) VALUES ($values)";
        if (isset($args['condition'])) $sql .= " WHERE ".$args['condition'];
 
        $sql = $this->dbExpression($sql,$params);
 
        $stmt = $this->dbh->prepare($sql);
 
        return $stmt->execute($params);
    }
 
    public function update ($args)
    {
        $params = array();
        $values = array();
 
        foreach ($args['fields'] as $key => $value)
        {
            $params[":$key"] = $value;
            $values[] = "$key = :$key";
        }
        $values = implode(',',$values);
 
        $sql = "UPDATE ".$args['table']." SET $values";
        if ($args['condition']) $sql .= " WHERE ".$args['condition'];
 
        $sql = $this->dbExpression($sql,$params);
 
        $stmt = $this->dbh->prepare($sql);
 
        return $stmt->execute($params);
    }
 
    public function replace ($args)
    {
        $params = array();
        $values = array();
 
        foreach ($args['values'] as $key => $value)
        {
                $params[":$key"] = $value;
                $values[] = ":$key";
        }
 
        $fields = implode(',',array_keys($args['values']));
        $values = implode(',',$values);
 
        $sql = "REPLACE INTO {$args['table']} ($fields) VALUES ($values)";
 
        $sql = $this->dbExpression($sql,$params);
 
        $stmt = $this->dbh->prepare($sql);
 
        return $stmt->execute($params);
    }
 
    // TODO: Convert in $args
    public function delete ($table,$cond)
    {
        $sql = "DELETE FROM " . $table . " WHERE " . $cond . ";";
 
        $stmt = $this->dbh->prepare($sql);
 
        return $stmt->execute($params);
    }
 
}