Hello and welcome to our community! Is this your first visit?
Register
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
    Posts
    1
    Thanks
    2
    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.

    Thanks
    Rich

  • #2
    Regular Coder
    Join Date
    Jan 2012
    Posts
    134
    Thanks
    0
    Thanked 32 Times in 32 Posts
    Yes

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

    Code:
    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.

  • Users who have thanked KuriosJon for this post:

    RichPyke (02-14-2012)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,449
    Thanks
    76
    Thanked 4,373 Times in 4,338 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
    Code:
    SELECT * FROM events WHERE class='whatever' ORDER BY id DESC LIMIT 1
    Or, if you have a timestamp on each record, I would use
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • 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
    •