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
> 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.
> > > >
>
=== message truncated ===
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
