0

I'm making a search engine which creates a query depending on what's chosen on the search page. Since the query is limited I can not just include anything in it, which is where I have to do the rest with IF clauses and query while-loops. So, there's no age field in the table, but a birthdate field, so I use an IF function to check if the age is correct and then prints out the username(s) in a while loop.

This works alright, but I also need to add two more fields into the search which are as follows: County and City, right now working on the County selection of the page. What I can't figure out by myself is the logic behind how I'm supposed to manage to print out the users that fit all the required fields without having 1000 IF ELSE's.

I thought of SELECTing and filtering out all the correct zip codes to the county/region chosen, then put it in an array, and then validate the output query while-loop against it, but that didn't work so well either.

In my database I have 3 tables which look like this:

county_table
id, name_of_county

municipial_table
id, county_id, municipial_name

zip_code_table
zip, zip_place_name, municipial_id

These are pre-made for my country. So, given the zip code of the user, I will have to do two different SELECT queries to connect it to the county_table (zip->municipial->county).

So basically, what I'm trying to say; I want the search engine to output the users that have the correct data, this depending if the age, region and city fields are selected. They need to be independent and not like:

if($age>X){
     if($county==Y){
          if($city==Z){
               -OUTPUT RESULTS HERE-
} } }

Now, the problem with this is: What if one of the fields are not requested in the search? Say, the age? The county number? The city? I think what I need are non-nested and independent IF blocks, but I'm not sure how to set it up correctly.

Help very much appreciated, thank you alot.

Elodee
  • 1
  • not a lot of understanding of MySQL uh – mauris Mar 06 '11 at 14:34
  • 1
    Please elaborate on what you meant. – Elodee Mar 06 '11 at 14:37
  • Please show the SQL query that you have so far. There is no reason it shouldn't be possible to add more conditions right there. Also it's unclear how the user_table relates to the zip_code_table. – mario Mar 06 '11 at 14:45
  • `SELECT * FROM user_table WHERE user LIKE '%username_here%' AND online_status = 0 AND email = 'email_here'` That's the query you get if you fill out all the fields. The reason I have to leave out the age for example, is that in the database, the birthdate is recorded like this: YYYY.MM.DD. The whole site relies on this and there's a function I use that converts it to valid age etc. So what I do with the age is just to fetch it, run it through the function to get XX age and `if($age==$age_input){ OUTPUT RESULTS }` – Elodee Mar 06 '11 at 14:53
  • You can calculate the age in the SQL query too. Use `SELECT *, (2011-strftime('%Y', birthday)) AS age WHERE ...` for example (function names depend on database, 2011 should be calculated too obviously). And then use the `age` field in the WHERE clause. -- Likewise you can use a subquery for the zip_code_table methinks – mario Mar 06 '11 at 15:25

1 Answers1

0

My re-inventing the wheel? Unless you're making a search engine for educational purpose, use something that's already been tested and optimized. See this related question.

You are doing it wrong.

Community
  • 1
  • 1
Yanick Rochon
  • 51,409
  • 25
  • 133
  • 214