PHP foreach not inserting whole array into mysql table

Question

Hi I have created a simple foreach loop which is looping through an array and inserting the data into my db table. The problem I am having is it is only inserting 130 rows into my database even though the array contains 196.

Here is my code:

foreach ($currencyArray as $currency) {

    if (count($currency) > 1) {
        $currency_table = $db->prepare('
            INSERT INTO currency_name (symbol, name, btc_value)
            VALUES (:name, :symbol, :btc_value)
        ');

        $currency_table->bindParam(':name', $currency[0]);
        $currency_table->bindParam(':symbol', $currency[1]);
        $currency_table->bindParam(':btc_value', $currency[2]);

        $currency_table->execute();
    }
}

I have made sure it isnt the if statement causing the problem. I added a count to inside the if statement and this count 197 records which is the amount of rows I am expecting in my db table, but it is only storing 130 rows?

If you also believe there is a faster way of inserting the array into the db table please feel free to share :)

You can check [this answer](https://stackoverflow.com/questions/779986/insert-multiple-rows-via-a-php-array-into-mysql), batch/bulk insertion. — Tariq Albajjali, Oct 19 '17 at 13:54
You do not need to `prepare()` the query on every iteration. You should prepare BEFORE the foreach loop, and then execute the data inside the loop. — GrumpyCrouton, Oct 19 '17 at 13:54
@TareqAlbajjaly Most of those answers are obsolete, OP is trying to use prepared statements. Answers you linked to are either using obsolete `mysql_` functions, or using CodeIgniter which is not really relevant here. — GrumpyCrouton, Oct 19 '17 at 13:55
@GrumpyCrouton if you scroll down a little bit, you'll find an example of batch prepared insertion, FYI, it's not recommended to execute/insert inside a loop. you should prepare the data then insert it as a batch. — Tariq Albajjali, Oct 19 '17 at 13:58
are you getting any errors? you should use `try` `catch` and see if your sql is failing on some of your data. — Dimitris Filippou, Oct 19 '17 at 13:58
@TareqAlbajjaly Do you have a citation for that not recommended bit? And [I do see a PDO answer](https://stackoverflow.com/a/780030/5827005) on there (For a sec I thought OP was using `mysqli_*` for some reason) — GrumpyCrouton, Oct 19 '17 at 13:59
@GrumpyCrouton I added the prepare outside of the loop and I still get the same issue — Luke Rayner, Oct 19 '17 at 14:02
I am thinking timeout (takes too long), bad data at line 131 (INSERT fails, kills script). Timeout would appear in logs, bad data try 200 times the same valid line just to eliminate that possibility. — Nic3500, Oct 19 '17 at 14:07

score -1 · Answer 1 · answered Oct 19 '17 at 14:11

-1

I found out what the error was it, it was my mistake some of the names I am inserting into the database are longer than I have allowed

answered Oct 19 '17 at 14:11

Luke Rayner

391
6
20

PHP foreach not inserting whole array into mysql table

1 Answers1