PHP & MYSQL - Display user-uploaded photos from Database according to date photos were uploaded

Question

I created photo upload form. Different users are able to upload multiple photos at once and the photos, the user's details and input get stored in the database as shown in picture below:

I want to be able to display the users and their photos in my index.php page according to the date the photos were uploaded. I would like the index.php page to look something like this

I tried to use GROUP_CONCAT() and GROUP BY in my SQL statement hoping to achieve something like the table below which might allow me to loop through the table data in order to display the photos alongside the users

This is my code which did not work:

$sql = "SELECT id, unique_id, GROUP_CONCAT(fileName) as fileName,  title,date GROUP BY unique_id FROM images"; 
$result = $conn->query($sql); 
if ($result->num_rows > 0) { // This is the line 22 in my code.
    while($row = $result->fetch_assoc()) { 
        echo $row['fileName']; //I wanted to know if echo $row['fileName']; would print something like cow1.jpg, cow2,jpg etc.
    } 
} 

But I am getting error as follows:

Notice: Trying to get property of non-object in C:\xampp\htdocs\display.php on line 22

Someone suggested the GROUP_CONCAT() to me. I have done some search but all tutorials give example of how the table will look after using GROUP_CONCAT() but not how to get data from the final table to use in e.g index.php. I am new to GROUP_CONCAT() and I don't really know how to work with that.

Answer 1

After giving you a bit of a rebuke in comments for deleting the previous question, now let's try some "teaching" ;-)

As I said in a comment on the earlier question, this is something where I would not try and get fancy with GROUP_CONCAT. Foremost because it has the disadvantage that a) it is MySQL-specific, so using it kinda hinders portability of your code, and b) more severely, the length of the resulting string you can assemble is limited. File names (plus paths) could easily get longer, if you have different upload directories for different users or additional parameters to include in the URLs, so that would limit the amount of photos a group could contain, before this "explodes" ...

---

The control break I mentioned is a pretty basic programming principle. You loop over a sorted(!) set of data, and compare one or more criteria to see whether they have changed between the previous row and the current one.

Your example requires a simple one-level control break - the date is the only grouping criterion here. But this same principle can be extended to cover multiple levels - say if you had a list of your credit card purchases covering several years, and you want to output them grouped by year first, and then by month second.

The following example loops over an array more or less matching your example data; but looping over a database query result this works pretty much the same.

But remember, in any case, the input data must already be sorted by the break criterion (or criteria, for a multi-level control break; and in that case, also "ordered in the right order", meaning for above example with your CC purchases by year first and then by month second.)

Example with comments should be pretty self-explanatory from here on, so:

<?php

// example data - I left out a few of your columns; the important part is that
// it is sorted by the control group break criterion, in this case the date
$data = [
 ['id' => '1', 'name' => 'John', 'file' => 'cow1.jpg', 'date' => '07-12-2017 01:05'],
 ['id' => '2', 'name' => 'John', 'file' => 'cow2.jpg', 'date' => '07-12-2017 01:05'],
 ['id' => '3', 'name' => 'Dan', 'file' => 'cat1.jpg', 'date' => '08-12-2107 02:09'],
 ['id' => '4', 'name' => 'Dan', 'file' => 'cat2.jpg', 'date' => '08-12-2107 02:09'],
 ['id' => '5', 'name' => 'John', 'file' => 'dog.jpg', 'date' => '09-12-2107 03:10'],
];

echo '<ul>'; // open unordered list
echo '<li>'; // opening list item tag for first grouped set of data

$previousDate = null;
foreach($data as $row) {
  // check if group break has occurred - is the date of this row different
  // from that of the previous one? (since we initialized $previousDate as null,
  // this will be true for the first row in any case)
  if($row['date'] != $previousDate) {
    // break has occured
    if(null != $previousDate) {
      // if this is not the first row, then we have created output before, so
      // now we need to close the previous list item, and open an new one.
      echo '</li>';
      echo '<li>';
    }

    // now we output whatever is needed at the start of a new group - in this case,
    // user name and date, since we want those only once
    echo 'User: ' . $row['name'] . '<br>';
    echo 'Date: ' . $row['date'] . '<br>';
  }

  // now, handle the output of the actual "per-row" data - in this case, the individual image
  echo '[img src="' . $row['file'] . '"]<br>'; // pseudo image tag in lack of real image files

  // remember current row's date as the new previous one, so that we can compare against
  // that in the next loop iteration
  $previousDate = $row['date']; 
}

echo '</li>'; // closing list item tag for last grouped set of data
echo '</ul>'; // close unordered list

If you want something different than an unordered list (a bit ironic not using an ordered list here, I know, but I did not want to spoil the example output with the default item numbering that causes, that might be confusing), you can output the data in other structures as well of course - say, a table instead of a list. Just needs a little thought put into what to output and where/when exactly in each case. Say you wanted a table with three columns name, date and image, and leave name and date blank in each row where they still match the previous one - then you still have to output empty table cells for name and date, so as not to mess up the basic HTML table structure required. So the logic might need slight tweaks, depending on the specific output desired.

Answer 2

Try this:

$sql = "SELECT id, unique_id, GROUP_CONCAT(fileName) AS filenames, title, date FROM images GROUP BY unique_id"; 
$result = $conn->query($sql); 
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) { 
        echo $row['filenames'];
    }
}