RE: MultSelect ListBox hell!

"Bastien Koert" <bastien_k@xxxxxxxxxxx> · Wed, 22 Sep 2004 10:27:04 -0400

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 <stuart4m@xxxxxxxxx>
To: Bastien Koert <bastien_k@xxxxxxxxxxx>, php-db@xxxxxxxxxxxxx
Subject: RE:  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 <bastien_k@xxxxxxxxxxx> 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 <stuart4m@xxxxxxxxx>
> >To: php-db@xxxxxxxxxxxxx
> >Subject:  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 more
> >values (like a multi select should allow) I get a
> "no
> >records" return.
> >So something like this using in both AND:
> >
> >I choose from Industry : Accounting and Finance
> >From LocationState: I choose Alabama
> >great it gives back to me all accounting and
> finance
> >records Alabama
> >But if I add in LocationState, Alabama and
> California
> >then a no records is returned.
> >
> >Anyway, if anyone has some pointers or ideas I'd
> >greatly appreciate.
> >
> >Stuart
> >
> >--
> >PHP Database Mailing List (http://www.php.net/)
> >To unsubscribe, visit: http://www.php.net/unsub.php
> >
>
>
_________________________________________________________________
> Take charge with a pop-up guard built on patented
> Microsoft® SmartScreen
> Technology.
>
http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines
>
>   Start enjoying all the benefits of MSN® Premium
> right now and get the
> first two months FREE*.
>
>

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

_________________________________________________________________

Don't just Search. Find! http://search.sympatico.msn.ca/default.aspx The new 
MSN Search! Check it out!

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