check out this code:
*************************************
function build_statement ( $col, $needle ) {
$sWhere = "";
if ( $needle!= "" ) {
$array = explode ( " ", $needle );
$sWhere = "( ";
$firsttime= True;
foreach ( $array as $str ) {
if ( !$firsttime)
$sWhere.= "or ";
$sWhere.= "$col like '%$str%' ";
$firsttime= False;
} // foreach
$sWhere.= ")";
} // if ( $needle!= "" )
return $sWhere;
} /* function */
$nombrearea = strtoupper ( $nombrearea );
if ( $nombreempresa ) {
$sWhere.= armar_where ( "UPPER(e.nombreempresa)", $nombreempresa ). " ";
$sql ="SELECT * FROM list WHERE $sWhere"
}
*************************************
----- Original Message -----
From: "Leotta, Natalie (NCI/IMS)" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, March 05, 2002 8:50 AM
Subject: RE: [PHP-DB] Re: multiple select statements
> 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
> > ==
> >
)){
> >
> > $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 . " relationsh
ip.";
> >
> > }
> >
> >
> > 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
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
