Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: newbie help

  1. #1
    New to the CF scene
    Join Date
    Feb 2012
    Thanked 0 Times in 0 Posts

    newbie help

    Just starting to experiment with php and mysql but I know very little about either. I'm not asking for someone to do the work for me just some guidance in the right direction.

    Basically I have a mysql database which is populated automatically every 60 seconds with current air temperature, set temperature, date/time, events etc. and what I want to do is have my web page display the latest information available.

    There are 5 tables for various different things. they are comfort, events, temperatures, settings and timer.

    In the tables I am interested in (events and temperatures) I have fields for time, class, and status in events and time, airtemp, settemp and comfort in temperatures.

    I have managed to have it fetch the "latest" airtemp and settemp however not sure if I have done this the long way by first doing a SELECT max(time) FROM temperatures, setting that as $maxtime and then doing a SELECT airtemp FROM temperatures WHERE time=$maxtime. Is this a long winded way to achieve this?

    My other problem is i want to find the latest of a certain class in events and report its status but cant get anything to work to achieve that.

    Any help or advice would be very much appreciated.


  2. AdSlot6
  3. #2
    Regular Coder
    Join Date
    Jan 2012
    Thanked 32 Times in 32 Posts

    The easiest way to get the latest row from something is just do to:

    select * from table order by id desc limit 1
    You can replace the id column in the order by with time or anything else if you don't have an auto-incrementing primary key...although you should in this case.

  4. Users who have thanked KuriosJon for this post:

    RichPyke (02-14-2012)

  5. #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,945 Times in 4,906 Posts
    And it's hard to tell for sure, but I think what you need for "latest of a certain class in events" is something like
    SELECT * FROM events WHERE class='whatever' ORDER BY id DESC LIMIT 1
    Or, if you have a timestamp on each record, I would use
    SELECT * FROM events WHERE class='whatever' ORDER BY timestampfield DESC LIMIT 1
    I would personally favor a timestamp field over an auto_increment id field, as it gives you much more usable information. But if you do have such a field, you would want to create an index on it or your ORDER BY will have miserable performance. From that standpoint, an auto_increment field that is the primary key will be better, even if less usable.
    Be yourself. No one else is as qualified.

  6. Users who have thanked Old Pedant for this post:

    RichPyke (02-14-2012)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts