Question 20218 in Mysql Question posted in
The official MySQL documentation can be found here.

Upload multiple images in a database table in a single row – Mysql

I want to upload multiple images in one single column.

In my PHP form I have an input for name, upload images button and submit.

I have a database table named my_images with columns id, name, and image_data.

In my form, I input a name and I want to upload multiple images and only insert it in a single row in my database my_image table upload image form:
upload image form

<h2>Upload Images</h2>
    <form method="post" enctype="multipart/form-data">
        <label for="name">Name:</label>
        <input type="text" name="name" id="name"><br><br>
        <label for="images[]">Images:</label>
        <input type="file" name="images[]" id="images" multiple><br><br>
        <input type="submit" value="Upload">
    </form>

  <?php

// Check if the form was submitted
if ($_SERVER["REQUEST_METHOD"] == "POST") {

  // Get the name entered by the user
  $name = $_POST["name"];

  // Initialize an array to hold the image data
  $image_data_array = array();

  // Loop through each uploaded image
  foreach ($_FILES["images"]["tmp_name"] as $key => $tmp_name) {

    // Check if the image was uploaded successfully
    if ($_FILES["images"]["error"][$key] == UPLOAD_ERR_OK) {

      // Read the contents of the image file
      $image_data = file_get_contents($tmp_name);

      // Add the image data to the array
      $image_data_array[] = $image_data;

    } else {

      echo "Error uploading image: " . $_FILES["images"]["error"][$key];
    }
  }

  // Combine the image data into a single binary string
  $combined_image_data = implode("", $image_data_array);


  $stmt = mysqli_prepare($conn, "INSERT INTO my_images (name, image_data) VALUES (?, ?)");
  mysqli_stmt_bind_param($stmt, "sb", $name, $combined_image_data);
  mysqli_stmt_execute($stmt);

  echo "Images uploaded successfully!";
}

?>

I tried can combine the image data into a single binary string and store it in a BLOB column, but this only put the images in multiple columns.

And I don’t want to make multiple input fields for my images database file when I submit multiple imges:
database file when i submit multiple imges

the reason why i want to do this is because in my website
i want to display containers with a name and multiple images
container with images

Tags:

2

Answers


  1. 0

    You want to put all the images into one column, which could be done. but it is probably not the best thing to do.

    might I suggest, upload the images into a folder. then you can concatenate the paths of the images into one string and put this in one column.

    you would then have to deconstruct the string to give you the individual paths to the images, which you would then feed into the html so you can display the images as you needed.

    you could also just create a table the for images and a table for the user

    and then create a relationship between them as to identify which images belong to which user.

    these methods are going to be less cpu intensive and less convoluted then the method your are proposing.

    Login or Signup to reply.
  2. 0

    It’s possible, but I don’t recommend this method.

    First of all I point out the data type is better indicated as string, so:

    mysqli_stmt_bind_param($stmt, "sb", $name, $combined_image_data);

    should be:

    mysqli_stmt_bind_param($stmt, 'ss', $name, $combined_image_data);

    So to retrieve the single images you have to save somewhere the number and the sizes of them. To achieve this you could do this way:

    if ($_FILES["images"]["error"][$key] == UPLOAD_ERR_OK)
{
    $image_data = file_get_contents($tmp_name);
    $size = strlen($image_data);

    // temporarily stores each size and each image data
    $image_data_array[] = [$size, $image_data];
}
else
{
    /* */
}

    Then you need to store a header in the blob before the image data:

        // the number of images
    $blob = str_pad(count($image_data_array), 4, '0', STR_PAD_LEFT);

    // the sizes (adjust the number of digits as your needings, here is 12)
    foreach ($image_data_array as $imageData)
        $blob .= str_pad($imageData[0], 12, '0', STR_PAD_LEFT);

   // the images 
    foreach ($image_data_array as $imageData)
        $blob .= $imageData[1];

    Finally you can store the data

    $stmt = mysqli_prepare /* ... */
mysqli_stmt_bind_param($stmt, 'ss', $name, $blob); 
mysqli_stmt_execute($stmt);

    To retrieve the images you must read the header to know the number and the sizes of the images to split the blob:

    // example of simple fetch, you should prepare etc...
$sql = 'SELECT * FROM my_images WHERE ...';
$result = mysqli_query ($conn, $sql);
$data = mysqli_fetch_assoc($result);

// 4 is the number of digits of the number of images
$nImages = (int)substr($data['image_data'], 0, 4);

// 12 is the number of digits of the images sizes
$imagesSizes = str_split(substr($data['image_data'], 4, $nImages * 12), 12);

$imagesOffset = 4 + $nImages * 12;

for ($n = 0; $n < $nImages; $n++)
{
    // extract an image from the blob
    $img = substr($data['image_data'], $imagesOffset, (int)$imagesSizes[$n]);

    // do what you want with your image stored in $img
    
    $imagesOffset += (int)$imagesSizes[$n];
}

    But what I recommend is to create two tables: the first we call the parent table containing an id and description, the second contains a row for each image with any details and also the id of the parent table:

    CREATE TABLE `my_images` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `my_images_details` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `my_images_id` int(10) unsigned NOT NULL,
  `image_type` varchar(255) NOT NULL,
  `image` longblob NOT NULL,
  FOREIGN KEY (`my_images_id`) REFERENCES `my_images` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

    In this way, to retrieve the images you can select all the rows with my_image details.my_image _id equal to my images.id and every row will contain a single image

    Finally, I also suggest that you evaluate the possibility of storing the images on the file system and always using two tables, to store in the child table the image path instead of its content

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search