CODEWITHSUNDEEP
phpmysql
Michael
Michael

Reputation: 25

PHP fetch MAX values from MySQL table for each different user

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?

enter image description here

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.

enter image description here

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>";

  }
}

Upvotes: 0

Views: 70

Answers (1)

M. Eriksson
M. Eriksson

Reputation: 13635

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.

Upvotes: 1

Related Questions