Page 1 of 1
Select * if today?
Posted: Thu Jul 06, 2006 11:52 pm
by xterra
Hello,
Can someone assist me in a SQL statement. Trying to select * from table where `UnixTime` is within 24 hours?
Thankyou,
Rob.
Posted: Fri Jul 07, 2006 12:12 am
by feyd
Is `UnixTime` a unix timestamp? What do you consider within 24 hours (past, future, surrounding)?
Posted: Fri Jul 07, 2006 12:15 am
by xterra
Thanks for the reply.
The unix time is the unix timestamp that is in the "myDate" column. So it will be something like:
select * from myTable where `myDate` within 24 hours. And by 24 hours, I mean from the start of the day. So if it's Friday night, I want all the records since friday morning at 12:00AM.
Hope this clarifies. Thanks in advance.
Posted: Fri Jul 07, 2006 1:30 am
by feyd
You can use
mktime() and some results from
date() to create a timestamp for both the upper and lower limits. Using the BETWEEN clause in your SQL, you can then use that as the range. Alternately, FROM_UNIXTIME() and another function or two could be used if you want a purely SQL version.
Posted: Fri Jul 07, 2006 3:18 am
by jamiel
Code: Select all
SELECT * FROM myTable WHERE FROM_UNIXTIME(myDate) > CURDATE();
Posted: Fri Jul 07, 2006 1:43 pm
by xterra
feyd:
I took your advice this is as close as I can get, but it still returns nothing:
Code: Select all
$time = time();
$start_time = mktime(0, 0, 0, date('m', $time),date('d', $time),date('Y', $time));
$queryToday = "SELECT * FROM `Statistics` WHERE `ID` = '$ID' BETWEEN $start_time AND $time";
//get all times from early this morning to now
Can you see a problem?
jamiel: no luck either, sorry.
Posted: Fri Jul 07, 2006 1:46 pm
by feyd
No field is specified for the BETWEEN to check.
Posted: Fri Jul 07, 2006 8:17 pm
by printf
If you just want to select all from today, then format FROM_UNIXTIME() to = CURDATE();!
Like so...
Code: Select all
SELECT * FROM my_table WHERE FROM_UNIXTIME(myDate, '%Y-%m-%d') = CURDATE();
pif!
Posted: Sat Jul 08, 2006 12:18 am
by xterra
Whoa. It worked. This doesn't make sense. But it works lol.
$queryToday = "SELECT * FROM `Stat` WHERE `UID` = '$UID' AND FROM_UNIXTIME(`date`, '%Y-%m-%d') = CURDATE()";
Thanks. It just looks weird.
Posted: Sat Jul 08, 2006 12:36 am
by xterra
If I want to find something from yesterday how come I can't just use curdate()-1?
php running on its own
Posted: Sat Jul 08, 2006 4:32 am
by 57an
ok i see you are talking about something related to my question, but it is a little bit different.
Is it posible a php script to run on its own, not triggered by a user, but automatically. For example I want my php script to run every monday at 12:00. Is it posible and how is it posible?
According to me this is not possible, but one told me I'm wrong. So I'm getting confused. Thanx for the help in advance.
Re: php running on its own
Posted: Sat Jul 08, 2006 5:39 am
by Oren
57an wrote:ok i see you are talking about something related to my question, but it is a little bit different.
Is it posible a php script to run on its own, not triggered by a user, but automatically. For example I want my php script to run every monday at 12:00. Is it posible and how is it posible?
According to me this is not possible, but one told me I'm wrong. So I'm getting confused. Thanx for the help in advance.
I don't see how it is related, but just try to google for
cron jobs.
xterra wrote:Whoa. It worked. This doesn't make sense. But it works lol.
It does make sense.
xterra wrote:If I want to find something from yesterday how come I can't just use curdate()-1?
For yesterday try:
Code: Select all
SELECT * FROM `Stat` WHERE `UID` = '$UID' AND FROM_UNIXTIME(`date`, '%Y-%m-%d') = (CURDATE() - INTERVAL 1 DAY)
Posted: Sun Jul 09, 2006 1:52 pm
by xterra
Thanks Oren. That was useful because I was able to put it in a for loop to get data for the last 7 days. Works well, if anyone wants to see:
Code: Select all
for ($i=0;$i<7;$i++)
{
$queryDay= "SELECT * FROM `Stat` WHERE `UID` = '$UID' AND FROM_UNIXTIME(`date`, '%Y-%m-%d') = (CURDATE() - INTERVAL $i DAY)";
$resultDay=mysql_query($queryDay);
$numDay=mysql_numrows($resultDay);
echo "$numDay";
}