Receive array and write multiple rows in MySQL

Question

Receive array and write multiple rows in MySQL

Navigation

#1 by (8 votes)
#2 by (6 votes)
#3 by (2 votes)

10

I have the following form in HTML:

<h2>Sessao 1</h2>
<input type="text" name="ipl[]" class="span12 ">
<input type="text" name="spot[]" class="span12 ">
<input type="text" name="energia[]" class="span12 ">
<input type="text" name="dcd[]" class="span12 ">
<input type="text" name="dis_inicial[]" class="span12 ">
<input type="text" name="dis_final[]" class="span12 ">

<h2>Sessao 2</h2>
<input type="text" name="ipl[]" class="span12 ">
<input type="text" name="spot[]" class="span12 ">
<input type="text" name="energia[]" class="span12 ">
<input type="text" name="dcd[]" class="span12 ">
<input type="text" name="dis_inicial[]" class="span12 ">
<input type="text" name="dis_final[]" class="span12 ">

<h2>Sessao [....]</h2>
<input type="text" name="ipl[]" class="span12 ">
<input type="text" name="spot[]" class="span12 ">
<input type="text" name="energia[]" class="span12 ">
<input type="text" name="dcd[]" class="span12 ">
<input type="text" name="dis_inicial[]" class="span12 ">
<input type="text" name="dis_final[]" class="span12 ">

<h2>Sessao 200</h2>
<input type="text" name="ipl[]" class="span12 ">
<input type="text" name="spot[]" class="span12 ">
<input type="text" name="energia[]" class="span12 ">
<input type="text" name="dcd[]" class="span12 ">
<input type="text" name="dis_inicial[]" class="span12 ">
<input type="text" name="dis_final[]" class="span12 ">

When submitting, I would like to include each session in a new row in the MySQL database. That is, I need a foreach, loop, while in PHP to separate the sessions and perform the Insert of each one.

php mysql

asked by anonymous 23.10.2014 / 07:17

3 answers

Difference between .NET Framework for Entity Framework How to print only the first 5 characters?

score 8 · Answer 1

This is the scope for multiple MySQL inserts:

INSERT INTO NOME_DA_TABELA (COLUNA1,COLUNA2,COLUNA3) VALUES 
(VALOR1,VALOR2,VALOR3),
(VALOR4,VALOR5,VALOR6),
(VALOR7,VALOR8,VALOR9);

In PHP, iterate the received array: * I assume you are using POST method:

$sql = "INSERT INTO NOME_DA_TABELA (ipl,spot,energia) VALUES";

$data = $_POST;
foreach( $data['ipl'] as $k => $v ){
    $sql .= PHP_EOL . "('" . $v . "','" . $data['spot'][$k] . "','" . $data['energia'][$k] . "')";

    if( $k > 0 ){
        $sql .= ",";
    }
}
$sql .= ";";


// Query:
echo $sql; exit;

obs: I just put an example with 3 columns. The rest you can do yourself after understanding the logic. There are other ways to resolve and obviously need to do the treatments on receiving the data before mounting the SQL query. As these other details are not the main focus of the question, I chose not to comment on it to avoid a long and complicated answer.

See the MySQL (5.5) manual: link

score 6 · Answer 2

You can make a INSERT for each line, or concatenate all values in a string and execute only that query.

Example:

// utilizei o filter_input para receber os dados do formulário, com o parâmetro FILTER_REQUIRE_ARRAY, declarando que o dado é um array
$ipl = filter_input(INPUT_POST,'ipl',FILTER_DEFAULT, FILTER_REQUIRE_ARRAY);
$spot = filter_input(INPUT_POST,'spot',FILTER_DEFAULT, FILTER_REQUIRE_ARRAY);
$energia = filter_input(INPUT_POST,'energia',FILTER_DEFAULT, FILTER_REQUIRE_ARRAY);
$dcd = filter_input(INPUT_POST,'dcd',FILTER_DEFAULT, FILTER_REQUIRE_ARRAY);
$dis_inicial = filter_input(INPUT_POST,'dis_inicial',FILTER_DEFAULT, FILTER_REQUIRE_ARRAY);
$dis_final = filter_input(INPUT_POST,'dis_final',FILTER_DEFAULT, FILTER_REQUIRE_ARRAY);

// comando para múltiplas inserções
$sql = "INSERT INTO SESSAO (ipl,spot,energia,dcd,dis_inicial,dis_final) VALUES ";

$dados = array(); 
// concatena os dados linha por linha
for($i= 0;$i<count($ipl);i++) {
    $dados[] = "("
               . "'$ipl[$i]', "
               . "'$spot[$i]', "
               . "'$energia[$i]', "
               . "'$dcd[$i]', "
               . "'$dis_inicial[$i]', "
               . "'$dis_final[$i]'"
               . ")";
}

// concatena a consulta com os valores
$sql .= implode(',', $dados);

// execulta a consulta
$mysqli->query($sql);

score 2 · Answer 3

Concatenating all values in a string should not be done because it can exceed the maximum string size, the best way would be to create a loop and generate a INSERT at a time or use the class PDF of PHP . The PDO class would be the best option because you can send the parameters in an array and still have security against SQL Injection .

Example:

srtSQL = "INSERT INTO tblnivestate (codnivestate, nomnivestate) VALUES (:codigo, :nome)"
$parametros = array(":codigo" => 10, ":nome" => Teste);

try {//Define comando de tratamento de erro e exceções           
    $sqlalt = $this->conexao->prepare($strSQL); //Prepara a consulta SQL (Query)
    foreach($parametros as $key => $value){//Laço verifica matriz de parametros             
        $sqlalt->bindValue($key, $value);//Atribui parametros para execução
    }
    $sqlalt->execute()
} catch(PDOException $err) {//Gera exceção caso seja gerado erro na instrução SQL
    return $err;//Retorna erro gerado               
}