Using the BETWEEN within a complex request

AND var1 LIKE w AND var2 LIKE x AND var3 BETWEEN y AND z

[The_Hat]

Here is what I have it doing right now.. and this doesnt work.

$query = ("SELECT * FROM $table WHERE first_name LIKE '$first_name%' AND
last_name LIKE '$last_name%' AND street1 LIKE '%$street1%' AND city LIKE
'$city%' AND zipcode LIKE '$zipcode%' AND state LIKE
'$state%' AND gender LIKE '$gender%' AND optin_status LIKE '$optin_status%' AND subscriber LIKE '$subscriber%' AND dob BETWEEN '%$dob1' AND '%$dob2' LIMIT 0, $limit ");

both dob1 and dob2 are four digit numbers.. and they do pass to this result page correctly and they do exist in the db.. i can pull results for one if I specify just one like "AND dob LIKE '%$dob1'".. I think it has to do with the AND between the start and end numbers.. I'll bet I need to seperate that part of the request from the rest of it somehow but am struggling with the correct syntax.. I'll bet somebody knows this without even thinking bout it. php/MySql

[edited by: The_Hat at 3:43 pm (utc) on July 24, 2008]

[jamie]

you could try surrounding the between with brackets?

AND subscriber LIKE '$subscriber%' AND (dob BETWEEN '%$dob1' AND '%$dob2') LIMIT 0

[The_Hat]

I had tried some of that but wasn't sure of the correct place to put them.. I tried this and it also doesnt work..

[LifeinAsia]

If DOB1 & DOB2 are numbers, why are you converting them to strings and using a wildcard? Just use:
dob BETWEEN $dob1 AND $dob2

[jamie]

why do the numbers have a % sign before them? that is only used in like afaik

[The_Hat]

they are numbers but the way they are inside of the database is #/##/#### and so I figured the best way to do a search for somebody a certain age (that math is next and should be a problem) would be to look for anything with the correct year of birth.. disregarding the month and day they were born.. but yet still retaining that information in the database.. maybe a more elegant way of pulling it exists? That's why I'm asking..

Edit reason.. the three x's in the original got censored.

[edited by: The_Hat at 5:01 pm (utc) on July 24, 2008]

[LifeinAsia]

they are numbers but the way they are inside of the database is x/xx/#*$!x

Ah, that's your problem right there. You're trying to compare characters, not dates. Remember that while 10/02/2000 comes before 10/01/2001 in terms of comparing dates, "10/02/2000" comes after "10/01/2001" in terms of comparing character strings.

I suggest converting the field to a DATE field. Otherwise, use SUBSTRING (or whatever the similar function is in MySQL) to just comparing the year part of the field.

But in general, storing dates as character strings will almost always come back and bite you in the backside down the road (like now :) ). DBs have so many fuctions for converting and comparing dates- it's almost always better to let the DB do the work for you.

[edited by: LifeinAsia at 5:07 pm (utc) on July 24, 2008]

[The_Hat]

ohh holy snikies.. you are exactly right about that.. that was part of the reason why I was only using the years to compare against.. 2000 would be after 1987.. etc.. but I will convert it to a date field and go down that road.. I appreciate the input.. this is all for a subscriber db interface for some email marketing we will be doing and either I figure out a way for my adreps to build their own list or I have to do it for them.. *wink*

Thanks again.