check out this code: *************************************
function build_statement ( $col, $needle ) { $sWhere = ""; if ( $needle!= "" ) { $array = explode ( " ", $needle ); $sWhere = "( "; $firsttime= True; foreach ( $array as $str ) { if ( !$firsttime) $sWhere.= "or "; $sWhere.= "$col like '%$str%' "; $firsttime= False; } // foreach $sWhere.= ")"; } // if ( $needle!= "" ) return $sWhere; } /* function */ $nombrearea = strtoupper ( $nombrearea ); if ( $nombreempresa ) { $sWhere.= armar_where ( "UPPER(e.nombreempresa)", $nombreempresa ). " "; $sql ="SELECT * FROM list WHERE $sWhere" } ************************************* ----- Original Message ----- From: "Leotta, Natalie (NCI/IMS)" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, March 05, 2002 8:50 AM Subject: RE: [PHP-DB] Re: multiple select statements > I'd try building it piece by piece if you do have too many combinations - > (I'm not promising efficiency, but I do something similar to this in one of > my programs and it works). > > Have vars for each type of data: > > $firstName = ""; > $lastName = ""; etc. > > Then you can set each one based on the selection boxes you have > > if ($firstNameComboValue <> "select one") > $firstName = $firstNameComboValue; > > >From there you can build your SQL statement: > > $criteria = ""; > if ($firstName <> "") > $criteria .= "'$firstName'"; (I can't remember if PHP needs the > single quotes or not but perl does) > ... for each variable (making sure you put in AND between each one). > > Then you can do: > > $sql = "SELECT <whatever> from <table> WHERE $criteria"; > > I do my db programming in perl so this is probably a horrible mix of the > two, but at least it will give you an idea for somewhere you could start. > > You could also use booleans to determine if each one has been set, but that > would probably just be an extra step that you wouldn't need unless you want > to use them somewhere else too. > > Good luck! > > -Natalie > > > > -----Original Message----- > > From: Lerp [SMTP:[EMAIL PROTECTED]] > > Sent: Tuesday, March 05, 2002 11:47 AM > > To: [EMAIL PROTECTED] > > Subject: [PHP-DB] Re: multiple select statements > > > > Hi there :) > > > > You could construct seperate queries for every possible combination of > > search provided you don't have a huge number of search criteria (form > > elements for your search) > > . > > I did this just recently for a friend finder site. In the search the user > > has four fields that are automatically included in the search and three > > more > > that are optional > > > > I built the sql queries based on every combination of the three optional > > search criterias. Since there are three optional search criterias the > > number > > of queries to be built is 8 to compensate for every combination. > > > > This is one way to do it anyway:) > > > > Hope this helps you out, Joe :) > > > > > > <?php > > > > # stateprovince, country, and relationship are all optional therefor 8 > > combinations for query -- use the other form values as well > > > > > > if(($country == "All") && ($stateprovince == "All") && ($relationship == > > "All")){ > > > > $sql = "SELECT friend_id, first_name, sex, age, city, province_state, > > country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN > > '$age1' > > AND '$age2' AND sexuality ='$sexuality' ORDER BY signupdate DESC"; > > $searchcriteria = $sex . " " . $sexuality . " between the ages of " . > > $age1 > > . " and " . $age2; > > } > > > > elseif(($country != "All") && ($stateprovince == "All") && ($relationship > > == > > "All")){ > > > > $sql = "SELECT friend_id, first_name, sex, age, city, province_state, > > country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN > > '$age1' > > AND '$age2' AND sexuality ='$sexuality' AND country ='$country' ORDER BY > > signupdate DESC"; > > $searchcriteria = $sex . " " . $sexuality . " between the ages of " . > > $age1 > > . " and " . $age2 . " from " . $country; > > } > > > > > > elseif(($country != "All") && ($stateprovince != "All") && ($relationship > > == > > )){ > > > > $sql = "SELECT friend_id, first_name, sex, age, city, province_state, > > country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN > > '$age1' AND '$age2' AND sexuality ='$sexuality' AND country ='$country' > > AND province_state ='$stateprovince' ORDER BY signupdate DESC"; > > $searchcriteria > > = $sex . " " . $sexuality . " between the ages of " . $age1 . " and " . > > $age2 . " from " . $stateprovince . ", " . $country; > > > > } > > > > > > elseif(($country != "All") && ($stateprovince != "All") && ($relationship > > != > > "All")){ > > > > $sql = "SELECT friend_id, first_name, sex, age, city, province_state, > > country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN > > '$age1' > > AND '$age2' AND sexuality ='$sexuality' AND country ='$country' AND > > province_state ='$stateprovince' AND relationship ='$relationship' ORDER > > BY > > signupdate DESC"; > > $searchcriteria = $sex . " " . $sexuality . " between the ages of " . > > $age1 > > . " and " . $age2 . " from " . $stateprovince . ", " . $country . " > > looking > > for " . $relationship . " relationship."; > > > > } > > > > > > elseif(($country == "All") && ($stateprovince != "All") && ($relationship > > != > > "All")){ > > > > $sql = "SELECT friend_id, first_name, sex, age, city, province_state, > > country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN > > '$age1' > > AND '$age2' AND sexuality ='$sexuality' AND province_state > > ='$stateprovince' AND relationship ='$relationship' ORDER BY signupdate > > DESC"; > > $searchcriteria = $sex . " " . $sexuality . " between the ages of " . > > $age1 > > . " and " . $age2 . " from " . $stateprovince . " looking for " . > > $relationship . " relationsh ip."; > > > > } > > > > > > elseif(($country == "All") && ($stateprovince == "All") && ($relationship > > != > > "All")){ > > > > $sql = "SELECT friend_id, first_name, sex, age, city, province_state, > > country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN > > '$age1' > > AND '$age2' AND sexuality ='$sexuality' AND relationship ='$relationship' > > ORDER BY signupdate DESC"; > > $searchcriteria = $sex . " " . $sexuality . " between the ages of " . > > $age1 > > . " and " . $age2 . " looking for " . $relationship . " relationship."; > > > > } > > > > > > elseif(($country != "All") && ($stateprovince == "All") && ($relationship > > != > > "All")){ > > > > $sql = "SELECT friend_id, first_name, sex, age, city, province_state, > > country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN > > '$age1' > > AND '$age2' AND sexuality ='$sexuality' AND country ='$country' AND > > relationship ='$relationship' ORDER BY signupdate DESC"; > > $searchcriteria = $sex . " " . $sexuality . " between the ages of " . > > $age1 > > . " and " . $age2 . " from " . $country . " looking for " . $relationship > > . > > " relationship."; > > > > } > > > > > > elseif(($country == "All") && ($stateprovince != "All") && ($relationship > > == > > "All")){ > > > > $sql = "SELECT friend_id, first_name, sex, age, city, province_state, > > country, relationship FROM FRIEND WHERE sex = '$sex' AND age BETWEEN > > '$age1' > > AND '$age2' AND sexuality ='$sexuality' AND country ='$country' ORDER BY > > signupdate DESC"; > > $searchcriteria = $sex . " " . $sexuality . " between the ages of " . > > $age1 > > . " and " . $age2 . " from " . $stateprovince; > > > > } > > > > > > #connect to db here > > > > odbc_do($connectionToDb, $sql); > > > > blah, blah, blah :) > > > > > > > > > > > > > > "Geoffrey Makstutis" <[EMAIL PROTECTED]> wrote in message > > 002201c1c460$85f5fd80$8eaf7ad5@nt">news:002201c1c460$85f5fd80$8eaf7ad5@nt... > > Hi, > > > > I've got an HTML form which allows users to select various criteria to > > search for in my database (MySQL). > > > > The problem is that I can't seem to figure out how create the SELECT > > statement, given the fact that they could choose any or none of the > > criteria. > > > > Does anyone know of a way to dynamically create a SELECT statement that > > might have different numbers of criteria? > > > > Thanks > > > > > > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php