Insert data into table via PHP Array()

Asked

Viewed 142 times

0

I have the following PHP:

# Prospect
$sql_prospect = "SELECT * FROM interessados LIMIT 100";
$exe_prospect = mysqli_query($link, $sql_prospect);

$i = 0;
while($res_prospect=mysqli_fetch_array($exe_prospect, MYSQLI_ASSOC)){
    $prospect[$i]['uni_id'] = $res_prospect['int_cod_unidade'];
    $prospect[$i]['cur_id'] = $res_prospect['int_cod_curso'];
    $prospect[$i]['usu_id'] = $res_prospect['int_cod_consultora'];
    $prospect[$i]['cam_id'] = $res_prospect['int_cod_campanha'];
    $prospect[$i]['pro_id_origem'] = $res_prospect['int_cod_origem'];
    $prospect[$i]['pro_nome'] = $res_prospect['int_nome'];
    $prospect[$i]['pro_telefone'] = $res_prospect['int_telefone'];
    $prospect[$i]['pro_whatsapp'] = $res_prospect['int_whatsapp'];
    $prospect[$i]['pro_endereco_cep'] = $res_prospect['int_endereco_cep'];
    $prospect[$i]['pro_endereco'] = $res_prospect['int_endereco'];
    $prospect[$i]['pro_endereco_estado'] = $res_prospect['int_endereco_estado'];
    $prospect[$i]['pro_endereco_cidade'] = $res_prospect['int_endereco_cidade'];
    $prospect[$i]['pro_email'] = $res_prospect['int_email'];
    $prospect[$i]['pro_data'] = $res_prospect['int_data'];
    $prospect[$i]['pro_data_nascimento'] = $res_prospect['int_data_nascimento'];
    $prospect[$i]['pro_observacao'] = $res_prospect['int_observacao'];
    $prospect[$i]['pro_status'] = $res_prospect['int_status'];
    $i++;
}

In such a way that, $prospect, all arrays are assigned, as shown above.

What would be the right way to enter in the database? Without me having to do "insert into tabela (campo1, campo2, campo3) values (campo1, campo2, campo3).."

I’d like to do something like that: INSERT INTO tabela (todos_os_campos) VALUE (todos_os_campos), maybe with some kind of implode?

php
  • 2

    With the function array_keys you can get the list of indexes and with the array_values values. Just convert both to string with the join (or implode) and build SQL.

  • It is a solution @Andersoncarloswoss, but my return would look like this: INSERT INTO Prospect (uni_id,cur_id,usu_id,cam_id,pro_id_origem,pro_nome,pro_telefone,pro_whatsapp,pro_endereco_cep,pro_endereco,pro_endereco_estado,pro_endereco_cidade,pro_email,pro_data,pro_data_nascimento,pro_observacao,pro_status) VALUES (1,24,0,2,63,Leonardo,,(32) 9912-15056,,,0,0,,2016-09-02 20:13:29,0000-00-00,51), which would error the insert.

  • Put everything in quotes then. Whatever number the bank solves.

1 answer

3


Hi, You can use this method, I use it in my script. Add the quotes in the assignment and already manages the implode in the loop. Here’s a link of the working code

$i = 0;
while($i<10){

    $prospect[$i]['uni_id'] = "'abc'";
    $prospect[$i]['cur_id'] = "'abc'";
    $prospect[$i]['usu_id'] = "'ba'";
    $prospect[$i]['cam_id'] = "'abc'";
    $prospect[$i]['pro_id_origem'] = "'abc'";
    $prospect[$i]['pro_nome'] = "'abc'";
    $prospect[$i]['pro_telefone'] = "'abc'";
    $prospect[$i]['pro_whatsapp'] = "'abc'";
    $prospect[$i]['pro_endereco_cep'] = "'abc'";
    $prospect[$i]['pro_endereco'] = "'abc'";
    $prospect[$i]['pro_endereco_estado'] = "'abc'";
    $prospect[$i]['pro_endereco_cidade'] = "'abc'";
    $prospect[$i]['pro_email'] = "'abc'";
    $prospect[$i]['pro_data'] = "'abc'";
    $prospect[$i]['pro_data_nascimento'] = "'abc'";
    $prospect[$i]['pro_observacao'] = "'abc'";
    $prospect[$i]['pro_status'] = "'abc'";
    // verifica se $nomeCampos já não está setado
    if(!isset($nomeCampos)){
        // define nomes colunas
        $nomeCampos = array_keys($prospect[0]);
    }

    $prospect[$i] = '(' . implode(', ', $prospect[$i]) . ')' ;
    $i++;
}
$prospects = implode(', ', $prospect);


$nomeCampos = implode(', ', $nomeCampos);



$sql = "INSERT INTO {$nomeCampos} VALUES ($prospects);";
echo $sql . PHP_EOL;

Browser other questions tagged php

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