RE: MultSelect ListBox hell!

"Bastien Koert" <bastien_k@xxxxxxxxxxx> · Wed, 22 Sep 2004 09:37:35 -0400

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