I have a section in my script that allows admins to view logs. I want to give them the option to view certain time periods of the logs, ie one hour, day, or week.

I'm having a bit of trouble determining how to phrase the query; I thought about ordering by date descending (thus making the first row the most recent), but am not sure if this is the best way to go about it, nor can I determine how to limit by the time (in the hour example, 3600 seconds). My goal is to select X timeframe from the database itself and display the results.

The field to use to limit by time is named date.

Any help is always appreciated! :)

How is the date saved in the db? What format

Unix timestamp; eg, 1191080691

something like this?

select * from table where date < number and date > second number

Sort of, but not exactly.

I want to:
1. Select all rows from the log table that match the admin id specified
2. From those rows I want to return only those within the specified timeframe. For example, say they last logged in at 8:00 and out at 10:00. I want to retrieve the log from 9:00-10:00.

So I guess the first thing I'd need is the largest timestamp as that would be the most recent record, and then count back by the specified timeframe (hour, day, etc)

Hope this makes more sense. :)

Sort of, but not exactly.

I want to:
1. Select all rows from the log table that match the admin id specified
2. From those rows I want to return only those within the specified timeframe. For example, say they last logged in at 8:00 and out at 10:00. I want to retrieve the log from 9:00-10:00.

So I guess the first thing I'd need is the largest timestamp as that would be the most recent record, and then count back by the specified timeframe (hour, day, etc)

Hope this makes more sense. :)

I'm a little tired otherwise i would create it for you, maybe a programmer can jump in but I can give some hints.

select * from table where admin_id = '1' and date < time() and date > strtotime("-1 day")

its not syntax correct but shows what i mean, shows all from yesterday to now. Maybe you can build off of it. Just have to create timestamps. mktime for example.

http://us.php.net/manual/en/function.mktime.php

You've been most helpful, Steven; thank you :) It's definitely more of a starting point than I had an hour ago.

I'll give it a go and see if I can get it to do what I want it to do.

there are exists usefukk between statement in where clause
For e.g SELECT * from table where date between 123 AND 1234;
This will select all records that you need.

there are exists usefukk between statement in where clause
For e.g SELECT * from table where date between 123 AND 1234;
This will select all records that you need.

Yeah, I'm using the BETWEEN method with "timestamp" (mysql field type) too and it works perfect.

good tips.