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 <stuart4m@xxxxxxxxx> wrote:
> Wow! Thank you!
>
> Stuart
> --- Bastien Koert <bastien_k@xxxxxxxxxxx> 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 <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
>
=== message truncated ===
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
