(MySQL) Search by Time Span

@evenstar7139Jul 05.2006

How do I tell MySQL to narrow it’s search by rows whose datetime collum has a date that falls within a specific time span? Like for example if I want to search for all rows with a datetime between January 1-31 2003

to post a comment

PHP

16 Comments(s) _↴

@bokehJul 05.2006 — #

[code=php]<?php
 
 function DateToDATETIME($date)
 {
 return date('Y-m-d G:i:s', strtotime($date));
 }
 
 # start: table and column names
 $tablename   = 'Table name';
 $date_column = 'DATETIME column name';
 # end: table and column names
 
 # start: convert dates to DATETIME
 $start = DateToDATETIME('January 1 2003');
 $end   = DateToDATETIME('January 31 2003');
 # end: convert dates to DATETIME
 
 $query = <<<END
 
 SELECT * 
 FROM $tablename
 WHERE $date_column >= '$start'
 AND $date_column <= '$end'
 
 END;
 
 ?>[/code]

@evenstar7139authorJul 06.2006 — #Also, how could I tell it to search for rows created during the current month (e.g. july 2006)?

@bokehJul 06.2006 — #Well you would need to have a field in each row that stores its creation time.

@evenstar7139authorJul 06.2006 — #I do have that.

@bokehJul 06.2006 — #

[CODE]WHERE MONTH($date_column) = 7 
 AND    YEAR($date_column) = 2006[/CODE]

@evenstar7139authorJul 06.2006 — #But I want this page to pull up the current month's news post when somebody first goes to it. I would have to manually change that each month. Anyway to tell it to look for the current month?

@bokehJul 06.2006 — #Try to ask the question you want to ask right from the start. If you want it dynamic it would be helpful if you mentioned this. You should be able to do it like this but I didnt test it.

[CODE]WHERE MONTH($date_column) = MONTH(CURDATE())
 AND YEAR($date_column) = YEAR(CURDATE())[/CODE]

@evenstar7139authorJul 07.2006 — #Okay and if it doesn't find anything for the current month, how can I tell it to keep going back by one month until it does?

@bokehJul 07.2006 — #With a loop... either in [URL=http://dev.mysql.com/doc/refman/5.0/en/while-statement.html]MySQL[/URL]

or in PHP ([URL=http://es2.php.net/manual/en/control-structures.for.php]for[/URL], [URL=http://es2.php.net/while]while[/URL]).

@evenstar7139authorJul 07.2006 — #What's the MySQL command to back up a month? Well...or the PHP one if it would be better.

@bokehJul 07.2006 — #[b]-1[/b]

@SheldonJul 07.2006 — #So WHERE MONTH($date_column) = (MONTH(CURDATE()) - 1)
?????

@bokehJul 07.2006 — #

[code=php]<?php
 
 $i = 0;
 $max_attempts = 12;
 
 $tablename = 'table';
 $date_column = 'date';
 
 while($i < $max_attempts)
 {
 $year = date('Y', $ts = mktime(0, 0, 0, date('n') - $i++, 1, date('Y')));
 $month = date('n', $ts);
 $query = 'SELECT * '.
 "FROM $tablename ".
 "WHERE MONTH($date_column) = $month ".
 "AND YEAR($date_column) = $year";
 $result = mysql_query($query) or die(mysql_error());
 if($num_rows = mysql_num_rows($result)
 {
 break;
 }
 }
 
 if($num_rows)
 {
 # deal with your query results here
 }
 else
 {
 # max attempts reached without a result
 }
 
 ?>[/code]

Or:

[code=php]while($i < $max_attempts)
 {
 $date = date('Y-m-d', $ts = mktime(0, 0, 0, date('n') - $i++, 1, date('Y')));
 $query = 'SELECT * '.
 "FROM $tablename ".
 "WHERE MONTH($date_column) = MONTH($date) ".
 "AND YEAR($date_column) = YEAR($date)";
 $result = mysql_query($query) or die(mysql_error());
 if($num_rows = mysql_num_rows($result)
 {
 break;
 }
 }[/code]

@evenstar7139authorJul 07.2006 — #Okay I've been looking that last bout of code you posted real good to learn how it works and I have a question.

Why do you put the table and collum names for the MySQL query in variables?

@SheldonJul 07.2006 — #So that it is dynamic and can be used easier, you can just set these to vars each time.

$tablename = 'table';

$date_column = 'date';

@bokehJul 08.2006 — #So that it is dynamic and can be used easier, you can just set these to vars each time.

[code=php]$tablename = 'table';
 $date_column = 'date';[/code]

[/QUOTE]That's right! I can just copy and paste this query into another script and dont need to edit it at all even if the table and column names of the other script are completely different. Also there is less chance someone will "break" the code when editing it.

Also in #PHP _↴

Display User's name after login I have installed this language translator How to get seaqrch engine query for for submited web forms?

Success!

Help @evenstar7139 spread the word by sharing this article on Twitter...

Tweet This

(MySQL) Search by Time Span

16 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

(MySQL) Search by Time Span

16 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

16 Comments(s) _↴

Also in #PHP _↴