Insert Random Number of Multiple Rows of Data

[username]

Hi all,

I am inserting multiple rows of data into a MySQL database at once instead of using multiple insert statements or a loop as per the below, however I cannot figure out how to insert a random number of rows at once. I need to construct the code so that it basically generates an Insert statement like the below, but may insert any number of rows, not just 2 set rows as per below. My data will be coming from an array such as:

array[0][0] = John Citizen
[0][1] = 100 Rodeo Drive
[1][0] = Bill Smith
[1][1] = 42 Hollywood St
[2][0] = ...
[2][1] = ...

INSERT INTO customers(
cust_name,
cust_address,
VALUES(
'John Citizen',
'100 Rodeo Drive',
),
(
'Bill Smith',
'42 Hollwood St',
);

Your help would be appreciated.

[mooger35]

shuffle($array); //or whatever your array name is

$max = 2; //set this for max or use rand() to create a number

for($i=0; $i<$max; $i++){
$insert = mysql_query("INSERT INTO customers(cust_name, cust_address)
VALUES('".$array[$i][0]."','".$array[$i][1]."')");
}

[username]

Hi mooger35, thanks for the reply. Your solution as far as I can see still executes multiple INSERT statements into the database. Is it possible to create just one INSERT statement with multiple INSERT values as outlined in my original post.

Basically I want to run only one INSERT statement which inserts multiple rows, but not using a loop construct.

[coopster]

Initialize a "values" list and loop over your array. If you don't have anything in the values variable after your loop is complete, you don't have any data to insert. Something along these lines (pseudocode):

$values = ''; // initialize 
foreach ($array as $value) { 
 $values .= mysql_real_escape_string($value); 
} 
if ($values) { 
 $sql = "INSERT INTO myTable VALUES($values) ... "; 
 // execute the query, etc. 
}

Note: This is just giving you an idea how to approach it, you need to fill in the quite a few details here!