adrianTNT Posted September 5, 2006 Share Posted September 5, 2006 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. Link to comment https://forums.phpfreaks.com/topic/19777-how-to-select-records-by-a-given-date-range/ Share on other sites More sharing options...
Wintergreen Posted September 5, 2006 Share Posted September 5, 2006 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] Link to comment https://forums.phpfreaks.com/topic/19777-how-to-select-records-by-a-given-date-range/#findComment-86441 Share on other sites More sharing options...
obsidian Posted September 5, 2006 Share Posted September 5, 2006 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 Link to comment https://forums.phpfreaks.com/topic/19777-how-to-select-records-by-a-given-date-range/#findComment-86452 Share on other sites More sharing options...
adrianTNT Posted September 5, 2006 Author Share Posted September 5, 2006 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. Link to comment https://forums.phpfreaks.com/topic/19777-how-to-select-records-by-a-given-date-range/#findComment-86467 Share on other sites More sharing options...
adrianTNT Posted September 5, 2006 Author Share Posted September 5, 2006 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. Link to comment https://forums.phpfreaks.com/topic/19777-how-to-select-records-by-a-given-date-range/#findComment-86710 Share on other sites More sharing options...
obsidian Posted September 6, 2006 Share Posted September 6, 2006 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 Link to comment https://forums.phpfreaks.com/topic/19777-how-to-select-records-by-a-given-date-range/#findComment-87087 Share on other sites More sharing options...
adrianTNT Posted September 6, 2006 Author Share Posted September 6, 2006 Works great, thanks [b]Obsidian[/b]. Link to comment https://forums.phpfreaks.com/topic/19777-how-to-select-records-by-a-given-date-range/#findComment-87120 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.