PHP fetch MAX values from MySQL table for each different user

Question

I have a table with many different users on my website. They upload the picture. How can I display the last uploaded picture with the highest ID number?

If I do this query:

SELECT user_name, MAX(pictid) FROM `pictures` GROUP BY user_name;

It works in PHP My Admin, but it returns empty result on the website.

Username is written correctly, just ID part is empty. Why is that? That is my code:

    include('connection.php'); 

$sql = "SELECT user_name, MAX(pictid) FROM `pictures` GROUP BY user_name;"; 
$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
  // output data of each row

   while($row = $result->fetch_assoc()) {

echo "<tr><td>". $row["user_name"] . "</td><td>". $row["pictid"] . "</td><td><img style='width:90px;' src='../uploads/".$row["picture"]."'</td><tr>";

  }
}

When selecting functions like MAX(), SUM(), COUNT() etc, MySQL will return the value with that name, so to get the result for that function, you need to call it with: `$row['MAX(pictid)']`, unless you alias it: `SELECT user_name, MAX(pictid) AS pictid FROM ...` — M. Eriksson, Sep 05 '21 at 09:12
I posted it as a proper answer below. If it solved the problem, please accept it. — M. Eriksson, Sep 05 '21 at 09:27

M. Eriksson · Accepted Answer · 2021-09-05T09:28:59.720

When using functions like MAX(), SUM(), COUNT() etc, MySQL will return those values with a different name (so it won't collide with other column names you have in the query).

So in your case, instead of $row["pictid"], it should be $row["MAX(pictid)"].

However, since you're not selecting the pictid "as is", you can alias that function:

SELECT user_name, MAX(pictid) as pictid FROM `pictures` GROUP BY user_name

(note the as pictid in the query above)

Now you can fetch the value using:

$row['pictid']

Side note:

I would recommend that you display all errors and warnings while developing in your local environment. In this case, you should have gotten a warning about undefined index/array key pictid, which would have helped you debugging the problem.

And don't forget the fix the broken <img> tag as @brombeer pointed out in the comments to your question.

PHP fetch MAX values from MySQL table for each different user

1 Answers1

Side note: