[RESOLVED] Select ID'S where date between certain dates

[MitchEvans]

Hi wondered if anyone could point me in the right direction.
I need to select all id's where the dates are between 1/05/2007 and 31/05/2007.

I have this so far:

IF ($paid=='PAID' AND $date   ???   )

[Piranha]

if ($date >= 2007-03-31 AND $date <= 2007-03-01)

[MitchEvans]

Thanks piranha but i'm not getting the wanted results, i'm trying this:

IF ($paid=='PAID'){
		IF ($date >= '01/05/2007' AND $date <= '31/05/2007'){
		$maycountpaid++;}}

$maycountpaid is equaling 5 and there are only 2 in my database, any ideas? Does the above look correct?

[Piranha]

No, the above doesn't look correct. To get the date correct you need to have year, then month (with two numbers) and last day (also with two numbers). If you want to be able to use both 19xx and 20xx then you need to have the year as 4 numbers. What sign you choose to have in between doesn't matter, as long as it is the same. You can look more into the [man]date[/man] function as well.

But out of curiosity, how do you get $date? If it is from a database it is better to collect the correct dates only from it, not everything.

Edit: A way to look at comparing strings is that it takes the first character, decide if it is above or below, then if needed it does the same with the next character. And so on and so on. I think that you see that it doesn't work as you expect it to when looking at it that way.

[MitchEvans]

Yes the details are stored like that in my database:

01/05/2007

So I just need to count them, I thought that code should pick them up

I've tried this:

$directory = "SELECT * FROM directory WHERE date between '01/05/2007' AND '31/05/2007'";
$directoryResults = mysql_query($directory);
while ($directoryRow = mysql_fetch_array ($directoryResults)){

$paid = $directoryRow["subscription"];
$date= $directoryRow["date"];
IF ($paid=='PAID'){
	$countpaid++;}

But $countpaid =5 for some reason

I've also tried what i showed you above in the same format, doesn't make sense that i'm getting 5 as the value when only 2 of the values appear between the two dates. I think it's counting just the paid ones, not taking the date into account.

It seems to be counting all the dates not the ones between the values

[MitchEvans]

This is the latest $maycount5 is still getting the value, it should be 2, it seems to be counting just $paid and not taking the between dates into consideration, any ideas?!?!?!?!

$directory = "SELECT * FROM directory";
$directoryResults = mysql_query($directory);
while ($directoryRow = mysql_fetch_array ($directoryResults)){

$paid = $directoryRow["subscription"];
$date= $directoryRow["date"];
IF ($paid=='PAID'){
	$countpaid++;}
IF ($paid=='FREE'){
	$countfree++;}	
IF ($paid=='PAID' AND $date >= '01/05/2007' AND $date <= '31/05/2007'){

	$maycountpaid++;}
}

[liquorvicar]

How is the date stored in your database? If it's stored as a text string then your SQL query will match all rows, not just the rows you want to match. I'd suggest changing your table structure so that the date column uses a date storage type. Then

$directory = "SELECT * FROM directory WHERE date between '2007-05-01' AND '2007-05-31'";

will do exactly what you want it to.

[MitchEvans]

Excellent thank you so much, had to chnage how I was storing it, was storing it as VARCHAR. You're a genius, thanks a lot!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!