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
> ==
> "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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
