SQL - Inserting data where values are an array

[system]

I want to be able to add an array of strings to a table so that each string is a new row (in PHP).

This is it in psuedo-code:

$Array = "10000,10001,10002,10003"; $Data = "ImportantData"; mysqli_query($db, "INSERT INTO MyTable(`id`,`data`) VALUES($Array, $Data)");

So that a previously empty table would look like:

id | data

10000 | ImportantData
10001 | ImportantData
10002 | ImportantData
10003 | ImportantData

In an update script, with those rows already established, I could just say:

mysqli_query($db, "UPDATE MyTable SET data = $Data WHERE `id` IN($Array));

However I want it to create rows, not just update old ones.

Is there any way I can do this?

[lothop]

Yup, loop through the array, update or insert based on your requirements

[richei]

That’s not an array, least not yet.

$id_array = array(10000, 10001, 10002, 10003);
$data = “stuff”;

for($i=0; $i < count($id_array); $i++) {
mysqli_query(“INSERT INTO MyTable (id,data) VALUES ($id_array[$i], $data)”);
}

You’ll need to make sure that whatever $data represents has been properly escaped and quoted, then imploded.

[JimL]

Richei: no, he doesnt have to care about escaping, we’re not in 2000 any more.

Use prepared statements with placeholders.