List:General Discussion« Previous MessageNext Message »
From:Leif Neland Date:September 5 1999 1:30pm
Subject:Re: help with complex queries
View as plain text  

On Sat, 4 Sep 1999, Geocrawler.com wrote:

> This message was sent from Geocrawler.com by "Apoorva Mohunta"
> <apm@stripped>
> Be sure to reply to that address.
> 
> well i have form whihc when sybmitted will search the DB ans spit out the result. 
> 
>                         Problem:
>                         I have 5 input fields which have two 'states'.
> 
>                         1) Check DB for a specific item.
>                         2) check for all items for that column.
> 
>                         lets say the five variables are
>                         $country - check for all countries or specific
> country.
>                         $item: check for new items or new and old
> items.
>                         $ownership: checks for private or dealer item.
>                         $brand: checks for a particular brand or some
> any brand.
>                         $option: checks for all options or a
> particualr option.
> 
>                         now building the mysql query with ifs
> statement (in php) is fine except that there are a combination 5
> items. Which means thata the quesry is very large SInce i have check
> the state for each item and for that partiucualt combo of the states
> of each item a particular query is executed. simple enuf right? THe
> problem being there too many if statement since i am checking for all
> possible combinations and too many mysql querys (for each if
> statetment 1 query).
> 
>                         I would like to know if things could be made
> simpler but with the same power of having all posisble combos and not
> restricting user. For example i could ask the user to check only one
> brand and one option at a time rather than giving him the option of
> cheking all brands with all options..but i dont want to do that since
> it just isnt logical.
> 
>                         I guess i am looking for soem guidnace of how
> to actually create the query so that the user could get the same
> powerful searching facility and my life would also become a lot
> simpler with simple coding technique
> 
>                         SInce the combinations of 5 variables would
> give me i think 20-25(guessing) different if statements with eqaul
> number of sql queries.  Some guys who have had experience with complex
> searches could really be a godsend and who could help me.
> 
Assume you have your form as
 <input type=checkbox name=colour value=red>
 <input type=checkbox name=colour value=blue>
 <input type=checkbox name=colour value=green>
 <input type=checkbox name=form value=circle>
 <input type=checkbox name=form value=square>

you get your info in arrays.

Build your query in a string:
$query = "select something from some,thing where some.a=where.a";
if (@colour) {
  $query="$query and (false";
  for $col (@colour) {$query="$query or colour=\"$col\";}
  $query="$query)";
};
if (@form) {
  $query="$query and (false";
  for $for (@form) {$query="$query or form=\"$for\";}
  $query="$query)";
};
$query="$query order by ...";

Note the trick "$query and (false", to be able to add clauses with " or
clause", without having to do special for the first clause.

If your initial select doesn't have a "where", similarly add a 
"where true" to the clause.

I'm sure the optimizer will notice true and false are constants, and
remove them from the processing.

Leif

Thread
help with complex queriesGeocrawler.com5 Sep
  • Re: help with complex queriesLeif Neland5 Sep
  • Re: help with complex queriesMartin Ramsch5 Sep