Receive array and record multiple lines in Mysql

Question

I have the following HTML form:

<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 performing Submit I would like to include each session in a new line 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.

Answer 1

This is the scope for multiple inserts in Mysql:

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

In PHP, itere 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 solve and obviously need to do the processing in receiving the data before mounting the SQL query. As these other details are not the main focus of the question, I preferred not to comment on to avoid a long and complicated answer.

See the Mysql manual (5.5): http://dev.mysql.com/doc/refman/5.5/en/insert.html

Answer 2

Can be made a INSERT to each line, or concatenate all values into a string and execute only this 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);

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 PHP PDO. The class PDO 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               
}