Bastien, 

Sorry , I hit the send button to fast on the last
response. Is the second series of statements for the
jobs field ? It looks like it is but you copied and
pasted I think from the first set (states) ...or maybe
I"m wrong, the whole thing , needs to be created for
each element.

Stuart
--- Stuart Felenstein <[EMAIL PROTECTED]> wrote:

> Wow!  Thank you!
> 
> Stuart
> --- Bastien Koert <[EMAIL PROTECTED]> wrote:
> 
> > 
> > ah, i get the problem now...
> > 
> > what you need to do is create the entire sql
> > statement on the fly, based on 
> > whether or not there are results for a particular
> > field....
> > 
> > <?
> > ....
> > 
> > //base sql statement
> > $sql = "select * from jobs where record_deleted =
> > 'NO' ";
> > 
> > if (isset($_POST['states'])){
> >    //check to see if the states is an array
> > (multiple items or just one
> >    if (is_array($_POST['state'])){
> >      $sql .= "(";
> >      $x = 0;
> >      foreach ($_POST['state'] as $state)
> >        if ($x == 0){
> >        $sql.= "state = '$state' ";
> >        $x=1;
> >      }else{
> >         $sql .= " OR state = '$state' ";
> >     }
> >     $sql .= ")";
> >   }else{
> >      //$_POST['state'] is not an array
> >      $sql .= "state = '".$_POST['state']."' ";
> > }//end if
> > 
> > if (isset($_POST['job'])){
> >   if (isset($_POST['state'])){  $sql .= " AND "; }
> 
> > //add in the AND if the 
> > state is set
> >   //check to see if the states is an array
> (multiple
> > items or just one
> >    if (is_array($_POST['job'])){
> >      $sql .= "(";
> >      $x = 0;
> >      foreach ($_POST['job'] as $job )
> >        if ($x == 0){
> >        $sql.= "job = '$job ";
> >        $x=1;
> >      }else{
> >         $sql .= " OR job = '$job ";
> >     }
> >     $sql .= ")";
> >   }else{
> >      //$_POST['job'] is not an array
> >      $sql .= "job = '".$_POST['job']."' ";
> > }//end if
> > ...
> > ?>
> > 
> > 
> > And something like that needs to be done for each
> > element in the search
> > 
> > Bastien
> > 
> > >From: Stuart Felenstein <[EMAIL PROTECTED]>
> > >To: Bastien Koert <[EMAIL PROTECTED]>,
> > [EMAIL PROTECTED]
> > >Subject: RE: [PHP-DB] MultSelect ListBox hell!
> > >Date: Wed, 22 Sep 2004 06:49:55 -0700 (PDT)
> > >
> > >Correct, I've added that AND, and so now it will
> > match
> > >jobs by state.
> > >But without choosing state, no job return.
> > >So I'm figuring i'll have to add another series
> of
> > >OR's .  Trying to figure that out now.
> > >
> > >Stuart
> > >--- Bastien Koert <[EMAIL PROTECTED]> wrote:
> > >
> > > > need to have AND between states and jobs
> > > >
> > > > select * from tablename where
> (jobs='accounting'
> > or
> > > > jobs='bookkeeping') and
> > > > (state = 'AL' or state='CA')
> > > >
> > > > Bastien
> > > >
> > > >
> > > > >From: Stuart Felenstein <[EMAIL PROTECTED]>
> > > > >To: [EMAIL PROTECTED]
> > > > >Subject: [PHP-DB] MultSelect ListBox hell!
> > Date:
> > > > Wed, 22 Sep 2004 05:19:36
> > > > >-0700 (PDT)
> > > > >
> > > > >I am in the process of building a search form
> > for
> > > > the
> > > > >database.  The entire thing consists of 3
> multi
> > > > >selects and 2 text boxes with a "contains". 
> Oh
> > and
> > > > >I've only rigged 2 of the multi selects for
> the
> > > > time
> > > > >being.
> > > > >So the way the multiselects should work that
> > the
> > > > user
> > > > >can  choose one or the other or both, but the
> > more
> > > > >criteria provided back to the query , the
> more
> > > > refined
> > > > >the return.
> > > > >
> > > > >Here is the code and my tale of woe
> > > > follows:($projects
> > > > >is a just a holder / variable)
> > > > >
> > > > >function BindEvents()
> > > > >{
> > > > >     global $VendorJobs;
> > > > >
> > > >
> > $VendorJobs->ds->CCSEvents["BeforeExecuteSelect"]
> > > > >= "VendorJobs_ds_BeforeExecuteSelect";
> > > > >}
> > > > >//End BindEvents Method
> > > > >
> > > > >//VendorJobs_ds_BeforeExecuteSelect
> @2-A4F75E44
> > > > >function VendorJobs_ds_BeforeExecuteSelect()
> > > > >{
> > > > >     $VendorJobs_ds_BeforeExecuteSelect =
> true;
> > > > >//End VendorJobs_ds_BeforeExecuteSelect
> > > > >
> > > > >global $VendorJobs;
> > > > >$s_Industry = CCGetParam("s_Industry", "");
> > > > >if (count($s_Industry) > 0 AND
> > > > is_array($s_Industry))
> > > > >{
> > > > >foreach ($s_Industry as $key => $value) {
> > > > >if ($Projects != "") $Projects =
> $Projects.",";
> > > > >$Projects = $Projects."'".$value."'";
> > > > >}
> > > > >}
> > > > >if ($Projects)
> > > > >$VendorJobs->ds->SQL.= " AND
> > (`VendorJobs`.Industry
> > > > IN
> > > > >(".$Projects."))";
> > > > >
> > > > >$s_LocationState =
> > CCGetParam("s_LocationState",
> > > > "");
> > > > >if (count($s_LocationState) > 0 AND
> > > > >is_array($s_LocationState)) {
> > > > >foreach ($s_LocationState as $key => $value)
> {
> > > > >if ($Projects != "") $Projects =
> $Projects.",";
> > > > >$Projects = $Projects."'".$value."'";
> > > > >}
> > > > >}
> > > > >if ($Projects)
> > > > >$VendorJobs->ds->SQL.= " AND
> > > > >(`VendorJobs`.LocationState IN
> > (".$Projects."))";
> > > > >
> > > > >echo "SQL:". $VendorJobs->ds->SQL."<br>";
> > > > >
> > > > >return $VendorJobs_ds_BeforeExecuteSelect;
> > > > >
> > > > >If you notice in the the SQL the "AND", I've
> > > > changed
> > > > >them back and forth to OR and a combo of AND
> in
> > the
> > > > >first and OR in the second.
> > > > >
> > > > >If I have OR in both, then each multi works
> on
> > it's
> > > > >own, no refined / results.  If I have AND in
> > both,
> > > > >then they come together but if I try to add
> 
=== message truncated ===

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to