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 == "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' 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 . " 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 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