asearching between 2 dates

[Mushroom-Graphics]

Hi,

I have a table that had car parts. There are 2 columns First_Year and Last_Year. These are the dates for the model years a part is availabel for. I have a date drop down, that can be picked from, so if you pick, Modl = Mustang GT, Year = 1997, I need to get all of the parts for the models that the year includes 1997. It could be the engine is 1995 to 1998.

I have tired this:
$WADbSearch1->addComparison("IDModel_new","".((isset($_POST["model"]))?$_POST["model"]:"") ."","AND","=",1);
$WADbSearch1->addComparison("FirstYear_new","".((isset($_POST["year"]))?$_POST["year"]:"") ."","AND",">=",1);
$WADbSearch1->addComparison("LastYear_new","".((isset($_POST["year"]))?$_POST["year"]:"") ."","AND","<=",1);

And I have tried hand coding using BETWEEN.

Any helpful hints?

larry

[Ray Borduin]

What data type are you using for your FirstYear_new and LastYear_new fields in your database? Looks like they might be varchar, that could throw off your search results...

Also looks like you have your >= and <= mixed up.

seems like it should be:


$WADbSearch1->addComparison("IDModel_new","".((isset($_POST["model"]))?$_POST["model"]:"") ."","AND","=",1);
$WADbSearch1->addComparison("FirstYear_new","".((isset($_POST["year"]))?$_POST["year"]:"") ."","AND","<=",0);
$WADbSearch1->addComparison("LastYear_new","".((isset($_POST["year"]))?$_POST["year"]:"") ."","AND",">=",0);

[Mushroom-Graphics]

Both are Year (4)

I believe I have them correct. I want:
Greater than or Equal to >= First Year
Less than or Equal to <= Last Year

Larry

[Ray Borduin]

Your example:

$WADbSearch1->addComparison("IDModel_new","".((isset($_POST["model"]))?$_POST["model"]:"") ."","AND","=",1);
$WADbSearch1->addComparison("FirstYear_new","".((isset($_POST["year"]))?$_POST["year"]:"") ."","AND",">=",1);
$WADbSearch1->addComparison("LastYear_new","".((isset($_POST["year"]))?$_POST["year"]:"") ."","AND","<=",1);

would create the query:

WHERE IDModel_new = 'Mustang GT' AND FirstYear_new >= '2000' AND LastYear_new <= '2000'

so you see...

Even though you want
"Greater than or Equal to >= First Year
Less than or Equal to <= Last Year"

since the column is actually on the left hand side of the comparison you are specifying:
"First Year >= the entered value
Last Year <= the entered value"

Which is incorrect, and which is why my suggestion should be correct.

[Mushroom-Graphics]

I posted the reply and was thinking about it and realized your point. Stupid Me!!!!!!

Works perfect now.

Thanks