Trying to insert column data into mysql from json array

Question

I am trying to insert a JSON Array (objects?!) into mysql with php. When the data comes through as Rows, I can handle that! However, I had to export the array in a different manner, and now I'm not able to figure it out. After $array=json_decode($posted,true);, my array looks like:

(
    [0] => Array
        (
            [0] => Allen, test
            [1] => Anderson, Jayson
            [2] => Barrett, Kayla
            [3] => Bennett, Amira
        )

    [1] => Array
        (
            [0] => [email protected]
            [1] => [email protected]
            [2] => [email protected]
            [3] => [email protected]
        )
)

How can I get this into a foreach loop that outputs:

insert into table (name, email) values("Allen, test","[email protected]")
insert into table (name, email) values("Anderson, Jayson","[email protected]")
... etc?

When the data was in a 'row' format, it was easy for me ..

          foreach($array as $row)
          {   
$query="insert into table (name, email) values(\"$row[0]\",\"$row[1]\")";
$doit = mysqli_query($con,$query);
          }

But now that the array is not coming through in rows, I cant quite figure it out.

You will need to use the index here to access the corresponding data in the other array (or both, of you switch out the foreach for a for loop.) — C3roe
– C3roe, Commented Apr 30, 2020 at 10:27

jeprubio · Accepted Answer · 2020-04-30 10:46:37Z

2

One way is to loop the $row[0] and use the key to get both values:

foreach($row[0] as $key => $value) {
    $name = $row[0][$key];
    $email = $row[1][$key];
    $query = "insert into table (name, email) values('$name', '$email')";
    $doit = mysqli_query($con,$query);
}

Working sample

However, I strongly encourage you using prepared statements instead of string variables in your queries to prevent from SQL injection attacks.

edited Apr 30, 2020 at 10:46

answered Apr 30, 2020 at 10:39

jeprubio

18.1k5 gold badges51 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

PatrickNY Over a year ago

Is this better:

$stmt = $con->prepare("INSERT INTO TemporaryUserEmail (name, email) VALUES (?, ?)"); $stmt->bind_param("ss", $name, $email);  foreach($row[0] as $key => $value) {     $name = $row[0][$key];     $email = $row[1][$key];     $stmt->execute();     }  $stmt->close(); $con->close();

jeprubio Over a year ago

Yes, I thought the bind_param() should be in the loop but I've just checked and it seems correct this way. Much better with prepared statements

PatrickNY Over a year ago

It works both inside and outside the loop. Does it matter where I put the bind_param ? Is it more efficient one way or the other?

jeprubio Over a year ago

It seems actually better outside, what you can do it once is always better. Thanks for listening to me and use prepared statements. I'm glad I could help.

Collectives™ on Stack Overflow

Trying to insert column data into mysql from json array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related