RE: [PHP-DB] MultSelect ListBox hell!

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*.