How to insert multiple arrays into a MySQL table

Question

How to insert multiple arrays into a MySQL table

Navigation

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

6

I get via Ajax a $_POST request with 5 indexes and the last 3 are Arrays.

It looks something like this:

   Array
    (
    [c] =>
    [s] => 
    [dt] => 
    [nl] => Array
    (
            [0] => valor1
            [1] => valor2
            [2] => valor3
    )

    [ol] => Array
    (
            [0] => valor1
            [1] => valor2
            [2] => valor3
    )

    [cat] => Array
    (
            [0] => valor1
            [1] => valor2
            [2] => valor3
    )

    [save] => save
    )

The indexes "c", "s" and "dt" are being saved in a table:

    primeira tabela
     id  |  campaign_name  |   subject     |  date    
     AI  |  $_POST['c']    |   $_POST['s'] |  $_POST['dt']

and if they are inserted, the other indexes ("nl", "ol" and "cat") are saved in another table:

     id |  main_url    | new_url       | access_count  |  campaign_FK
     AI | $_POST['ol'] | $_POST['nl']  |    NULL       |  id_primeira_tabela

Referencing the id of the first insertion. The first query runs ok. My problem is to mount the correct query for the second insertion.

I have already used foreach, for, tried to insert one by one and then give an UPDATE (gambiarra). I researched quite a bit on Google as well and none of the searches served that doubt.

Can anyone help me?

php mysql

asked by anonymous 07.04.2014 / 02:51

2 answers

3

If the question is to insert multiple rows into the table with a single query, you can use the following syntax:

INSERT INTO tbl (col1, col2) VALUES (val1, val2), (val3, val4);

The above example is for two lines, but you can adapt it to whatever you need, and parameterize the query as suggested in Fulvio's answer.

07.04.2014 / 03:56

How to put my ASP.NET website online Combination of 4 numbers in MySQL

score 5 · Accepted Answer

$pdo = new PDO();
$sts = $pdo->prepare("insert into tabela(campaign_name,subject,date) values(?,?,?)");
$sts->bindValue(1, $_POST['c'], PDO::PARAM_STR);
$sts->bindValue(2, $_POST['s'], PDO::PARAM_STR);
$sts->bindValue(3, $_POST['dt'], PDO::PARAM_STR);
$sts->execute();
$sts->closeCursor();
$sts = null;
//NEW ID GERADO
$pk = $pdo->lastInsertId();
//  
$sts = $pdo->prepare("insert into tabela(main_url,new_url,access_count,campaign_FK) values(?,?,NULL,?)");
$listaOl = $_POST['ol'];
$listaNl = $_POST['nl'];
for($i = 0; $i < sizeof($listaOl); $i++){
    $sts->execute(array($listaOl[$i], $listaNl[$i], $pk));  
}
$sts->closeCursor();
$sts = null;

In PDO (need to put bank data), and table inside $pdo->prepare need to change to the name of your table !!!

Reference

The PDO class