dynamic insert php mysql and performance

Question

I have a folder/array of images, it may be 1, maximum of 12. What I need to do is dynamically add them so the images are added to an images table.

At the moment I have

 $directory = "portfolio_images/$id/Thumbs/";
 $images = glob("" . $directory . "*.jpg");

  for ( $i= 0; $i <= count($images); $i += 1) {

 mysql_query("INSERT INTO project_images  (image_name, project_id)VALUES ('$images[0]', '$id')") or die(mysql_error());

 }

this is fine but it does not feel right, how is this for performance? Is there a better way?

The maximum number of images is only ever going to be 12.

Answer 1

With this solution, you'll send up to 12 insert queries to the database -- which means up to 12 calls between PHP and MySQL.

A possibly faster way would be to send only one SQL query, that would insert several lines at once.

That SQL query would look like this :

INSERT INTO project_images  (image_name, project_id)
VALUES ('image name 1', 'id_1'), 
('image name 2', 'id_2'), 
('image name 3', 'id_3'), 
('image name 4', 'id_4')

See 12.2.5. INSERT Syntax in MySQL's manual -- there's an example of such a query.

This means you could change your code, to build that query, first ; and only then call MySQL once.

Not tested, but I suppose your could would look a bit like this :

$values = array();
$directory = "portfolio_images/$id/Thumbs/";
$images = glob("" . $directory . "*.jpg");
for ( $i= 0; $i <= count($images); $i += 1) {
    $values[] = "('$images[0]', '$id')";
}

$values_str = implode(', ', $value);
mysql_query("INSERT INTO project_images  (image_name, project_id) VALUES $values_str") or die(mysql_error());

Answer 2

1. You have an error in your code. Not $images[0] but $images[$i]

2. Your code fail to follow proper SQL syntax. $images[$i]=mysql_real_escape_string($images[$i]) must be added

3. Yes, It can be done another way as Pascal mentioned

4. No, there is no performance problem.
5. Using or die() is terrible practice, use or trigger_error() to handle error message and and a template for user notification