Re: [PHP-DB] Re: multiple select statements

[Andrés Felipe Hernández]

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  from  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 :)
> >
> >
> >  >
> > # 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 

RE: [PHP-DB] Re: multiple select statements

[Leotta, Natalie (NCI/IMS)]

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  from  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 :)
> 
> 
>  
> # 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 BETWEE