Page 1 of 1

Obtaining a date range from a mySql database

Posted: Thu Nov 28, 2002 2:03 am
by Slyvampy
I was wondering if anyone could help me, im trying to run a query to obtain a date range from a mySql database.

I have a table like this

+---------------+----------------------+
- takings_date - amount_taken -
+---------------+----------------------+
- 2000-11-17 - 105.00 -
- 2001-08-20 - 110.05 -
- 2001-11-24 - 120.00 -
- 2002-11-27 - 150.10 -
+---------------+----------------------+


I get 2 dates from the input text boxs on the Php page, for example:

Code: Select all

<?php
$first_date = "2001-01-01";
$second_date = "2002-01-01";
?>
Therefore I need help locating a sql command to enable me to access a date range.

This is what I have at the moment, but as you can see it returns all the dates in the database.

Code: Select all

<?php
$dates_returned = mysql_query("SELECT * FROM tbl_daily_takings ORDER BY takings_date ASC", $connection) or die ("(Error: Search Query Failed)");

$dates_handled = -1;

if(mysql_Num_Rows($dates_returned) != 0)
{
	while ( $dates_handled < $dates_returned )
	{
		$date_row = mysql_fetch_row($dates_returned);
		$coloum_takings_date = $date_rowї0];
		$coloum_amount_taken = $date_rowї1];

		$format_of_output_date0 = $coloum_takings_date{0};
		$format_of_output_date1 = $coloum_takings_date{1};
		$format_of_output_date2 = $coloum_takings_date{2};
		$format_of_output_date3 = $coloum_takings_date{3};
		$format_of_output_date4 = $coloum_takings_date{4};
		$format_of_output_date5 = $coloum_takings_date{5};
		$format_of_output_date6 = $coloum_takings_date{6};
		$format_of_output_date7 = $coloum_takings_date{7};
		$format_of_output_date8 = $coloum_takings_date{8};
		$format_of_output_date9 = $coloum_takings_date{9};

		$formatted_coloum_takings_date = "$format_of_output_date8" . "$format_of_output_date9" . "-" . "$format_of_output_date5" . "$format_of_output_date6" . "-" . "$format_of_output_date0" . "$format_of_output_date1" . "$format_of_output_date2" . "$format_of_output_date3";

		echo "$formatted_coloum_takings_date :";

		echo " <B>£</B>$coloum_amount_taken <BR>";

		$dates_handled++;
	}

?>
I think I need a query passing both $first_date and $second_date.

I think the answer is right under my nose, its just brainfreeze. I've spent all morning on the internet and I can't find anything. :?

Cheers,

SteJ.

Posted: Thu Nov 28, 2002 6:33 am
by riley
Change your sql code to

Code: Select all

"SELECT * FROM tbl_daily_takings Where taking_date Between '$first_date' and '$second_date' ORDER BY takings_date ASC"
Also be sure to format the date varibles correctly I find a good way is 'yyyy-mm-dd' although mysql accepts a variety of methods.

Posted: Thu Nov 28, 2002 6:37 am
by dmorris
As i understand - you want the query to only return the dates between the passed dates - if so, why not change the sql to the following:

"SELECT * FROM tbl_daily_takings where taking_date > $first_date and taking_date < $second_date ORDER BY takings_date ASC"


hope that helps

Duncan

Obtaining a date range from a mySql database

Posted: Thu Nov 28, 2002 9:18 am
by Slyvampy
Cheers people, I think that might just do it, however I got my brain to unfreeze and I thought of another way using timestamp instead of date in the mySQL database and using strftime to change the string $first_date and $last_date and putting them into the datebase with mktime. If anyone has any other ideas I would appreciate it.

Thanks a million!

SteJ. :lol: