PHP PDO: How to insert multiple rows?

Question

I've many sql statements but I want but I wonder if there is any method or function to shorten and do it in the fewest possible lines using PHP PDO.

One idea I have is to create an array with the following names: Perfiles, Usuarios, Customer, Mps, Poliza and Servicios together something like a foreach but I have no concrete idea, could you help me?

SQL STATEMENTS

$sqlpermsPer = "INSERT INTO t_perfiles_permisos (Id_Perfil, Area_Permiso, Buscar, Crear, Eliminar, Modificar)
                                            VALUES ($idPerfil,'Perfiles',0,0,0,0)";
$resultpermsPer = $this->dbConnect->query($sqlpermsPer) or die ($sqlpermsPer);

sqlpermsC = "INSERT INTO t_perfiles_permisos (Id_Perfil, Area_Permiso, Buscar, Crear, Eliminar, Modificar)
                                            VALUES ($idPerfil,'Clientes',0,0,0,0)";
$resultpermsC = $this->dbConnect->query($sqlpermsC) or die ($sqlpermsC);

$sqlpermsU = "INSERT INTO t_perfiles_permisos (Id_Perfil, Area_Permiso, Buscar, Crear, Eliminar, Modificar)
                                            VALUES ($idPerfil,'Usuarios',0,0,0,0)";
$resultpermsU = $this->dbConnect->query($sqlpermsU) or die ($sqlpermsU);

$sqlpermsM = "INSERT INTO t_perfiles_permisos (Id_Perfil, Area_Permiso, Buscar, Crear, Eliminar, Modificar)
                                            VALUES ($idPerfil,'Mps',0,0,0,0)";
$resultpermsM = $this->dbConnect->query($sqlpermsM) or die ($sqlpermsM);

$sqlpermsP = "INSERT INTO t_perfiles_permisos (Id_Perfil, Area_Permiso, Buscar, Crear, Eliminar, Modificar)
                                            VALUES ($idPerfil,'Poliza',0,0,0,0)";
$resultpermsP = $this->dbConnect->query($sqlpermsP) or die ($sqlpermsP);

$sqlpermsS = "INSERT INTO t_perfiles_permisos (Id_Perfil, Area_Permiso, Buscar, Crear, Eliminar, Modificar)
                                            VALUES ($idPerfil,'Servicio',0,0,0,0)";
$resultpermsS = $this->dbConnect->query($sqlpermsS) or die ($sqlpermsS);

Thanks in advance!

You're using PDO in such a way that it is open to sql injection. — Daedalus, Sep 01 '12 at 23:02
Sure, In this statements I haven't change query() instead prepare() and bindParam but using prepare() and bindParam... that way i can solve it? — SoldierCorp, Sep 01 '12 at 23:05
It should be easy to create a function that takes an associative array, and table string as parameters, and then creates the insert. Should be prettier. — Krycke, Sep 01 '12 at 23:07
Voted to close as duplicate. See [this](http://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query) and [this](http://stackoverflow.com/questions/10060721/pdo-mysql-insert-multiple-rows-in-one-query). — Kermit, Sep 01 '12 at 23:10

Daedalus · Accepted Answer · 2012-09-01T23:21:00.957

This creates an array containing your strings, and loops through them, executing the same query with the bound parameters each time.

$stmt = $this->dbConnect->prepare("INSERT INTO t_perfiles_permisos (Id_Perfil, Area_Permiso, Buscar, Crear, Eliminar, Modificar) VALUES (:idPerfil,:str,0,0,0,0)");
$stmt->bindParam(':idPerfil', $idPerfil);
$stmt->bindParam(':str',$val);
$in_arr = array("Perfiles","Clientes","Usuarios","Mps","Poliza","Servicio");

foreach ($in_arr as $key => $val) {
    $stmt->execute();
}

score 0 · Answer 2 · answered Sep 01 '12 at 23:13

0

INSERT INTO
  table 
    (column_one, column_two) 
  VALUES
    ('value_one_one', 'value_one_two'), 
    ('value_two_one', 'value_two_two'), 
    ('value_three_one', 'value_three_two');

answered Sep 01 '12 at 23:13

Andrew Rasmussen

13,619
7
40
77

JDavis · Answer 3 · 2012-09-01T23:35:15.993

As long as you terminate each INSERT properly you can concatenate them. The following code example also makes use of prepare to prevent SQL injection.

$sql  = "INSERT INTO t_perfiles_permisos (Id_Perfil, Area_Permiso, Buscar, Crear, Eliminar, Modificar) VALUES (:idPerfil,'Perfiles',0,0,0,0);";
ssql .= "INSERT INTO t_perfiles_permisos (Id_Perfil, Area_Permiso, Buscar, Crear, Eliminar, Modificar) VALUES (:idPerfil,'Clientes',0,0,0,0);";
$sql .= "INSERT INTO t_perfiles_permisos (Id_Perfil, Area_Permiso, Buscar, Crear, Eliminar, Modificar) VALUES (:idPerfil,'Usuarios',0,0,0,0);";
$sql .= "INSERT INTO t_perfiles_permisos (Id_Perfil, Area_Permiso, Buscar, Crear, Eliminar, Modificar) VALUES (:idPerfil,'Mps',0,0,0,0);";
$sql .= "INSERT INTO t_perfiles_permisos (Id_Perfil, Area_Permiso, Buscar, Crear, Eliminar, Modificar) VALUES (:idPerfil,'Poliza',0,0,0,0);";
$sql .= "INSERT INTO t_perfiles_permisos (Id_Perfil, Area_Permiso, Buscar, Crear, Eliminar, Modificar) VALUES (:idPerfil,'Servicio',0,0,0,0);";

try
{
  if ( $stmt = $db->prepare( $sql ) )
  {
    if ( ! $stmt->execute( array( ':idPerfil' => $idPerfil ) )
      error_log( $stmt->errorInfo() );
  }
}
catch ( PDOException $e )
{
  error_log( $e->getMessage() );
}

PDO does not support multiple statements in a single `execute()` call. This will fail with a MySQL syntax error. — Michael Berkowski, Sep 01 '12 at 23:15
I do it all the time. The only difference here is that this is using `(column) VALUES` syntax which I never do. Not sure that would make a difference. — JDavis, Sep 01 '12 at 23:21
A quick search on the subject; http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd — JDavis, Sep 01 '12 at 23:39

score -1 · Answer 4 · edited May 23 '17 at 11:50

-1

Please check this answer.

You might wish to execute a multiple-insert statement like this:

$qry = 'INSERT INTO table (FirstName, LastName) VALUES ';
$qry .= "(?,?), "
      . "(?,?), "
      . "(?,?)  "
      ;

edited May 23 '17 at 11:50

Community

1
1

answered Sep 01 '12 at 23:10

SteAp

10,824
8
48
83

PHP PDO: How to insert multiple rows?

4 Answers4