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";


}