By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,784 Members | 3,582 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,784 IT Pros & Developers. It's quick & easy.

Query by Date Range

P: 31
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.
Sep 13 '07 #1
Share this Question
Share on Google+
10 Replies


P: 3
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]
Sep 13 '07 #2

P: 31
the data type is timestamp.
Sep 13 '07 #3

P: 36
If its a unix timestamp use the php time() function or the mysql function of UNIX_TIMESTAMP()
Sep 13 '07 #4

P: 31
thanks, but what am i actually doing to set a range, from midnight to 11.59pm?
Sep 13 '07 #5

P: 36
use something like (UNIX_TIMESTAMP()+(60*60*24))
Sep 13 '07 #6

pbmods
Expert 5K+
P: 5,821
Heya, Record LL.

On the PHP side, you can build your two datetimes like this:
Expand|Select|Wrap|Line Numbers
  1. $thisMorning = date('Y-m-d 00:00:00');
  2. $thisEvening = date('Y-m-d 11:59:59');
  3.  
  4. // Or, for maximum efficiency...
  5. $today = date('Y-m-d');
  6. $thisMorning = $today . ' 00:00:00';
  7. $thisEvening = $today . ' 11:59:59';
  8.  
Then, using the MySQL BETWEEN keyword, it becomes reasonably easy to find the events of the day:
Expand|Select|Wrap|Line Numbers
  1. $_sql = "
  2. SELECT
  3.         *
  4.     FROM
  5.         `alert`
  6.     WHERE
  7.     (
  8.         `datetime`
  9.             BETWEEN
  10.                     '{$thisMorning}'
  11.                 AND
  12.                     '{$thisEvening}'
  13.     )
  14.     ORDER BY
  15.         `id` DESC
  16.     LIMIT 1";
  17.  
Sep 14 '07 #7

P: 31
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.
Sep 14 '07 #8

P: 31
just an update, that worked. thank you
Sep 16 '07 #9

pbmods
Expert 5K+
P: 5,821
Heya, LL.

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

ak1dnar
Expert 100+
P: 1,584
[Query by Date Range - First post here.]
Removed some unnecessary words from the thread title.
Sep 17 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.