Insert multiple rows into mysql with php using foreach arrays

Question

Im stuck, been trying to figure this out for 2 hours now. I have figured out the foreach loop, but cant figure out how to insert the data now.

here is my php, what am I doing wrong?

    $query = "INSERT INTO images (thumb_path, image_path, main_image, project_id) VALUES ";  
foreach($_POST as $key => $value) {
    $query .= "$thumb_path,$image_path,$main_image,$_POST[project_id])";
    $result = mysql_query($query, $connection);
}

Thanks!

Should I lay it out like this, sorry still a newbie to foreach and how it works.

foreach($_POST as $key => $value) {
    $query = "INSERT INTO images VALUES (thumb_path, image_path, main_image, project_id),";  
    $query .= "$value[thumb_path], $value[$image_path], $value[$main_image], '$_POST[project_id'])";
}

$result = mysql_query($query, $connection);

Answer 1

First, escape $_POST[project_id] with mysql_real_esape_string.

Then, the syntax is INSERT INTO table VALUES ( ... ), ( ... )

Answer 2

// escape your input
$_POST = array_map('addslashes', $_POST);

// rather than recursively calling mysql_query, you can insert all your rows with one query
// INSERT INTO table (columns) VALUES (data), (data), (data), ...
$values = array();
foreach($_POST as $key => $value) {
    $values[] = "('{$_POST['thumb_path']}', '{$_POST['image_path']}', '{$_POST['main_image']}', '{$_POST['project_id']}')";
}
if(sizeof($values)) {
    $query = "INSERT INTO images (thumb_path, image_path, main_image, project_id) VALUES ".implode(',', $values);
    $result = mysql_query($query, $connection);
}

Answer 3

I find something like this is far easier to maintain than a repeated string concatenation as you're doing:

$values = array();
foreach ($_POST as $key => $value) {
    $qvalue = mysql_real_escape_string($value);
    $values[] = "($field1, $field2, $field3, $qvalue)"; // quoted value, not the raw value
}

$query_values = implode(',', $values);

$query = "INSERT INTO images (field1, field2, field3, field4) VALUES $query_values";
$result = mysql_query($query, $connection);

Just keep in mind that when building a query like this, it's entirely possible to build a query large enough to except the max_packet length, in which case you'd have to split the insert into multiple smaller queries.