Re: search by multiple varaibles...

On 30 Aug, John Corry wrote: > So far, I have a form that can be used to search a database by three > separate fields. > > //relevant generation of query > //base query > $searchStmt = "SELECT *, hi_price, lo_price from $table where " ; > > //plus concatenated query > if ($price_key) > $searchStmt .= "price_key like '$price_key' and " ; > if ($title) > $searchStmt .= "title like '$title' and " ; > if ($area) > $searchStmt .= "area like '$area' and " ; > > //minus the trailing 'and ' > $stmt= substr($searchStmt, 0, strlen($searchStmt)-4) ; > > But what I really need is to be able to search a broader section of the DB. > This works great if the user only selects one variable for each search > term...but how do I write it if they need to be able to select *multiple* > variables? > > I guess multiple variables are an array...but I can't envision what to do > with it... > > thanks, > John Corry > Loop through the array appending the elements to the WHERE clause and separating them with AND or OR depending on how you want the result? Here is a chunk of code I use to get the results of an array of States (AU not USA so there are only a few) and build part of a WHERE clause. The state codes (integers) are contained in the array $state; $datafile.state is the field state in the table $datafile. $statesel holds the SQL syntax. /*Determine whether all states required etc */ if($num_states==0): /* If they haven't asked for anything, give them Aust */ $state=9; endif; if ($state[0]==10): /* Asked for all states - will come up in index 0*/ $statesel = ""; else: $statesel = " AND ($datafile.state=$state[0]"; if($num_states>1): $i=1; while($i < $num_states): $statesel = $statesel . " OR $datafile.state=$state[$i]"; $i++; endwhile; endif; $statesel = $statesel . ")"; endif; -- David Robley | WEBMASTER & Mail List Admin RESEARCH CENTRE FOR INJURY STUDIES | http://www.nisu.flinders.edu.au/ AusEinet | http://auseinet.flinders.edu.au/ Flinders University, ADELAIDE, SOUTH AUSTRALIA