PHP PDO: insertion SQL multiple en une seul requete
Par François Lasselin le jeudi, avril 14 2011, 08:05 - Génie Logiciel - Lien permanent
Un select peut retourner plusieurs résultats généralement sous la forme d'un tableau que php manipule facilement. Un update peut mettre à jour plusieurs enregistrements en un seul appel. Mais pour l'insert si on ne connait pas le nombre de lignes à insérer ... c'est plus compliqué.
Devant cette problématique récurrente, voici une solution élégante pour insérer un nombre variable de champs en une seule requête avec PDO.
En PHP, on peut faire ses requêtes de plusieurs façon.
On peut rester sur le classique:
On peut aussi utiliser PDO et des requêtes paramétrées:
PDO est une extension de PHP inclus dans PHP. C'est une interface d’abstraction à l'accès aux données (mais pas d'abstraction de base de données. c'est déjà une part du chemin pour interopérabilité). Les requêtes paramétrées permettent de se protéger de l'injection sql. C'est un avantage considérable.
Une insertion donne alors ceci:
Le problème d'une insertion multiple c'est qu'on ne connait pas (toujours) le nombre de lignes à insérer en base et donc, le nombre de paramètres.
J'avais lu cette solution : pdo-prepared-inserts-multiple-rows-in-single-query
Mais c'est un peu lourd. Il y a beaucoup de code et plusieurs boucles. De plus, variabiliser le nombre de champs à insérer par ligne ne me semblait pas judicieux. On peut certainement faire mieux en exploitant les fonctions natives disponibles dans php.
Il faut donc construire la requête et lui passer les paramètres. Mais sous quelle forme ?
Quand on fait un fetchall() du résultat d'un select avec PDO, on récupère un tableau de tableaux. Ce format est relativement structuré et déjà utilisé par PDO (on peut imaginer que le résultat d'une requête précédente va alimenter une autre table).
Exemple:
Le but est donc de générer la bonne chaine (?,?),(?,?),(?,?)... en fonction de la structure passée en paramètre.
Comment faire ? En fait ce qu'il faut d'abord, c'est compter le nombre d'éléments à insérer dans chaque ligne et créer la chaine correspondante : (?,?)
On compte le nombre d'éléments pour la première ligne :
count($data[0])
Ensuite, avec str_repeat() on génère une chaine qui répète le motif "?," autant de fois que d'élément. Pour notre exemple, on obtient : ?,?,
On tronque ce motif pour retirer la dernière virgule :
$params= substr(str_repeat("?,", count($data[0])), 0, -1);
Ensuite, on va répéter ce motif autant de fois que de ligne à insérer:
str_repeat("('',".$params."),", count($data));
On obtient: (?,?),(?,?),(?,?),
Et on re-tronque pour obtenir la chaine qui va bien (sans la dernière virgule).
2 eme étape, il faut passer les paramètres à insérer 'à plat'. On fait une boucle sur le contenu avec array_merge pour concaténer les sous-tableaux. Là par contre, je n'ai pas trouvé comment faire autrement qu'une boucle.
Au final ça donne :
On peut rester sur le classique:
$db = mysql_connect('host', 'login', 'password');
mysql_select_db('nom_de_la_base',$db);
$req = mysql_query("INSERT INTO matable (champ1,champ2) VALUES ('1','un'),('2','deux'),('3','trois')");
$res = mysql_num_rows($req);
mysql_select_db('nom_de_la_base',$db);
$req = mysql_query("INSERT INTO matable (champ1,champ2) VALUES ('1','un'),('2','deux'),('3','trois')");
$res = mysql_num_rows($req);
On peut aussi utiliser PDO et des requêtes paramétrées:
PDO est une extension de PHP inclus dans PHP. C'est une interface d’abstraction à l'accès aux données (mais pas d'abstraction de base de données. c'est déjà une part du chemin pour interopérabilité). Les requêtes paramétrées permettent de se protéger de l'injection sql. C'est un avantage considérable.
Une insertion donne alors ceci:
$sth = $dbh->prepare('INSERT INTO matable (champ1,champ2) VALUES (?,?),(?,?),(?,?)');
$sth->execute(array(array('1','un'),array('2','deux'),array('3','trois'));
$sth->execute(array(array('1','un'),array('2','deux'),array('3','trois'));
Le problème d'une insertion multiple c'est qu'on ne connait pas (toujours) le nombre de lignes à insérer en base et donc, le nombre de paramètres.
J'avais lu cette solution : pdo-prepared-inserts-multiple-rows-in-single-query
Mais c'est un peu lourd. Il y a beaucoup de code et plusieurs boucles. De plus, variabiliser le nombre de champs à insérer par ligne ne me semblait pas judicieux. On peut certainement faire mieux en exploitant les fonctions natives disponibles dans php.
Il faut donc construire la requête et lui passer les paramètres. Mais sous quelle forme ?
Quand on fait un fetchall() du résultat d'un select avec PDO, on récupère un tableau de tableaux. Ce format est relativement structuré et déjà utilisé par PDO (on peut imaginer que le résultat d'une requête précédente va alimenter une autre table).
Exemple:
Array
(
[0] => Array
(
[champ1] => 1
[champ2] => un
)
[1] => Array
(
[champ1] => 2
[champ2] => deux
)
[2] => Array
(
[champ1] => 3
[champ2] => trois
)
)
(
[0] => Array
(
[champ1] => 1
[champ2] => un
)
[1] => Array
(
[champ1] => 2
[champ2] => deux
)
[2] => Array
(
[champ1] => 3
[champ2] => trois
)
)
Le but est donc de générer la bonne chaine (?,?),(?,?),(?,?)... en fonction de la structure passée en paramètre.
Comment faire ? En fait ce qu'il faut d'abord, c'est compter le nombre d'éléments à insérer dans chaque ligne et créer la chaine correspondante : (?,?)
On compte le nombre d'éléments pour la première ligne :
count($data[0])
Ensuite, avec str_repeat() on génère une chaine qui répète le motif "?," autant de fois que d'élément. Pour notre exemple, on obtient : ?,?,
On tronque ce motif pour retirer la dernière virgule :
$params= substr(str_repeat("?,", count($data[0])), 0, -1);
Ensuite, on va répéter ce motif autant de fois que de ligne à insérer:
str_repeat("('',".$params."),", count($data));
On obtient: (?,?),(?,?),(?,?),
Et on re-tronque pour obtenir la chaine qui va bien (sans la dernière virgule).
2 eme étape, il faut passer les paramètres à insérer 'à plat'. On fait une boucle sur le contenu avec array_merge pour concaténer les sous-tableaux. Là par contre, je n'ai pas trouvé comment faire autrement qu'une boucle.
Au final ça donne :
public function set_data($data)
{
if (count($data)<1)
{
return 0;
}
$params=substr(str_repeat("?,", count($data[0])), 0, -1);
$params=substr(str_repeat("('',".$params."),", count($data)), 0, -1);
$a=array();
foreach ($params AS $param)
{
$a=array_merge($a, array_values($param));
}
$querySQL="INSERT INTO matable (champ1, champ2) VALUES ".$params;
try
{
$state = $this->connect->prepare($querySQL);
$state->execute($a);
return $this->connect->lastInsertId();
}
catch (Exception $e)
{
// Traitement de l'exception
}
}
{
if (count($data)<1)
{
return 0;
}
$params=substr(str_repeat("?,", count($data[0])), 0, -1);
$params=substr(str_repeat("('',".$params."),", count($data)), 0, -1);
$a=array();
foreach ($params AS $param)
{
$a=array_merge($a, array_values($param));
}
$querySQL="INSERT INTO matable (champ1, champ2) VALUES ".$params;
try
{
$state = $this->connect->prepare($querySQL);
$state->execute($a);
return $this->connect->lastInsertId();
}
catch (Exception $e)
{
// Traitement de l'exception
}
}
La discussion continue ailleurs
URL de rétrolien : http://blog.nalis.fr/index.php?trackback/105

Derniers commentaires
hanane - avril 22 2013
bonjour j'ai un nokia n9 qui vient des UAE et j'ai oublié mon code de sécurité…
bloggento - avril 18 2013
Je dois être mal réveillé, car je ne vois pas de version professionnelle sur ton…
François Lasselin - avril 18 2013
Bonjour bloggento , Si si, la version pro existe toujours…
bloggento - avril 18 2013
Pour info la version professionnelle de magento n'existe plus depuis un moment…
SICARD Robert - avril 16 2013
Est-il possible de faire ces modifications sur un EeePC 701 4G linux qui marche…
Amelaye - mars 17 2013
Non seulement la vie privée est menacée mais les gens ne se rendent pas compte…