Aller au contenu | Aller au menu | Aller à la recherche


Utilisation de PHP PDO sur MySQL et Oracle

Par PlaceOweb le lundi, février 9 2009, 22:40 - PHP - Lien permanent

PDO (PHP Data Objects), interface pour accéder à n'importe quelle base de données depuis PHP.

Benchmark PDO

Après de mini benchmarks sur PDO, j'ai constaté que :

  • bindParam est plus rapide que l'utilisation d'un array() dans l'exécution du statement.
  • PDO::query reste plus rapide et plus simple qu'un prepared statement dans le cas d'une requête statique.

Difficultés rencontrés à l'utilisation de PDO

num_rows et num_fields

num_rows : le nombre de lignes (affectés) d'un résultat

  • MySQL
    • mysql_num_rows — Retourne le nombre de lignes d'un résultat MySQL
    • mysql_affected_rows — Retourne le nombre de lignes affectées lors de la dernière opération MySQL
  • Oracle
    • oci_num_rows — Retourne le nombre de lignes affectées durant la dernière commande Oracle

Pour compter le nombre d'enregistrements contenus dans votre SELECT, il ne semble exister d'équivalence PDO à la fonction mysql_num_rows qui n'as d'ailleurs pas d'équivalent sous Oracle. A vous de vous organiser avec des count(fetchAll()) ou bien des requêtes COUNT(*) au préalable...

Pour connaître le nombre d'enregistrements affectés avec votre UPDATE, vous pouvez utiliser PDOStatement->rowCount — Retourne le nombre de lignes affectées par le dernier appel à la fonction PDOStatement::execute()

num_fields : le nombre de champs (colonnes) d'un résultat

  • MySQL
  • Oracle
    • oci_num_fields — Retourne le nombre de colonnes dans un résultat Oracle

PDOStatement->columnCount — Retourne le nombre de colonnes dans le jeu de résultats

Par exemple pour mettre des en-têtes avant d'afficher vos données :

$query = "SELECT * FROM MyTable";
$stmt = $conPDO->query($query);
$nbr_champ = $stmt->columnCount();
 
for( $i = 0; $i < $nbr_champ ; $i++ ) {
	$meta = $stmt->getColumnMeta($i);
//array(6) {
//   ["native_type"]=>
//   string(7) "integer"
//   ["flags"]=>
//      array(0) {
//      }
//   ["name"]=>
//   string(8) "firstField"
//   ["len"]=>
//   int(-1)
//   ["precision"]=>
//   int(0)
//   ["pdo_type"]=>
//   int(2)
//}
	echo $meta['name'];
	if( $i < $nbr_champ - 1 ) {
		echo ";";	
	}else{
		echo "\n";
	}
}
 
while($row = $stmt->fetch(PDO::FETCH_NUM)) {
	for( $i = 0; $i < $nbr_champ ; $i++ ) {
		echo $row[$i];
		if( $i < $nbr_champ - 1 ) {
			echo ";";
		}else{
			echo "\n";
		}
	}
}

ou

$query = "SELECT * FROM MyTable";
$result = $DbToQuery->query($query);
// Get the number of columns
$Cols = $result->columnCount();
// Loop through the results
$countrows = 1;
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
   if($countrows == 1) {
      // Print column names
      print join(",", array_keys($row));
   }
   $countrows++;
   // handle the row data
   // ...
}

IN()

DATES

NULL

Problème pour un SELECT : IS NULL, pas = NULL

On peut le typer avec les constantes PDO pré-définies

  • PDO::PARAM_NULL

Oracle FAQ : NULL est un marqueur qui represente des données manquantes, inconnue ou inapplicables.

Les tris ASC et DESC sur les NULL avec Oracle : Dans l'ordre ASCendant, les valeurs NULL apparaissent toujours à la fin.

SELECT * FROM emp ORDER BY sal DESC NULLS FIRST;
 
SELECT * FROM emp ORDER BY sal DESC NULLS LAST;

Avec MySQL, il n'y pas d'option de tri des NULL. Les NULL sont placés en premier pour un tri ASC.

Et les exemples valides et invalides :

Exemples invalides

Un NULL n'est pas égal à NULL:
SELECT * FROM emp WHERE NULL = NULL;
Un NULL ne peut pas être égal à un NULL:
SELECT * FROM emp WHERE NULL <> NULL;
Un NULL n'est pas égal à une chaine vide:
SELECT * FROM emp WHERE NULL = '';

Examples valides

Selection des valeurs nulles NULL d'une colonne:
SELECT * FROM emp WHERE comm IS NULL;
Selection des valeurs non nulles NOT NULL d'un colonne:
SELECT * FROM emp WHERE comm IS NOT NULL;
Changer la valeur d'une colonne en NULL:
UPDATE emp SET comm = NULL WHERE deptno = 20;

Les fetch modes de PDO

Vous aurez surement besoin d'avoir les un fetch mode numérique (PDO::FETCH_NUM) ou litteral (PDO::FETCH_NAMED), mais pas les 2 cumulés (PDO_FETCH_BOTH par défaut)

$stmt = $conPDO->query($query);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
 
// Le code ci dessous est identique mais nécessite une ligne de plus 
$stmt = $conPDO->query($query);
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$row = $stmt->fetch();

Consulter les constantes pré-définies de PDO pour connaitre les modes de fetch (PDO::FETCH_*) possibles.

Lecture d'une seule colonne

Pour la lecture d'un seul champs (une seule colonne) vous pouvez utiliser PDO::FETCH_COLUMN, par exemple avec un COUNT(*)

$query = "SELECT COUNT(*) AS nbr FROM maTable WHERE id = :id";
$stmt = $pdo->prepare($query);
$stmt->bindParam(':id', $id);
$stmt->execute();
 
$nbr = $stmt->fetch(PDO::FETCH_COLUMN);

Lecture indexée par la première colonne

Au lieu de de faire cela avec une requête du type SELECT id, value FROM maTable, mais attention votre tableau sera indexé par la colonne de votre select !

while( $row = $stmt->fetch(PDO::FETCH_ASSOC) ) {
	$array[$row['id']] = $row['value'];
}

Vous pouvez faire ceci

$array = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
//ou
$array = $stmt->fetchAll(PDO::FETCH_COLUMN | PDO::FETCH_UNIQUE);

Lecture indexée par la première colonne pour un group sans l'auto index (en considérant que la clé de groupage, retourne un seul et unique row)

$array = $stmt->fetchAll(PDO::FETCH_ASSOC|PDO::FETCH_GROUP|PDO::FETCH_UNIQUE);
// 
$array = $stmt->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC);
/*
col1, col2, col3
devient un tableau index par col1 :
array(2) {
  [10]=>
  array(2) {
    ["col2"]=>
    string(10) "BLEBEZERTY"
    ["col3"]=>
    string(4) "7.00"
  }
  [22]=>
  array(2) {
    ["col2"]=>
    string(10) "ZADADVFIJL"
    ["col3"]=>
    string(4) "7.00"
  }
}
 
*/

Notez que la même chose sans le PDO::FETCH_UNIQUE, indexera toujours par la col1, mais du fait du multiple attendu, rajoutera un tableau indexé numériquement pour chaque entrée de col1

/*
array(2) {
  [10]=>
        array(2) {
              [0] =>
                    array(2) {
                      ["col2"]=>string(10) "BLEBEZERTY"
                        ...
*/

Comment remplacer les différents fetch :

  • MySQL
    • mysql_fetch_array — Retourne une ligne de résultat MySQL sous la forme d'un tableau associatif, d'un tableau indexé, ou les deux
    • mysql_fetch_assoc — Lit une ligne de résultat MySQL dans un tableau associatif
    • mysql_fetch_field — Retourne les données enregistrées dans une colonne MySQL sous forme d'objet
    • mysql_fetch_lengths — Retourne la taille de chaque colonne d'une ligne de résultat MySQL
    • mysql_fetch_object — Retourne une ligne de résultat MySQL sous la forme d'un objet
    • mysql_fetch_row — Retourne une ligne de résultat MySQL sous la forme d'un tableau
  • Oracle
    • oci_fetch_all — Lit toutes les lignes d'un résultat Oracle
    • oci_fetch_array — Lit une ligne d'un résultat Oracle sous forme de tableau
    • oci_fetch_assoc — Lit une ligne d'un résultat Oracle sous forme de tableau associatif
    • oci_fetch_object — Lit une ligne d'un résultat Oracle sous forme d'objet
    • oci_fetch_row — Lit une ligne d'un résultat Oracle sous forme de tableau numérique
    • oci_fetch — Lit la prochaine ligne dans le résultat Oracle

De bonnes explications sur les différentes méthodes de fetch :

Les scroll avec PDO, l'orientation du défilement (scroll) des enregistrements (rows)

  • PDO::CURSOR_FWDONLY (par défaut) Crée un objet PDOStatement avec un curseur uniquement de retour. C'est le choix par défaut pour le curseur, car il est rapide et l'accès aux données est commun pour les masques en PHP.
  • PDO::CURSOR_SCROLL Crée un objet PDOStatement avec un curseur scrollable. Passez la constante PDO::FETCH_ORI_* pour contrôler les lignes récupérées du jeu de résultats. (permettant de faire des fetch spécifiques) :
    • PDO::FETCH_ORI_NEXT Récupère la prochaine ligne d'un jeu de résultats. Valide seulement pour les curseurs scrollables.
    • PDO::FETCH_ORI_PRIOR Récupère la ligne précédente d'un jeu de résultats. Valide seulement pour les curseurs scrollables.
    • PDO::FETCH_ORI_FIRST Récupère la première ligne d'un jeu de résultats. Valide seulement pour les curseurs scrollables.
    • PDO::FETCH_ORI_LAST Récupère la dernière ligne d'un jeu de résultats. Valide seulement pour les curseurs scrollables.
    • PDO::FETCH_ORI_ABS Récupère la ligne demandée par un numéro de ligne d'un jeu de résultats. Valide seulement pour les curseurs scrollables.
    • PDO::FETCH_ORI_REL Récupère la ligne demandée par une position relative à la position courante du curseur d'un jeu de résultats. Valide seulement pour les curseurs scrollables.

Mon expérience personnelle avec PHP 5.3.3 et MySQL 5.1.51 m'a démontré que l'utilisation du SCROLL des CURSORS ne fonctionnait pas avec PDO::prepare ! La faute à qui ? MySQL ou PDO ? Visiblement à PDO : Note that PDO::CURSOR_SCROLL is **not** supported or emulated by the MySQL PDO driver, nor is a warning thrown.

Notez les exception lors de l'appel à PDO::getAttribute avec les attributs : ATTR_CURSOR & ATTR_CURSOR_NAME

$dbh = PdoSql::getInstance();
$dbh->getAttribute($constValue);
//ATTR_CURSOR_NAME : Exception : SQLSTATE[IM001]: Driver does not support this function: driver does not support that attribute
//ATTR_CURSOR : Exception : SQLSTATE[IM001]: Driver does not support this function: driver does not support that attribute

Pour connaitre les autres attributs de la classe PDO, vous pouvez passez par la classe ReflectionClass, voyez un exemple d'utilisation de la classe ReflectionClass pour connaître les attributs possibles de la classe PDO, ainsi que leur valeur.

function readDataForwards($dbh) {
  $sql = 'SELECT * FROM test_pdo ORDER BY id ASC';
  try {
    $stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR, PDO::CURSOR_SCROLL));
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT)) {
      $data = $row[0] . "\t" . $row[1] . "\n";
      print $data;
    }
    $stmt = null;
  }
  catch (PDOException $e) {
    print $e->getMessage();
  }
}
 
function readDataBackwards($dbh) {
  $sql = 'SELECT * FROM test_pdo ORDER BY id ASC';
  try {
    $stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR, PDO::CURSOR_SCROLL));
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_LAST);
    do {
      $data = $row[0] . "\t" . $row[1] . "\n";
      print $data;
    } while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_PRIOR));
    $stmt = null;
  }
  catch (PDOException $e) {
    print $e->getMessage();
  }
}
 
$conn = PdoSql::getInstance();
 
print "Lecture en avant :\n";
readDataForwards($conn);
 
print "Lecture en arrière :\n";
readDataBackwards($conn);

Résultat :

Lecture en avant :
1	nom1
2	nom2
Lecture en arrière :
1	nom1
2	nom2

La lecture en arrière ne fonctionne pas, nous aurions du avoir

Lecture en arrière :
2	nom2
1	nom1

Données SQL :

CREATE TABLE IF NOT EXISTS `test_pdo` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Table de test pour PHP PDO';
 
INSERT INTO `test_pdo` (`id`, `nom`) VALUES
(1, 'nom1'),
(2, 'nom2');

Ressources

Se connecter à une source de données (datasource) avec PDO

Les options du drivers PDO::PDO::MYSQL_ATTR_*

PDO::MYSQL_ATTR_INIT_COMMAND pour forcer l'encodage en UTF-8

Sous MySQL si vous souhaitez vous connecter en en UTF-8, vous avez 2 choix (qui sont identiques : exécuter la requête : SET NAMES 'utf8') :

Passer en UTF-8 à tout moment, en exécutant la requête :

$pdo->exec("SET NAMES 'utf8'");

Passer en UTF-8 à l'initialisation de la connexion, en rajoutant des options lors de la création de l'objet PDO :

Comme l'indique force mysql PDO driver to use UTF-8 for the connection, l'attribut PDO::MYSQL_ATTR_INIT_COMMAND permet d'exécuter une commande sql : Commande à exécuter lors de la connexion au serveur MySQL. Sera automatiquement ré-exécuté lors d'une reconnexion.

$pdo_sql_options = array( 
									PDO::ATTR_TIMEOUT => 3 ,
									PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
									PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'	// en remplacement de : self::$instance->exec("SET NAMES 'utf8'"); // en remplacement de : self::$instance->exec("SET NAMES 'utf8'");
                $pdo = new PDO('mysql:host=' . $config['sql']['host'] . ';dbname=' . $config['sql']['base'], $config['sql']['user'], $config['sql']['pass'], $pdo_sql_options);
									);

Par contre on ne peut pas lire ce qui a été défini avec PDO::getAttribute(PDO::MYSQL_ATTR_INIT_COMMAND) car cela retourne une exception :

Uncaught exception 'PDOException' with message 'SQLSTATE[IM001]: Driver does not support this function: driver does not support that attribute'

PDO::MYSQL_ATTR_USE_BUFFERED_QUERY pour un résultat unbuffered qui consomme moins de ram

Afin d'optimiser les requêtes qui retourne un trop grand nombre de résultat qui ferait saturer la mémoire pour stocker ces 1,2,3... Go de données dans le résultat du statement, on peut faire des requêtes non bufférisées "unbuffered". Cela a pour impact de ne pas stocker tout le jeu de résultat.

Par contre cette option MYSQL_ATTR_USE_BUFFERED_QUERY ne semble pris en compte uniquement à la création de la connexion. L'utilisation de PDO::setAttribute sur une connexion existante ne semble pas changer l'option.

Par contre attention, lorsque vous imbriquer des exécutions de requêtes, vous aurez une erreur qui indique de faire l'inverse de ce que l'on veut, c.à.d. stocker tout via un fetchAll ou d'utiliser les requêtes bufférisées "buffered" :

Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.'

Erreurs

Oracle

Des erreurs Oracle avec PHP (et PDO)

Tous avec en complément de l'erreur : (/usr/src/php5-5.3.2/ext/pdo_oci/php-pdo-oci-1.0.1/PDO_OCI-1.0.1/oci_driver.c:579)

Mauvaise URI de connexion à la base et son serveur hôte
SQLSTATE[42S02]: pdo_oci_handle_factory: ORA-12154: TNS:could not resolve the connect identifier specified
Mauvais user/pass
SQLSTATE[HY000]: OCISessionBegin: ORA-01017: nom utilisateur/mot de passe non valide ; connexion refusee (/usr/src/php5-5.3.2/ext/pdo_oci/php-pdo-oci-1.0.1/PDO_OCI-1.0.1/oci_driver.c:630)
SELECT * WHERE 1 AND id = 3
SQLSTATE[HY000]: General error: 920 OCIStmtExecute: ORA-00920: operateur relationnel non valide
SELECT * FROM maTable LIMIT 5
SQLSTATE[HY000]: General error: 933 OCIStmtExecute: ORA-00933: la commande SQL ne se termine pas correctement
SELECT * FROM maTable where rownum <= 5;
SQLSTATE[HY000]: General error: 911 OCIStmtExecute: ORA-00911: caractere non valide
SELECT "id" FROM maTable
SQLSTATE[HY000]: General error: 904 OCIStmtExecute: ORA-00904: "id" : identificateur non valide
SELECT * , id FROM maTable
SQLSTATE[HY000]: General error: 923 OCIStmtExecute: ORA-00923: mot-cle FROM absent a l'emplacement prevu
SELECT * FROM maTable;
SQLSTATE[HY000]: General error: 942 OCIStmtExecute: ORA-00942: Table ou vue inexistante

Car la table a été crée en spécifiant les doubles quotes " sur son nom, il faut y accéder avec les mêmes doubles quotes :

SELECT * FROM "maTable"

Que ce soit pour le nom des tables ou des champs, pour permettre à Oracle d'utiliser à la fois les minuscules et majuscules dans le nom, il faut l'encapsuler (aussi bien à la création CREATE qu'à la lecture avec un SELECT) avec des doubles quotes ".

Sinon par défaut il vous retourne, sa case : UPPER_CASE

Bien que modifiable avec PDO::setAttribute et l'attibut PDO::ATTR_CASE qui force les noms de colonnes à une casse particulière, on ne peut pas mixer du upper et lower case sans double quoter les noms des tables ou des colonnes.

$conPDO->setAttribute (PDO::ATTR_CASE, PDO::CASE_NATURAL); // laisse les noms des colonnes inchangées. Laisse les noms des colonnes comme retournés par le driver de base de données.
$conPDO->setAttribute (PDO::ATTR_CASE, PDO::CASE_LOWER); // force les noms des colonnes à être en minuscules.
$conPDO->setAttribute (PDO::ATTR_CASE, PDO::CASE_UPPER); // force les noms de colonnes à être en majuscules.

Voir :

Ajouter un commentaire

Le code HTML est affiché comme du texte et les adresses web sont automatiquement transformées.

Fil des commentaires de ce billet