How to insert multiple arrays into a Mysql table

Asked

Viewed 2,478 times

6

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

It’s kind of 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
    )

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 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 difficulty is in mounting the correct query for the second insertion.

I already used foreach, for, tried to insert one by one and then give an UPDATE (gambiarra). I searched enough on google also and none of the searches served for that question.

Can someone help me?

php mysql

  • Have you tried $_POST['ol[0]'] ?

  • Already! You can not mount a single query using this index since there are 3 arrays for the same table. :/

2 answers

5


$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 the PDO (you need to put the database data), and table inside the $pdo->prepare need to switch to your table name !!!

Reference

  • 2

    Great Man!! I was already using PDO, I just didn’t know how to manipulate it to use this way. It was perfect! Thank you very much!!

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 as many as you need, and parameterize the query as suggested in the Fulvius response.

Browser other questions tagged php mysql

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