====== FluentPDO ======

===== Ressources =====

  * [[https://www.sitepoint.com/getting-started-fluentpdo/|Getting started]]
  * [[https://github.com/envms/fluentpdo|Github]]

===== Utilisation basique =====

==== Connexion ====

<code php>
// 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());
}</code>
===== SELECT =====

<code php>$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();
}</code>

===== INSERT INTO =====

<code php>$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é
}</code>

===== UPDATE =====

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

===== DELETE =====

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

===== Quelques mémos de cas particuliers =====


==== SELECT autre que * ====

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


==== SELECT count(*) ====

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

==== SELECT ~ FROM ~ WHERE ~ IN (~) ====

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

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

<code php>
$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();</code>

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

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

==== Debug des requêtes ====

<code php>$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";
};</code>