Insert multiple rows with a different field and the others equal in php and mysqli

Question

Insert multiple rows with a different field and the others equal in php and mysqli

Navigation

#1 by (3 votes)
#2 by (0 votes)

1

I'm trying to insert several rows in the same query

Details: I have several fields in the table, and only one field would have to be different in each row, the other fields would be identical in each row.

This is the code I have. but I do not seem to be on the right track.

$insertar = mysqli_query ($link,"INSERT INTO temporal8 
            (articulo,fraccion,inciso,nombre,nombreDoc,ano,departamento,rango,nuevafecha,consecutivo,mes,destino,id) 
            VALUES ('$articulo', '$fraccion', '$inciso', '$nombre', '$nombreDoc', '$ano', ('ALUMBRADO','CATASTRO','COMPRAS'), '$rango', '$nuevafecha', '$consecutivo', '$mes', '$destino', NULL)") 
            or die("fallo al insertar los primeros datos: ".mysqli_error($link));

In the Field "departamento" I try to insert the different values. Or I generate 3 rows with repeated values except the field "departamento"

php mysqli

asked by Sharly Infinitywars 22.06.2017 в 01:36

source

2 answers

0

What I recommend you use is 3 VALUEs separated by commas. That is:

$insertar = mysqli_query ($link,"INSERT INTO temporal8 
    (articulo,fraccion,inciso,nombre,nombreDoc,ano,departamento,rango,nuevafecha,consecutivo,mes,destino,id)
    VALUES ('$articulo', '$fraccion', '$inciso', '$nombre', '$nombreDoc', '$ano', 'ALUMBRADO', '$rango', '$nuevafecha', '$consecutivo', '$mes', '$destino', NULL),
        ('$articulo', '$fraccion', '$inciso', '$nombre', '$nombreDoc', '$ano', 'CATASTRO', '$rango', '$nuevafecha', '$consecutivo', '$mes', '$destino', NULL),
        ('$articulo', '$fraccion', '$inciso', '$nombre', '$nombreDoc', '$ano', 'COMPRAS', '$rango', '$nuevafecha', '$consecutivo', '$mes', '$destino', NULL)") 
or die("fallo al insertar los primeros datos: ".mysqli_error($link));

With this you generate 3 insertions to the base at the same time, and in each one you change the value of the column. Greetings.

answered by 22.06.2017 в 16:27

Multiplication and sum of results of a Foreach split Javascript separator

score 3 · Accepted Answer

I will propose an example in PDO , so that you can then adapt it to your requirements.

For the proposed example we will have a table with two fields name, age where the fixed value will be the name and the value that will change will be the age using a for mode example , We start by creating the transaction with beginTransaction , we prepare the sentence. Then we link the parameters to the value we want with bindvalue which additionally you should "specify" the type of data using constants that handles PDO , for later in for We execute the prepared statement.

Eye: run more changes are not made in BD to make the changes must be used commit

try { 
        /* Creamos La Conexión con PDO, modificar los valores respectivos*/
        $bd = new PDO('mysql:host=localhost;dbname=mibd',"root", "",array(PDO::ATTR_PERSISTENT => true));
        $bd->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        /* Creamos la Transacción*/
        $bd->beginTransaction();
        /* Preparamos la Sentencia*/
        $sentencia = $bd->prepare("INSERT INTO app (name,edad) VALUES (:name,:edad)");
        /* Le pasamos el valor fijo antes de entrar al For */
        $sentencia->bindValue(':name', 'MiValorFijo', PDO::PARAM_STR);
        for ($i=1; $i <5 ; $i++) { 
            $sentencia->bindValue(':edad', $i, PDO::PARAM_INT);
            $sentencia->execute();
        }
         /* Aplicamos los Cambios en La BD */
        $bd->commit();
    }
catch (Exception $e) {
     /* Cancelamos La Transacción por si exista Error*/
    $mbd->rollBack();
    echo "Se Presento Un Error :  " . $e->getMessage();
}