How to insert multiple image into one mysql database row?

Question

Okay briefly I would like to say whats my problem with the code I have. The code below uploads image data into mysql database beautifully except with one barrier. On the form I have, people can upload up to 4 images. Now, the code below inserts four different rows. Each image 1 row. but the title for all four rows is the same because it belongs to same post. Is there anyway I can do a comma delimited insertion so that all four images for the single post be in one row?

<?php
   if(isset($_POST['submitting'])) 
      {
      if(isset($_FILES['file_array']))
         {
            $user = $_SESSION['user_id'];
            $pname = $_POST['Product_Name'];
            $ProPrice = $_POST['Product_fee'];
            $n_array = $_FILES['files_array']['name'];
        $tmp_name_array = $_FILES['files_array']['tmp_name'];
        $type_array = $_FILES['files_array']['type'];
        $size_array = $_FILES['files_array']['size'];
        $error_array = $_FILES['files_array']['error'];
        for($i = 0; $i < count($tmp_name_array); $i++)
        {
            if(move_uploaded_file($tmp_name_array[$i], "data/profile/posted_data/".$n_array[$i]))
            {
                $query = mysqli_query($conn, "INSERT INTO posts (userid, post_title, file, type, size, image_date) VALUES ('$user', '$pname', '$n_array[$i]', '$type_array[$i]', '$size_array[$i]', now())");
                echo '<div class="form_message_box">' . $n_array[$i] . '   ' . 'Uploaded Successfully' . '</div>' . '<br>';
                if($query) 
                                        {
                       echo '' . '<br>';
                    }
                        else
                    {
                       echo 'failed!' . '<br>';
                                        }
            }
        }
    }
 }
?>


            $posts = "SELECT * FROM posts WHERE userid='$user'";
        $posts_result = mysqli_query($conn, $posts);
        $res = mysqli_num_rows($posts_result);
        while ($posts_result_rows = mysqli_fetch_assoc($posts_result)) {
                                  $post_image = $posts_result_rows['files'];
                            }

                            <tr>
                <td width="220">
                <?php 
                                <img src="<?php echo "data/profile/posted_data/".$post_image; ?>" width="220" height="220"><hr>

                </td>        
            </tr>

    <?php
            }
    echo "</table>";                
    ?>

Why? It is better to insert them in separate rows for each image. — Logan Wayne
– Logan Wayne, Commented Aug 25, 2015 at 5:29
@Logan Wayne well, the reason is that for example when another user wants to see all of the posted images by a user, the post would be divided into 4 different posts even though the titles and and everything else is the same but pictures are different. I want it so that all four images belong to single post. — koroush
– koroush, Commented Aug 25, 2015 at 5:30
That is why you need to have a better schema. If that is my project, I would create a separate table for storing the post_title and would just index it to that table. And they way you store files right now is correct. — Logan Wayne
– Logan Wayne, Commented Aug 25, 2015 at 5:33
@koroush the problem is hat you had your insert inside you for loop. the answer is posted below. by Syed. — mdamia
– mdamia, Commented Aug 25, 2015 at 5:35
define relations. in your case one post can have many(4) images, so implement it that way. — Jigar
– Jigar, Commented Aug 25, 2015 at 5:36

Logan Wayne · Accepted Answer · 2015-08-25 06:23:06Z

5

I think you need a different schema:

Table 1:
- userid
- id 
- post_title

Table 2:
- id
- post_id (FK for table 1)
- file
- type
- size
- image_date
- sort (if you want to sort the images)

With the relationship 1:many you can add as many images for one title as you want and at the same time not have any duplicate data.

Pros of this answer:

Better database performance and better schema
Easier SQL queries (like INNER JOIN, DISTINCT BY, etc.)

Cons of what you want to achieve right now (if you continue the logic you have right now, where you want to store the file name into a single string for a single post):

Slower performance
When you fetch a data for a single post (e.g. Dadashi.jpg, 3.jpg, 2.jpg), you have to use functions like explode() and then run them in a loop in order to display them in a <img>.
When you want to add an image for a post, you have to fetch the file and then add the string of the new file name, then UPDATE it back to your table

An example for it:

Fetch all the post by (I would use prepared statement):

if($stmt = $con->prepare("SELECT userid, id, post_title FROM table1")){ /* FETCH ALL POST QUERY */
  $stmt->execute(); /* EXECUTE QUERY */
  $stmt->store_result(); /* NECESSARY WHEN NESTING A SECOND STATEMENT */
  $stmt->bind_result($userid,$id,$post_title); /* BIND RESULT TO THESE QUERIES */
  while($stmt->fetch()){ /* START FETCHING ALL POST RESULTS */

    ?>
      <tr> <!-- START ROW -->
        <td><?php echo $post_title; ?></td> <!-- FIRST COLUMN IS THE TITLE -->
        <td> <!-- SECOND COLUMN IS THE FILES ATTACHED FOR THIS POST -->
    <?php

    if($stmt2 = $con->prepare("SELECT post_id, file, type, size, image_date, sort FROM table2 WHERE id = ?")){ /* PREPARE THE QUERY THAT WILL FETCH ALL FILES ATTACHED FOR THE CURRENT POST */
       $stmt2->bind_param("i",$id); /* BIND THIS VARIABLE TO THE QUERY; i STANDS FOR INTEGER */
       $stmt2->execute(); /* EXECUTE QUERY */
       $stmt2->store_result(); 
       $stmt2->bind_result($postid,$file,$type,$size,$imagedate,$sort); /* BIND THE RESULTS TO THESE VARIABLES */
       while($stmt2->fetch()){ /* START FETCHING ALL FILES FROM THE CURRENT FETCHED POST */
         ?>
           <img src="data/profile/posted_data/<?php echo $file; ?>" width="220" height="220"><br>
         <?php
       } /* END OF FETCHING FILES FOR THE CURRENT FETCHED POST */
       $stmt2->close();
    } /* END OF SECOND PREPARED STATEMENT */
    ?>
        </td> <-- END OF COLUMN -->
      </tr> <!-- END OF ROW -->
    <?php

  } /* END OF WHILE LOOP */
  $stmt->close();
} /* END OF PREPARED STATEMENT */

edited Aug 25, 2015 at 6:23

Logan Wayne

5,98716 gold badges33 silver badges50 bronze badges

answered Aug 25, 2015 at 5:37

Naguib Ihab

4,5368 gold badges53 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

koroush Over a year ago

I already have different table actually the post table is the child table.

user2864740 Over a year ago

@koroush I suspect there should be an 'attachments' table (a 'child table' for each post).

Naguib Ihab Over a year ago

I agree with @user2864740, there's no reason to stop you from having a child table for a child table

Logan Wayne Over a year ago

@koroush - you need to realize that you are on the right track, you just have to consider @NaguibIhab's solution. This will have a better performance than storing Dadashi.jpg, 3.jpg, 2.jpg in a single row (where you will have to use explode() when fetching the images; and when you want to add more image, you have to fetch first image row and add the string of the new file, then update it back to that row; and many more).

koroush Over a year ago

@Naguib Ihab I think I am going to give your solution a try. Please stay tuned for my update.

|

Syed mohamed aladeen · Accepted Answer · 2015-08-25 05:53:27Z

1

try this code

<?php
   $images = array();
   $type = array();
   $size = array();
   if(isset($_POST['submitting'])) 
      {
      if(isset($_FILES['file_array']))
         {
            $user = $_SESSION['user_id'];
            $pname = $_POST['Product_Name'];
            $ProPrice = $_POST['Product_fee'];
            $n_array = $_FILES['files_array']['name'];
        $tmp_name_array = $_FILES['files_array']['tmp_name'];
        $type_array = $_FILES['files_array']['type'];
        $size_array = $_FILES['files_array']['size'];
        $error_array = $_FILES['files_array']['error'];
        for($i = 0; $i < count($tmp_name_array); $i++)
        {
            if(move_uploaded_file($tmp_name_array[$i], "data/profile/posted_data/".$n_array[$i]))
            {
                $images[] = $n_array[$i];
                $type[] = $type_array[$i];
                $size[] = $size_array[$i];
                echo '<div class="form_message_box">' . $n_array[$i] . '   ' . 'Uploaded Successfully' . '</div>' . '<br>';

            }
        }
  $all_images = implode(",",$images);
  $all_types = implode(",",$type);
  $all_sizes = implode(",",$size);
  $query = mysqli_query($conn, "INSERT INTO posts (userid, post_title, file, type, size, image_date) VALUES ('$user', '$pname', 'all_images', '$type', '$size', now())");
                if($query) 
                                        {
                       echo '' . '<br>';
                    }
                        else
                    {
                       echo 'failed!' . '<br>';
                                        }
    }
 }

?>

Extra Edit code

    $posts = "SELECT * FROM posts WHERE userid='$user'";
    $posts_result = mysqli_query($conn, $posts);
    $res = mysqli_num_rows($posts_result);
    while ($posts_result_rows = mysqli_fetch_assoc($posts_result)) {
                              $post_image = $posts_result_rows['files'];
                        }

                        <tr>
            <td width="220">
            <?php 
                 $images = expolde(",",$post_image);
                 foreach($images as $image)
                 {
                 ?>
                            <img src="<?php echo 'data/profile/posted_data/'.$image; ?>" width="220" height="220"><hr>
                 <?php
                 }

            </td>        
        </tr>

<?php
        }
echo "</table>";                
?>

edited Aug 25, 2015 at 5:53

answered Aug 25, 2015 at 5:30

Syed mohamed aladeen

6,8134 gold badges36 silver badges60 bronze badges

3 Comments

Syed mohamed aladeen Over a year ago

down voters any reason for down vote. this is the answer he requires.

koroush Over a year ago

mohammed thanks bro. it works to certain extend. the only problem I have now is that it won't insert the type and size information and I get a blank image.

koroush Over a year ago

mohammed thanks bro can you stay here for a minute I got another question and that is how would I show these on these images on the page? here is my current code for showing images: Please see edited post.

Collectives™ on Stack Overflow

How to insert multiple image into one mysql database row?

2 Answers 2

12 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related