Jump to content


Photo

How to select records by a given date range ?


  • Please log in to reply
6 replies to this topic

#1 adrianTNT

adrianTNT
  • Members
  • PipPipPip
  • Advanced Member
  • 146 posts

Posted 05 September 2006 - 02:57 PM

Hello.
In my database I have records (transactions) that have a transaction_date field.
The transaction_date filed value is formatted like this: "2006-09-15 00:00:00"
I will send the month, day and year as URL variable (I will send both start date and end date)
How should the PHP code look in order to show only the records in that date range? ???
Thank you.

- Adrian.

#2 Wintergreen

Wintergreen
  • Members
  • PipPipPip
  • Advanced Member
  • 107 posts

Posted 05 September 2006 - 03:15 PM

This is a complete stab in the dark, but

SELECT * FROM table WHERE month(date_column) >= 'number_of_month' AND month(date_column) <= 'number_of month'


#3 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 05 September 2006 - 03:23 PM

you'd have to set up your start and end variables in a timestamp format first (YYYY-MM-DD), then, you simply run a query to see what is in between them:
<?php
$start = "$stYear-$stMonth-$stDay";
$end   = "$edYear-$edMonth-$edDay";
$sql = mysql_query("SELECT * FROM tableName WHERE DATE(dateCol) >= $start AND DATE(dateCol) <= $end");
?>

hope this helps
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#4 adrianTNT

adrianTNT
  • Members
  • PipPipPip
  • Advanced Member
  • 146 posts

Posted 05 September 2006 - 03:33 PM

I made some more experiments while waiting a reply, I got to something that worked by this code:

<?php 
$start_date = ($start_year.'-'.$start_month.'-'.$start_day.' '.$start_hour.':'.$start_minutes.':'.$start_seconds);
$end_date = ($end_year.'-'.$end_month.'-'.$end_day.' '.$end_hour.':'.$end_minutes.':'.$end_seconds);
// add some slashes needed in query format
$start_date="'".$start_date."'";
$end_date="'".$end_date."'"; ?>
Then lower in code I have:
<?php 
$query_Recordset_transactions = sprintf("SELECT * FROM transactions WHERE tr_user_id = %s AND tr_date BETWEEN $start_date and $end_date", GetSQLValueString($colname_Recordset_transactions, "text")); ?>
This works for now so I will use this one if it will not generate problems.
Thanks for both your replies.

#5 adrianTNT

adrianTNT
  • Members
  • PipPipPip
  • Advanced Member
  • 146 posts

Posted 05 September 2006 - 09:40 PM

I got to another issue, I hope one of you can help :)
I needed to display records between two given dates, it works fine.
Then I added this code...
if start and end date ranges are not set then end_date is today and start_date is 7 days ago; so that by default it displays records for last 7 days.
Is there a function to decrease 7 days from a date like '2006-09-06 00:00:00' ? A code block is also nice if there isn't a function for this :)

Thanks.
- Adrian.

#6 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 06 September 2006 - 01:35 PM

you can do it one of two ways:

with SQL:
DATE_SUB(CURDATE(), INTERVAL 7 DAY)

with PHP:
<?php
$date = date('Y-m-d', strtotime("today -7 days"));
?>

good luck
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#7 adrianTNT

adrianTNT
  • Members
  • PipPipPip
  • Advanced Member
  • 146 posts

Posted 06 September 2006 - 02:17 PM

Works great, thanks Obsidian.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users