List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 5 1999 3:06pm
Subject:Re: help with complex queries
View as plain text  
On Sat, 1999-09-04 23:58:24 -0500,
Apoorva Mohunta <apm@stripped> wrote:
> 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 wonder, what the actual problem is?  Or maybe I don't understand
right ...

You do a query
   SELECT * FROM table1, table2 WHERE ...
and if $country say to search for a specific coutry, then you add
   table1.country = '$country'
to the WHERE part.  If $country says to check for all country, then
just leave it out from the WHERE part, not restricting the query.
The same for the aother options.

Let's assume, that you code the option "check for all" with the value
'-', this might look something like this in PHP:

  $sqlquery = 'SELECT * FROM table1 AS t1, table2 AS t2 WHERE 1=1';
  if($country!='-') { $sqlquery .= " AND t1.country='$country'"; }
  if($item!='-')    { $sqlquery .= " AND t1.item   ='$item'";    }
  $sqlquery .= " AND t1.ownership='$ownership'";
  if($brand!='-')   { $sqlquery .= " AND t1.brand  ='$brand'";   }
  if($option!='-')  { $sqlquery .= " AND t1.option ='$option'";  }

Or did I miss your actual problem?  Then please better describe, what
you're doing now, and more detailed what you want to archive.

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
help with complex queriesGeocrawler.com5 Sep
  • Re: help with complex queriesLeif Neland5 Sep
  • Re: help with complex queriesMartin Ramsch5 Sep