There are even easier ways to do it than that... using if statements. so,
start with something like this:
$query.="select ";
then let's say you have a buch of clickboxes which your user can check which
will construct your select statements, so,
if ($clickbox1="<value>") {
$query.="<field>";
}
if ($clickbox2="<value>") {
$query.="<anotherfield>";
}
then at the end: $query.="from <table>";
mysql_query($query)... and off you go...don't forget the little period,
otherwise it won't concat everything together as you build your query. If
you need to select from different tables, just add another variable to your
if statements..
If you want to see a real example, here's a snip I use to built an insert
statement, which will take any number of values from an array, create a
table based on their field names, and then insert them into that table..:
//start of insert...
$insert.="insert out_fuzzy set ";
$count=$count+1;
//loops through array
for ($i=0; $i<$cols_compare; $i++)
{
$insert.=mysql_field_name($result_compare, $i);
$insert.="_compare=\"";
$insert.=$compare[$i];
$insert.="\", ";
$insert.=mysql_field_name($result_compare, $i);
$insert.="_base=\"";
$insert.=$result_base[$i];
$insert.="\", ";
$insert.=mysql_field_name($result_compare, $i);
$insert.="_pct=\"";
$base_tmp=trim(strtoupper($result_base[$i]));
$compare_tmp=trim(strtoupper($compare[$i]));
$number=similar_text($base_tmp, $compare_tmp, $last_sim);
$insert.=number_format($last_sim,1);
$insert.="\", ";
Hope that helps.
aron
"Lerp" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> 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
