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
Grégoire Lecocq - mai 31 2018
Je suis sur Facebook pour ma propre pub. Mais Diaspora m'intéresse d'autant plus…
solution mobile entreprise - janvier 16 2018
Merci pour le partage d'informations. Il est très important pour une entreprise…
voip tech - décembre 1 2016
je veux votre contact technique pour réaliser un test a fin de créer un compte.…
abderrahmen - novembre 6 2015
je fais mes premiers pas sur Selenium.
abderrahmen - novembre 6 2015
bonjour , je fais mes premiers pas sur selenium.
Didier - octobre 4 2015
A signaler: les mini-ascenseurs foutent la m**de dans la programmation…