How can I store data from an array into the mySQL database?

Question

I want to store data from an array into my database.

This is how my Array looks when I do print_r($animals);:

Array
(
    [0] => Array
        (
            [animal] => Cat
            [name] => Tom
        )

    [1] => Array
        (
            [animal] => Dog
            [name] => Bob
        )

    [2] => Array
        (
            [animal] => Bird
            [name] => Sam
        )
    [3] => ....

This is how I try to store the data, but for some reason it doesn't work, nothing is stored:

$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO animals (animal,name) values(?,?) ";
$q = $pdo->prepare($sql);
foreach($animals as $row) {
   $q->execute(array(
      $row['animal'],
      $row['name'],));
}
Database::disconnect();

If I write the following for example the storing works, but only the first entry is stored (Cat and Tom)

foreach($animals as $row) {             
    $name = $row['name'];
    $animal = $row['animal'];
}

$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO animals (animal,name) values(?,?) ";
$q = $pdo->prepare($sql);
$q->execute(array($animal,$name));
Database::disconnect();

Your table may be transactional and you may need to commit, i.e $pdo->commit(); — Ayo Makanjuola
– Ayo Makanjuola, Commented Jul 31, 2015 at 12:22
Also you are not checking the status of any of these database access commands. How will you know whats wrong if you dont look???? — RiggsFolly
– RiggsFolly, Commented Jul 31, 2015 at 12:26
Could it be that you just have a comma too much: $row['name'],)); — t.h3ads
– t.h3ads, Commented Jul 31, 2015 at 12:34
Sorry, $properties was wrong, it should be $animals, I updated the mistake — peace_love
– peace_love, Commented Jul 31, 2015 at 12:42

RiggsFolly · Accepted Answer · 2015-07-31 14:30:00Z

1

Your first attempt made more sense. Remember you only need to prepare a parameterised statement once, then you can run it 1000 times if you like as long as you replace the parametes each time.

Also as you are setting PDO to emit Exceptions on error you should code this in a Try/Catch block

    try {
        $pdo = Database::connect();
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $sql = "INSERT INTO animals (animal,name) values(?,?) ";

        $q = $pdo->prepare($sql);

        foreach($animals as $row) {

            $q->execute(array($row['animal'], $row['name']));
        }
    }
    catch (PDOException $pe) {
        echo $pe->getMessage();
    }
    catch (Exception $e ) {
        echo $e->getMessage();
    }
    Database::disconnect();

edited Jul 31, 2015 at 14:30

answered Jul 31, 2015 at 12:35

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

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

6 Comments

peace_love Over a year ago

unfortunately I get a white page, if I use the code

peace_love Over a year ago

Sorry, I made a mistake, the page is not white, but there will be nothing stored in the database

peace_love Over a year ago

Ok, I found the mistake. Now it works, but with your code there is also only one entry in the database (only Cat and Tom)

RiggsFolly Over a year ago

Let us continue this discussion in chat.

peace_love Over a year ago

Hello, sorry, I was not on my desktop, are you still in the chat?

|

Pang · Accepted Answer · 2015-08-01 02:14:40Z

1

Modify your code like this, it will work.

$pdo = Database::connect();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
foreach($animals as $row) {
   $sql = "INSERT INTO animals (animal,name) values($row['animal'], $row['name']) ";
   $q = $pdo->prepare($sql);
   $q->execute();
}
Database::disconnect();

I can't explain it well, but I know the CodeIgniter Framework used PDO this way for SQL execution.

edited Aug 1, 2015 at 2:14

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Jul 31, 2015 at 14:44

Kevin Yan

1,23411 silver badges20 bronze badges

Comments

meda · Accepted Answer · 2015-07-31 14:28:54Z

0

The first one you got syntax error extra comma:

foreach($animals as $row) {
   $q->execute(array(
      $row['animal'],
      $row['name'],));//extra comma
}

The second one you got the loop at the top overwriting the variables. This means it will insert the last row.

So remove the comma in that for each or use for loop:

for($i=0; $i< count($animals); $i++) {
   $q->execute(array(
      $animals[$i]['animal'],
      $animals[$i]['name']));
}

answered Jul 31, 2015 at 14:28

meda

45.5k14 gold badges96 silver badges123 bronze badges

Collectives™ on Stack Overflow

How can I store data from an array into the mySQL database?

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related