complex SQL alternative to a PHP setup?

[PC Nerd]

Hi,

 

Ive got a database that has 2 fields that i want to compare: Year_Start and Year_End.

 

if i have a variable that stores a year, I want to return all teh records where my variable is in the range of Year_Start and Year_End.

 

ie - I cant use the following, and I dont want to have to do it in PHP because of the scale of the database.

SELECt * FROM `catalog` WHERE `Year_Start` < $YEAR .......

 

 

Ive been looking for a way to do it in SQL but i cant find a method to do it.

 

Any suggestions of a SQL statement, or a very fast PHP work around woudl be fantastic ( its over 600 records, likely to grow reasonably rapidly).

 

Thanks in advance.

[MadTechie]

why cant you do

SELECT * FROM `catalog` WHERE `Year_Start` > $YEAR AND `Year_End` < $YEAR

?

[PC Nerd]

eg:

 

Year_Start = 2001

Year_End = 2008

 

$YEAR = 2004

 

 

thats basically saying :

 

where 2001 is greater that 2004 AND 2008 is less than 2004

 

that wouldnt work would it????

 

what im really looking for is an SQL solution similar to:

 

where $YEAR > Year_Start AND $YEAR < Year_End

 

however from my knowledge and resources that doesnt work.

 

Thanks for the speedy reply.

[MadTechie]

Opps reverse that

SELECT * FROM `catalog` WHERE `Year_Start` <= $YEAR AND `Year_End` >= $YEAR

2001 <= 2004 = true

2008 >= 2004 = true

[PC Nerd]

***

 

the problem is that i also have records where the Year_Start is 1999 etc

 

so therefore:

1999 <= 2004 = true

 

and the same ( but revers incr numbers) for the Year_End

 

Can you see my problem?

Thanks

[MadTechie]

No i can't see the problem..

you have the AND in the SQL!

 

Post some example data with expected results

[Barand]

SELECT * FROM `catalog` WHERE $year BETWEEN `Year_Start`  AND `Year_End`

[PC Nerd]

Data:

    Start      End

 

(1) 1999      2005

(2) 2001      2003

(3) 1995      2008

(4) 2008      2008

 

now if i try teh following values:

 

2001: (1) (2) (3)

2000: (1) (3)

2004: (1) (3)

2008: (3) (4)

 

so what Im trying to do is i have a variable.  i want to return all records where that variable($YEAR) is in between the two given fields. ( Start is always smaller that End, and on occaision they will be the same as shown above....

 

 

 

Thanks for all the effore your going to.

 

 

*** edit: Ill try your example Barand - that sounds about correct. Thanks

[PC Nerd]

*** YIPPE***

Thanks soo much for all your help.

 

Barands solution was correct and is now working.

 

All my resources simply say that the onlw way to do it is where the database field is inbetween the 2 variables.... which i knew sounded wrong but had no idea about how to adapt to my situation.

 

Thanks also MadTechie - I think we were about to hit the nail on the head but Barand beat us.  Thanks