Get Last Year

[dagger2002]

Ok all i have stumped myself with this 1. and am not understanding the functions on php.net.

I am trying to do something i thought was simple. I want to be able to get a date range, my fiscal year, while listing multiple years.

exp

07/01/2006 - 06/31/2007
07/01/2005 - 06/31/2006
07/01/2004 - 06/31/2005

And i need to keep them grouped in there fiscal year.

Here is what I thought the answer would be:

$year = date("Y");

"SELECT * FROM  `files` WHERE  `file_type` =  '3'AND file_date BETWEEN " . $year . "0631 AND " . $year - $i . "0701 ORDER BY file_date ASC;"

But it isn't working.
Thanks

 

[sleipnir214]

If these are records in a database, the answer may be database specific.

What database system are you using?

What code have you written so far?



Want the best answers? Ask the best questions! TANSTAAFL!

 

[dagger2002]

What database system are you using?

mySql

What code have you written so far?

Im at the begining. This query is the main query. without it im gonna have to hard code the queries. adding 1 each year.

 

[jpadie]

what format is the file_date field in?

 

[dagger2002]

date

 

[sleipnir214]

I see a couple of things.

MySQL stores dates in the format YYYY-MM-DD, and you must use the dashes. You're not including them.

Second, you must surround date strings with singlequotes in MySQL (a date reference will be [tt]'2007-01-01'[/tt]). You're missing those quotes.



Also, your use of backticks in your query are only necessary if you are using MySQL reserved words as identifiers in your query. Otherwise, you don't need them. If you were trying to get your query through PHPMyAdmin or somesuch, it will put those quotes around every identifier, just in case.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[dagger2002]

The query itself will return back the data i need if i hardcode the year. But I would like to be able to get the current year from either php or mysql, and be able to manipulate it so that i can get 1 year before that year.

 

[sleipnir214]

I really need to see more of your PHP code to know where you're going with your code. All I see in this:

"SELECT * FROM  `files` WHERE  `file_type` =  '3'AND file_date BETWEEN " . $year . "0631 AND " . $year - $i . "0701 ORDER BY file_date ASC;"

is a string concatenation that nothing is done with.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[Itshim]

The MySQL operator BETWEEN is defined as:

[i]expr[/i] BETWEEN [i]min[/i] AND [i]max[/i]

Try placing the previous year first and the current year second.

 

[dagger2002]

Ok Itshim i have that working i just need to make $year. as seen here to be totally dynamic. That way i don't have to update this script every Year. I have tried date("y") -1 but that didn't work because, my thoughts please correct me if i am wrong, the date function doesn't return as an int.

 

[Itshim]

The date function returns a string, but that should not matter in this case, PHP will still report: date("Y")-1 as 2006.

In your first example you had: $year-$i. Did you initialize the $i variable, because if not $i will evaluate to zero;

 

[dagger2002]

here is what i was thinking.

$i = 1;
$year = date("Y") - $i;

but i was still getting 2007

 

[Itshim]

I copied the code you posted above

$i = 1;
$year = date("Y") - $i;

[i]echo $year;[/i]

And added the last line. When run on my machine it prints 2006. I cannot see any reason for it to be different on another machine. Sorry.

 

[jpadie]

i was going to suggest that this might all be better done within the query, and point the OP towards the MySql forum.