Re: multiple select statements
| From: | Lerp | Date: | Tue, 05 Mar 2002 16:47:06 +0000 |
| Subject: | Re: multiple select statements | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-17475@lists.php.net to get a copy of this message | ||
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" <gmak@gmak.net> wrote in message
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