Query by Date Range

[recordlovelife]

Hello theScripts, i've been using a lot of tutorials and forum reads here, so i figured it was time i joined. I've found everything here nothing but helpful.

I was wondering if you all could give me a hand on this. (if this is suppose to be in MySQL forum i apologize in advance).


I'm trying to make a "School Closing" alert for a school website, like when it snows. Pretty much, I made a simple form for a secretary to input into with a title and content. Her info goes into a MySQL table (named 'alert') with the fields title, content, datetime (which is set to timestamp with auto_update), and id (auto_increment).

Pretty much, i want the query to look at the table, if something was put in today, make a div on the site with the conent. If nothing was put in, it wont make a div. Simple.

Somehow, i need the query to check if the post was put in today between midnight and 11:59pm today. So once midnight hits tomorrow, it won't find anything.

I had this concept

[PHP]

<?php
$today = date("j, n, Y");
$query = mysql_query("SELECT * FROM `alert` WHERE datetime = $today ORDER BY `id` DESC LIMIT 0 , 1");
while($row = mysql_fetch_array($query)) {
echo '<div id="alert" style="background:red;border:2px solid #000;"><h1>'.$row['Title'].' - '.$row['datetime'].'</h1><p>'.$row['Alert'].'</p></div>';
}
?>[/PHP]


Now in thereory this should work, needless to say...it isn't though. I have the limit set to the first id because if she wanted to make a change to the current alert, all she has to do is retype and it will pick the top one.


Let me know what you all this, and thanks in advance.

[azang]

whats the data type of 'datetime'?
If the type is datetime, I think you need to do this:

[PHP] $today = date("Y-m-d H:i:s");[/PHP]

[link removed]

[recordlovelife]

the data type is timestamp.

[Weisbartb]

If its a unix timestamp use the php time() function or the mysql function of UNIX_TIMESTAMP()

[recordlovelife]

thanks, but what am i actually doing to set a range, from midnight to 11.59pm?

[Weisbartb]

use something like (UNIX_TIMESTAMP()+(60*60*24))

[pbmods]

Heya, Record LL.

On the PHP side, you can build your two datetimes like this:

$thisMorning = date('Y-m-d 00:00:00');
$thisEvening = date('Y-m-d 11:59:59');
 
// Or, for maximum efficiency...
$today = date('Y-m-d');
$thisMorning = $today . ' 00:00:00';
$thisEvening = $today . ' 11:59:59';
 

Then, using the MySQL BETWEEN keyword, it becomes reasonably easy to find the events of the day:

$_sql = "
SELECT
        *
    FROM
        `alert`
    WHERE
    (
        `datetime`
            BETWEEN
                    '{$thisMorning}'
                AND
                    '{$thisEvening}'
    )
    ORDER BY
        `id` DESC
    LIMIT 1";
 

[recordlovelife]

thanks alot, im still new to the whole "date formatting thing". im gonna try this out as soon as i can. thanks for the help all.

[recordlovelife]

just an update, that worked. thank you

[pbmods]

Heya, LL.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)

[ak1dnar]

[Query by Date Range - First post here.]
Removed some unnecessary words from the thread title.