Inserting an array

$ct is my array: array(4) { [“a”]=> int(1) [“b”]=> int(2) [“z”]=> int(2) [“c”]=> int(1) }

I need to put it in a two column database where “item” is col1 and “count” is col2 where the first row has an “a” in col1 and 1 in col2, 2nd row has a “b” in col1 and 2 in col2, etc.

What do I put after VALUES ($result = mysql_query("INSERT INTO test2(item,ct) VALUES ???) or die(mysql_error())

or do I need to use another approach?

You can see the values of those settings with this query:


SHOW VARIABLES;

How to interpret them is a different matter. Searching the manual is a good idea, you could start here: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

Yes, building a single query for inserting many rows is much more efficient for the database to execute.

Iterating an empty array is fine, it must be set though.
Also, no need to execute an SQL query for every item. You can build a query in this format to do a single insert.


INSERT INTO `test2` (item, ct) 
VALUES 
(a, 1), 
(b, 2), 
(z, 2)

I don’t know what the limit might be, but when you do an export from PhpMyAdmin is defaults to breaking inserts into 50,000 rows per query, which is a lot. And probably a lot bigger than the array here. Not that PhpMyAdmin is necessarily perfect though.

Yes cranial-bore, I had noticed this in phpmyadmin already but I was expecting if there is a certain exact limit for multiple insert.

Thanks!

Guys isn’t there a limit for such multiple inserts? I had heard something about
max_allowed_packet and bulk_insert_buffer_size. Can anyone give me a page where it is stated about this? I couldn’t get exactly information about this anywhere.

As nichemtkg approach, don’t forget to add count() checking to avoid foreach() loop error.


include_once "connect_to_mysql.php";

if (count($ct) > 0) {
    foreach ($ct as $value => $count) {
        $result = mysql_query("INSERT INTO test2(item,ct) VALUES ('$value','$count')") or die(mysql_error());

        echo $value.' -  '.$count." was inserted. <br />";
    }
} else {
    echo "ct variable doesn't contain anything, no need to insert i guess.";
}

a foreach loop was the answer:

include_once “connect_to_mysql.php”;
foreach ($ct as $value => $count)
{
$result = mysql_query(“INSERT INTO test2(item,ct) VALUES (‘$value’,‘$count’)”) or die(mysql_error());

// echo $value . $count. “<br />”;
}