Multiple insert with php PDO and Mysql

Question

Multiple insert with php PDO and Mysql

Asked 6 years, 5 months ago

Viewed 1,017 times

2

My situation,

A post form with a text input and two radios inputs.

Inputs are named respectively, nome[0] and tipo[0].

When one adds more form fields I put one .attr to stay nome[1] and tipo[1]" and successively as it adds more inputs.

I did this to have how to transfer and prepare their information via POST in my php 'upload' file.
It also helped me keep the radio buttons in the same group on different lines.

At the end, I would like to put these records in the Mysql table in a table, in the spaces name and guy.

So was the PHP

<?php

include 'conexao.php';

$nome = $_POST['nome'];  // Aqui pega input text, o valor do name="nome[0]".

$tipo = $_POST['tipo'];  // Aqui pega do input radio, o valor do name="tipo[0]".


  if (is_array($nome)){
    if (is_array($tipo)){
       
       foreach($nome as $valornome) {
         foreach($tipo as $valortipo) {
        
$carimbo = $con->prepare("INSERT INTO ingressos (nome,tipo) VALUES (?,?)");

$carimbo->bindValue(1,$valornome,PDO::PARAM_STR);
$carimbo->bindValue(2,$valortipo,PDO::PARAM_STR);

$carimbo->execute();
 

}}}}

It went wrong, but...!

I am managing to store only the correct radios, the name is cloned by the number of inputs generated :(

Form

Here the Mysql

I’ve been seeing some in materials about before sending the data I have to organize the information I took from the vectors and then send, but I didn’t understand very well the texts I had access to...

In short, what is the path to situations like this? Imagining for example up to a third input option... I would love directions of themes and readings.

My knowledge is very artificial, I understand a little of what is happening but not the depth of the logic of the codes.

Thanks in advance!

The Insert went wrong this is the problem? the most practical is to leave the name of the fields like this name="nome[]" at the time of recovery are converted to an array.

– rray

2018/01/29 at 10:09

2 answers

3

The problem is that you are running the INSERT twice as often, because the first foreach will go through all the names (2 inputs = 2 insert) and then go through all the "types" (2 inputs = +2 insert).

When you work with input this way, you have two alternatives.

Form 1: Use only one foreach in the input names, for example, and through ID capture the value of the input guy. Ex:

<?php

$nome = $_POST['nome'];  // Aqui pega input text, o valor do name="nome[0]".
$tipo = $_POST['tipo'];  // Aqui pega do input radio, o valor do name="tipo[0]".


if (is_array($nome) && is_array($tipo)){
    foreach($nome as $key => $valornome) {
        $carimbo = $con->prepare("INSERT INTO ingressos (nome,tipo) VALUES (?,?)");

        $carimbo->bindValue(1, $valornome, PDO::PARAM_STR);
        $carimbo->bindValue(2, $tipo[$key], PDO::PARAM_STR);

        $carimbo->execute();
    }
}

When we use the foreach, we can use 3 parameters: The array, A variable to capture the array index and a variable to capture the array value.

When I inform $key => $valornome i mean: "Capture the index of the array and store in the $key variable and capture and store the value in the $valuename variable".

Like $_POST['nome'] and $_POST['tipo'] has the same amount of data and follows the same indexes, I can simply take the index of one of them and access the value of another array. Ex:

$key will always receive this data at each step (0, 1, 2, 3, 4...) because the inputs come as follows nome[0], nome[1] etc. So I can use the value of $key to capture the other data. That way $tipo[0], $tipo[1], $tipo[2], $tipo[3], $tipo[4]...

Form 2: You can use inputs as a "multidimensional array". Ex:

<!DOCTYPE hml>
<html>
    <head>
        <title>Title of the document</title>
    </head>

    <body>
        <form action="/index2.php" method="POST" enctype="multipart/form-data">
            <fieldset>
                <label>
                    Nome
                    <input type="text" name="data[0][nome]" value="Um" />
                </label>

                <label>
                    Inteira
                    <input type="radio" name="data[0][tipo]" value="1" checked />
                </label>

                <label>
                    Meia
                    <input type="radio" name="data[0][tipo]" value="0" />
                </label>
            </fieldset>

            <fieldset>
                <label>
                    Nome
                    <input type="text" name="data[1][nome]" value="Dois" />
                </label>

                <label>
                    Inteira
                    <input type="radio" name="data[1][tipo]" value="1" />
                </label>

                <label>
                    Meia
                    <input type="radio" name="data[1][tipo]" value="0" checked />
                </label>
            </fieldset>

            <button type="submit">Enviar</button>
        </form>
    </body>
</html>

And in the PHP just use a foreach and capture the data in a array. Ex:

<?php

if (isset($_POST['data']) && is_array($_POST['data'])){
    foreach($_POST['data'] as $key => $value) {
        $carimbo = $con->prepare("INSERT INTO ingressos (nome,tipo) VALUES (?,?)");

        $carimbo->bindValue(1, $value['nome'], PDO::PARAM_STR);
        $carimbo->bindValue(2, $value['tipo'], PDO::PARAM_STR);

        $carimbo->execute();
    }
}

As for the possibilities above, about the same need, is there any question of performance? The first way you showed I didn’t understand how the second bindValue receives the [$key] information related to the $tipo. In itself if already check the two ? both $nome and $tipo. Thank you very much! gave it right here, and very practical the way to use! (:

– Bender

2018/01/29 at 13:24
1

@Yemoja edited my answer. I added a brief explanation. About performance, it’s basically the same thing, only change something if there are many inputs. No if I already check if both variables are arrays, if one of these conditions fails, the check returns false and the code below is ignored.

– Valdeir Psr

2018/01/29 at 13:50

Browser other questions tagged php mysql pdo foreach sql-insert

You are not signed in. Login or sign up in order to post.

by rray • **66,288** points · Answer 1 · 2018-01-29T10:32:39+00:00

With these chained foreachs what happens is for element in $nome the second foreach runs and only then comes back the first one which basically combines all the values. For example if each array has 4 elements 16 Inserts will be generated when only 4.

Example combination - ideone

To correct this situation the first step is to combine the two arrays with array_map() where each element of $tipos and $nomes will be paired in a new array.

With the array mounted correctly you can already pass it directly on execute().

$nomes = !empty($_POST['nome']) && is_array($_POST['nome']) ?  $_POST['nome'] : array();
$tipos = !empty($_POST['tipo']) && is_array($_POST['tipo']) ? $_POST['tipo'] : array(); 

$novo = array_map(function($nome, $tipo){ return array($nome, $tipo);}, $nomes, $tipos);

foreach($novo as $item){       
    $carimbo = $con->prepare("INSERT INTO ingressos (nome,tipo) VALUES (?,?)");
    if(!$carimbo->execute($item)){
        print_r($carimbo->errorInfo());
    }
}

Or alternatively:

$ingressos['nomesOK'] = !empty($_POST['nome']) && is_array($_POST['nome']);
$ingressos['tiposOK'] = !empty($_POST['tipo']) && is_array($_POST['tipo']);


if($ingressos['nomesOK'] && $ingressos['tiposOK']){
    $novo = array_map(function($nome, $tipo){ return array($nome, $tipo); }, $_POST['nome'], $_POST['tipo']);
    foreach($novo as $item){       
        $carimbo = $con->prepare("INSERT INTO ingressos (nome,tipo) VALUES (?,?)");
        if(!$carimbo->execute($item)){
            print_r($carimbo->errorInfo());
        }
    } 
}

$nomes = array('fulano', 'joão', 'maria', 'george');
$tipos = array ('meia', 'inteira', 'meia', 'meia');

Given the following entries, array_map() converts them to the format:

Example array_map - ideone

Array
(
    [0] => Array
        (
            [0] => fulano
            [1] => meia
        )

    [1] => Array
        (
            [0] => joão
            [1] => inteira
        )

    [2] => Array
        (
            [0] => maria
            [1] => meia
        )

    [3] => Array
        (
            [0] => george
            [1] => meia
        )

)