0

I am struggling with what seems to be a simple task. I have two tables in my database:

Table devices:
id, uid, devicecode, devicename, devicetype

Table devicelogs:
id, devicecode, timelogged, devicestatus

Before my page loads, I get all the user's devices with the following query:

 $sql = "SELECT * FROM devices WHERE `uid`='1'";

This gets me all the user's devices which I then use to create 'switches' on the page using checkboxes.

To set the checkbox's status, I need to then check what the last devicestatus of that device was in the 'devicelogs' table (on or off).

I am guessing I would have to do a join of some sort where the devicelogs table should show the last records of each of the devices.

halfer
  • 19,824
  • 17
  • 99
  • 186
Bruno
  • 511
  • 2
  • 6
  • 19
  • 1
    Possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) – Shadow Apr 12 '17 at 10:11

1 Answers1

0

You can build your query in two steps. First of all, you get the latest timelogged for each devicecode

select  devicecode, max(timelogged) as max_timelogged
from    devicelogs
group by devicecode

The you can use the above query as a filter, by joinig it with the original tables

select  t1.*, t2.timelogged, t2.devicestatus
from    devices t1
join    devicelogs t2
on      t1.devicecode = t2.devicecode
join    (
            select  devicecode, max(timelogged) as max_timelogged
            from    devicelogs
            group by devicecode
        ) t3
on      t2.devicecode = t3.devicecode and
        t2.timelogged = t3.max_timelogged
Stefano Zanini
  • 5,876
  • 2
  • 13
  • 33
  • Getting the record with max value type of questions are asked pretty much every day. Pls mark them as duplicate rather than try to answer each question separately. – Shadow Apr 12 '17 at 10:13
  • Thanks Stefano, I understand the concept now! I appreciate it. – Bruno Apr 12 '17 at 10:19