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
