Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
Thread: newbie help
02-14-2012, 12:44 AM #1
- Join Date
- Feb 2012
- Thanked 0 Times in 0 Posts
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.
02-14-2012, 01:41 AM #2
- Join Date
- Jan 2012
- Thanked 32 Times in 32 Posts
The easiest way to get the latest row from something is just do to:
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.Code:select * from table order by id desc limit 1
Users who have thanked KuriosJon for this post:
02-14-2012, 02:49 AM #3
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
Or, if you have a timestamp on each record, I would useCode:SELECT * FROM events WHERE class='whatever' ORDER BY id 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.Code:SELECT * FROM events WHERE class='whatever' ORDER BY timestampfield DESC LIMIT 1
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.