FluentPDO

Ressources

Utilisation basique

Connexion

// Configuration
$db_dsn="pgsql:host=localhost;port=5432;dbname=myapp";
$db_user="myapp";
$db_pwd="mysecret";
$db_options=array();
 
try {
        $pdo = new PDO($db_dsn, $db_user, $db_pwd, $db_options);
        $fpdo = new FluentPDO($pdo);
}
catch(Exception $e) {
        die("Fail to connect to DB (DSN : '$db_dsn') : ".$e->getMessage());
}

SELECT

$result = $fpdo -> from('matable')
      -> where(
          array (
              'col1 = ?' => 'val1',
              'col2 < ?' => 'val2',
          )
      )
      -> orderBy('col1 ASC')
      -> limit(10)
      -> offset(30)
      -> execute();
if ($result !== false) {
     // Retourner le premier enregistrement
     return $result -> fetch();
 
     // Ou retourner tous les enregistrements
     return $result -> fetchAll();
}

INSERT INTO

$result = $fpdo -> insertInto('matable')
      -> values(
          array (
              'col1' => 'val1',
              'col2' => 'val2',
          )
      )
      -> execute();
if ($result !== false) {
     return true; // Ou return $result pour récupérer l'ID de l'objet inséré
}

UPDATE

$result = $fpdo -> update('matable')
      -> set(
          array (
              'col1' => 'val1',
              'col2' => 'val2',
          )
      )
      -> where('username', 'toto')
      -> execute();
if ($result !== false) {
     return true;
}

DELETE

$result = $fpdo -> deleteFrom('matable')
      -> where('username', 'toto')
      -> execute();
if ($result !== false) {
     return true;
}

Quelques mémos de cas particuliers

SELECT autre que *

$fpdo -> from('matable')
      -> select(null)
      -> select(
          array (
              'col1',
              'col2',
          )
      )
      -> where([...])
      -> execute();

SELECT count(*)

$result = $fpdo -> from('matable')
      -> select(null)
      -> select('count(*) as count')
      -> where([...])
      -> execute();
if ($result !== False) {
    $row = $result -> fetch();
    return $row['count'];
}

SELECT ~ FROM ~ WHERE ~ IN (~)

$fpdo -> from('matable')
      -> where('status', array('val1', 'val2'))
      -> execute();

SELECT ~ FROM ~ WHERE ~ AND (~ OR ~ OR ~)

$where = array (
    'col1' => 'val1',
    'col2 > ?' => 12,
);
 
$query = $fpdo -> from('matable')
      -> where($where);
 
$patterns_where = array (
    'id = ?' => 15,
    'mail' => 'toto@exemple.fr',
    'tel LIKE ?' => '%15%',
);
call_user_func_array(array($query, 'where'), array_merge(array('('.implode(' OR ', array_keys($patterns_where)).')'), array_values($patterns_where)));
 
$result = $query -> orderBy($orderby)
                 -> limit($limit)
                 -> offset($offset)
                 -> execute();

Utilisation d'une méthode d’agrégation

$fpdo -> update('users')
      -> set(
          array (
              'password' => new FluentLiteral("crypt('".addslashes($password)."', gen_salt('bf'))"),
          )
      )
      -> where('username', 'toto')
      -> execute();

Debug des requêtes

$fpdo -> debug = function ($q) {
        $time  = sprintf('%0.3f', $q->getTime() * 1000) . ' ms';
        $rows  = ($q->getResult()) ? $q->getResult()->rowCount() : 0;
        $query = $q->getQuery();
        $msg = "# DB query ($time; rows = $rows) : $query";
 
        $parameters = $q->getParameters();
        if ($parameters) {
                if (is_array($parameters)) {
                        $msg .= "\n# Parameters: '" . implode("', '", $parameters) . "'";
                }
                else {
                        $msg .= "\n# Parameters: '" . varDump($parameters) . "'";
                }
        }
 
        echo $msg."\n";
};