RE: [PHP-DB] MultSelect ListBox hell!

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@yahoo.com> To: Bastien Koert <bastien_k@hotmail.com>, php-db@lists.php.net 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 <bastien_k@hotmail.com> 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@yahoo.com> To: php-db@lists.php.net 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 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!