How to select records by a given date range ?

[adrianTNT]

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)
[b]How should the PHP code look in order to show only the records in that date range?[/b] ???
Thank you.

- Adrian.

[Wintergreen]

This is a complete stab in the dark, but

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

[obsidian]

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:
[code]
<?php
$start = "$stYear-$stMonth-$stDay";
$end  = "$edYear-$edMonth-$edDay";
$sql = mysql_query("SELECT * FROM tableName WHERE DATE(dateCol) >= $start AND DATE(dateCol) <= $end");
?>
[/code]

hope this helps

[adrianTNT]

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

[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."'"; ?>
[/code]
Then lower in code I have:
[code]<?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")); ?>
[/code]
This works for now so I will use this one if it will not generate problems.
Thanks for both your replies.

[adrianTNT]

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.
[b]Is there a function to decrease 7 days from a date like '2006-09-06 00:00:00' ?[/b] A code block is also nice if there isn't a function for this :)

Thanks.
- Adrian.

[obsidian]

you can do it one of two ways:

with SQL:
[code]
DATE_SUB(CURDATE(), INTERVAL 7 DAY)
[/code]

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

good luck

[adrianTNT]

Works great, thanks [b]Obsidian[/b].