Billo Billo - 1 year ago 88
MySQL Question

PHP MySQL query - select all users but only display the earliest logon times for each user

I want to be able to bring back the earliest logon time per user, so only 1 record (the earliest record) displays for each user

I've tried various ways of

but can't seem to get it quite right (if that is actually the correct way of doing).
is the unique value which can be used to GROUP BY

Here's the code I'm currently working with..

SELECT username, name, logon, added FROM data WHERE (date(added) LIKE '$date') AND (logon = (SELECT MIN(logon) FROM data))

I've also tried (below) but only get one result back, only displaying one user

WHERE (date(added) LIKE '$date') AND logon = (SELECT MIN(logon) FROM data)

The first image is what I'm currently getting, the second image is how I want my results to display, please see below

Let me know if you require anymore information, I've tried to put as much as possible




Answer Source

You are close. Your query needs a correlation clause:

FROM data d
WHERE date(d.added) = '$date' AND 
      d.logon = (SELECT MIN(d2.logon) FROM data d2 WHERE =;

Note: The logic for added is confusing. First, you should not use like with dates. And, for that matter, you should not be inserting parameter values into the string, you should be using query parameters. And, actually, I don't see the need for that column; your question doesn't mention added.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download