0

I am working on a photo uploader that allows me to upload multiple images at one time to create photo galleries. This part works fine.

What I want to do is record the filename, active status and created timestamp of each image into a database table called photos using a MySQLi prepared statement, and I want each photo's filemane (in this case) stored in its own row. I want to do this in one shot, not wrapping the query in a loop. Each gallery will not always have the same amount of photos.

My initial results only stored the first filename. After about 4 hours of research today, I settled on this code, which works exactly as I want it to, yet there is a nagging feeling that this method is inefficient. Is this creating separate insert queries for each photo's information? For reference, the $names array is created by the uploading script and only contains the filename, $active is a hard-coded variable that can be changed through an update page elsewhere in this system.

$sql = 'INSERT INTO photos (photo_filename, photo_active, photo_created)
    VALUES (?, ?, NOW())';
$stmt->prepare($sql);
foreach ($names as $filename) {
$stmt->bind_param('si', $filename, $active);
$stmt->execute();
}
$imageOK = $stmt->affected_rows;

If this code is in fact creating multiple insert queries, any help on how to insert a group of records (could be 25, could be 75 or more) in one single query would be greatly appreciated. This is for a back-end system that only I will be using, so the server load will not be great. Still, if the above code is not the best way to do business, then I want to learn the best way.

The desired result this code is providing:

+----------------+--------------+---------------------+
| photo_filename | photo_active | photo_created       |
+----------------+--------------+---------------------+
| image_1.jpg    | yes          | 2015-06-25 15:12:47 |
+----------------+--------------+---------------------+
| image_2.jpg    | yes          | 2015-06-25 15:12:47 |
+----------------+--------------+---------------------+
and so on...

Many thanks!

wordman
  • 581
  • 2
  • 6
  • 20
  • possible duplicate of [Mysqli multiple row insert, simple multi insert query](http://stackoverflow.com/questions/19512498/mysqli-multiple-row-insert-simple-multi-insert-query) – mim. Jun 25 '15 at 22:29
  • There are plenty of posts here that could be possible duplicate. But most have not been what I was looking for. This post is asking a very specific question for a very basic process. I simply want to know if this is creating multiple queries. Most of the other explanations here are confusing to me. Cheers! – wordman Jun 25 '15 at 22:41
  • check out this one http://stackoverflow.com/a/19780424/2629508 – mim. Jun 25 '15 at 22:44
  • I actually read that one during my research. Not every upload I will do will have the same number of photos, and that one looks like it works with a fixed number of data. – wordman Jun 25 '15 at 22:49
  • If you can pass an array of data which will be inserted, you can use its count for loop instead of the number he gave. – mim. Jun 25 '15 at 22:54
  • I'll revisit that and give it a try. One thing though, is my code executing separate queries for each photo? – wordman Jun 25 '15 at 23:14

0 Answers0