Urgent Help! Shouldn't be this hard.... :)

[wglenn01]

Ok all I need some help with this. I have been stuck for two days. Basically I have a form that asks a registrant their gender and age, then my script spits out a list of race categories they qualify for. The situation calls for the registrant to be able to register for any category YOUNGER than them, but not older.

 

Screen Shot #1 shows my database setup (this can be changed if you have any better ideas...) that holds the race category information.

 

Here is the sql statement I am using to pull a female racer information, where $race_age is their submitted age.

 

"SELECT * from counts where female=1 and $race_age < age_end and $race_age > age_start"

 

This works, but doesn't pull any categories YOUNGER Than their age.

 

I'm stumpped Thanks for any help in advance.

 

 

[attachment deleted by admin]

[OldWest]

Are you running the query directly in phpMyAdmin? Or can you send your php and html processing query script? Are you echoing all the fields in your php/html?

[jcbones]

Try: (not sure if it will work).

"SELECT * from counts where female=1 and $race_age > age_start"

[wglenn01]

Are you running the query directly in phpMyAdmin? Or can you send your php and html processing query script? Are you echoing all the fields in your php/html?

 

I'm not sure what you mean. I am running the query on my php page that outputs the results of the query into a select box. I'm just not sure how to get it to select the correct age categores, or if there is a better way to structure the database to make it work.

[OldWest]

Can you post your code please for the select box?

[wglenn01]

Try: (not sure if it will work).

"SELECT * from counts where female=1 and $race_age > age_start"

Oh my gosh dude. This might have done it, I will report back I totally couldn't wrap my brain around this.

[Yucky]

I'd have one column 'gender' rather than two and do something like:

0 - male

1 - female

2 - both

 

Then the query could be along the lines of:

SELECT * FROM races WHERE gender = $user_gender OR gender = 2 AND $user_age > age_start AND $user_age < age_end;

 

[litebearer]

btw probably need to use the less than or = to / greater than or = to, as if the age matches either end of the span (ie 19 - 34 span - age =19 or 34) it will not show for on  < or > -

 

make sense?

[wglenn01]

btw probably need to use the less than or = to / greater than or = to, as if the age matches either end of the span (ie 19 - 34 span - age =19 or 34) it will not show for on  < or > -

 

make sense?

 

Will do, thank you all! I think we have it. Two days of scratching my head on that one. Mabey I need to leave the building for a bit

[DavidAM]

You could use IN for the gender and BETWEEN for the age.

 

SELECT * FROM races 
WHERE gender IN ($user_gender, 2) 
AND $user_age BETWEEN age_start AND age_end;

 

Note that BETWEEN includes both ends of the range (so it is >= and <=)

 

If you use the code with the OR in it, you need to put parenthesis around that group or it will be evaluated differently than you think:

 

SELECT * FROM races 
WHERE (gender = $user_gender OR gender = 2) 
AND $user_age > age_start 
AND $user_age < age_end;