RE: [PHP-DB] MultSelect ListBox hell!
It's working great! I replaced the former parser with the implode. All is good again in the world! :) Mucho gracias! Stuart --- "Ford, Mike" <[EMAIL PROTECTED]> wrote: > > Mike > > - > Mike Ford, Electronic Information Services Adviser, > Learning Support Services, Learning & Information > Services, > JG125, James Graham Building, Leeds Metropolitan > University, > Headingley Campus, LEEDS, LS6 3QS, United Kingdom > Email: [EMAIL PROTECTED] > Tel: +44 113 283 2600 extn 4730 Fax: +44 113 > 283 3211 > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MultSelect ListBox hell!
On 23 September 2004 20:53, Stuart Felenstein wrote:
> So here is what I have:
>
> //Here is defines the field variable and uses
> //CodeCharge function to grab the variable
> $s_Industry = CCGetParam("s_Industry", "");
> $s_LocationState = CCGetParam("s_LocationState", "");
> $s_TaxTerm = CCGetParam("s_TaxTerm", "");
>
> //Here is the parsing of the array. I'm not sure
> what the $proj variable is for. I gather it's a
> holder for the array values
>
> if (count($s_Industry) > 0 AND is_array($s_Industry)) {
> foreach ($s_Industry as $key => $value) {
> if ($Proj1 != "") $Proj1 = $Proj1.",";
> $Proj1 = $Proj1."'".$value."'";
> }
> }
$Proj1 now contains a comma-separated list of the values passed to
$s_Industry, which was filled by the CCGetParam() above. The above code
block is equivalent to (and would be better coded as):
if (count($s_Industry) > 0 AND is_array($s_Industry)) {
$Proj1 = "'".implode("','", $s_Industry)."'";
}
which may look somewhat familiar to you!
> //Parsing next array
> if (count($s_LocationState) > 0 AND
> is_array($s_LocationState))
> {
> foreach ($s_LocationState as $key => $value) {
> if ($Proj2 != "") $Proj2 = $Proj2.",";
> $Proj2 = $Proj2."'".$value."'";
> }
> }
> //Parsing file array
> if (count($s_TaxTerm) > 0 AND is_array($s_TaxTerm)) {
> foreach ($s_TaxTerm as $key => $value) {
> if ($Proj3 != "") $Proj3 = $Proj3.",";
> $Proj3 = $Proj3."'".$value."'";
> }
> }
These two blocks are similar for the other two parameters, $s_LocationState
and $s_TaxTerm
> Here is what will be the death of me :)
> First the where condition below is being appended to
> anoher file in my main sql statement (don't ask:))
> CC keeps their code in various sections and files.
> Anyway , if you remember yesterday, it was determined
> that I needed the where condition to be dynamically
> created based on the user input.
> Obviously the code below does nothing of that sort.
Obviously it does, using the lists of values built above.
> So as a start, I'm trying to figure out what I can do
> with this section here (since this is really what
> needs to be changed, I think) to make it all right.
>
> if ($Proj1)
> $VendorJobs->ds->SQL.= " AND (`VendorJobs`.Industry IN (".$Proj1."))";
Plug the value for $Proj1 built above into this, and again you have
something that may look very familiar to you. It's the very same IN clause
I was urging you to use yesterday! ;)
> if ($Proj2)
> $VendorJobs->ds->SQL.= " AND
> (`VendorJobs`.LocationState IN (".$Proj2."))";
> if ($Proj3)
> $VendorJobs->ds->SQL.= " AND (VendorJobs.TaxTerm IN (".$Proj3."))";
> echo "SQL:". $VendorJobs->ds->SQL."";
So you now have a dynamically built portion of SQL, in $VendorJobs->ds->SQL,
that has a clause like "AND x IN ('a','b','c')" for each input field that
has any values set. According to your specs, this could be anywhere up to
six separate clauses. This seems to be exactly what you wanted, so your
code looks good to go.
To help you understand exactly what's going on here, or in any script or
program you're struggling to interpret, I would recommend two time-honoured
techniques:
(i) Dry running: get out your genuine old-fashioned pen and paper, pretend
you're the PHP interpreter processing this program, and write down the value
of each variable as you work through the code. Yes, it can be quite
long-winded and tedious, but you really get a tremendous feel for exactly
what's going on. The number of times I've done this and cursed myself as
I've watched a value going out of range, or not getting set at all, I
couldn't begin to count.
(ii) As a variation of the above, put *lots* of debugging echos in your
script: echo the value of every variable frequently and redundantly -- it
can help sometimes just to have the reassurance that a value really hasn't
changed, even though you know it absolutely shouldn't!! Again, if you're
struggling with how a script operates this can help you see how values are
built up, and can often show you exactly where a wrong value gets calculated
(and how, and maybe even why). It's especially important to echo your final
complete SQL statement just before it's executed, so that if it produces an
error message you've got the actual relevant SQL right in front of you.
Hope this helps.
Cheers!
Mike
-
Mike Ford, Electronic Information Services Adviser,
Learning Support Services, Learning & Information Services,
JG125, James Graham Building, Leeds Metropolitan University,
Headingley Campus, LEEDS, LS6 3QS, United Kingdom
Email: [EMAIL PROTECTED]
Tel: +44 113 283 2600 extn 4730 Fax: +44 113 283 3211
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MultSelect ListBox hell!
Well don't hate me for coming back here. I am shamed
and embarassed enough.
And while I appreciate all the great code and feedback
received , I've run into a glitch.
(oi) Because I'm using CodeCharge, it's not easy to
just insert custom code anywhere. That is one of
those code generators. I suppose if I had more hands
on php coding the way could be found.
So here is what I have:
//Here is defines the field variable and uses
//CodeCharge function to grab the variable
$s_Industry = CCGetParam("s_Industry", "");
$s_LocationState = CCGetParam("s_LocationState", "");
$s_TaxTerm = CCGetParam("s_TaxTerm", "");
//Here is the parsing of the array. I'm not sure
what the $proj variable is for. I gather it's a
holder for the array values
if (count($s_Industry) > 0 AND is_array($s_Industry))
{
foreach ($s_Industry as $key => $value) {
if ($Proj1 != "") $Proj1 = $Proj1.",";
$Proj1 = $Proj1."'".$value."'";
}
}
//Parsing next array
if (count($s_LocationState) > 0 AND
is_array($s_LocationState))
{
foreach ($s_LocationState as $key => $value) {
if ($Proj2 != "") $Proj2 = $Proj2.",";
$Proj2 = $Proj2."'".$value."'";
}
}
//Parsing file array
if (count($s_TaxTerm) > 0 AND is_array($s_TaxTerm))
{
foreach ($s_TaxTerm as $key => $value) {
if ($Proj3 != "") $Proj3 = $Proj3.",";
$Proj3 = $Proj3."'".$value."'";
}
}
Here is what will be the death of me :)
First the where condition below is being appended to
anoher file in my main sql statement (don't ask:))
CC keeps their code in various sections and files.
Anyway , if you remember yesterday, it was determined
that I needed the where condition to be dynamically
created based on the user input.
Obviously the code below does nothing of that sort.
So as a start, I'm trying to figure out what I can do
with this section here (since this is really what
needs to be changed, I think) to make it all right.
if ($Proj1)
$VendorJobs->ds->SQL.= " AND (`VendorJobs`.Industry IN
(".$Proj1."))";
if ($Proj2)
$VendorJobs->ds->SQL.= " AND
(`VendorJobs`.LocationState IN (".$Proj2."))";
if ($Proj3)
$VendorJobs->ds->SQL.= " AND (VendorJobs.TaxTerm IN
(".$Proj3."))";
echo "SQL:". $VendorJobs->ds->SQL."";
As a crash course on this issue, search form has 6
fields, user may enter 1 or up to all 6 values, but
the results should be refined as more values are
added. In other words AND is correct conditional ,
but has to allow for user to choose how many values
they want to put in.
Anyway , don't throw me off the list yet! I am reading
on PHP, but I have gone pretty far on this project and
prefer to finish it without diverting back/
Thank you,
Stuart
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MultSelect ListBox hell!
On 22 September 2004 18:45, Stuart Felenstein wrote:
> Just to confirm,
> This is what I'm going to start with:
Yeah, I'd say you've pretty much got it, except...
> //base sql statement
> $sql = "select * from jobs where record_deleted = 'NO' ";
>
> if (isset($_POST['states'])){
Your SQL is going to need some sort of conjunction here, as your WHERE phrase already
has an initial condition in it. I'm guessing you'll want an AND, so:
$sql .= 'AND ';
> //check to see if the states is an array
> // multiple items or just one
> if (is_array($_POST['state']))
You've switched from $_POST['states'] to $_POST['state'] -- fix whichever is wrong ;)
> $sql .= "state='".implode("' OR state='",
> $_POST['state'])."'";
Given the conditions you want your WHERE phrase to test, you're going to need more
parentheses to force the ORs to be evaluated before the ANDs; this is where the IN
syntax, IMO, is more readable. So you want either:
$sql .= "(state='".implode("' OR state='",$_POST['state'])."')";
or:
$sql .= "state IN ('".implode("','",$_POST['state'])."')";
>
> }else{
> //$_POST['state'] is not an array
> $sql .= "state = '".$_POST['state']."' ";
> }//end if
>
> if (isset($_POST['job'])){
>if (isset($_POST['state'])){ $sql .= " AND "; }
And throughout this second block you've used a cut'n'paste of the first block without
altering ['state'] to ['job'] -- just the sort of oversight that can give you the
raving heebie-jeebies somewhere down the line if you fail to fix it! ;)
>
> //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['state']))
>$sql .= "state='".implode("' OR state='",
> $_POST['state'])."'";
> $sql .= ")";
> }else{
> $_POST['job'] is not an array
> $sql .= "job = '".$_POST['job']."' ";
> }
> //end if
Given the moderate complexity of this code, I'd strongly recommend echo-ing out $sql
immediately before it's used whilst you're in testing mode. When your query fails,
you'll already be one step ahead in working out what the problem is.
Cheers!
Mike
-
Mike Ford, Electronic Information Services Adviser,
Learning Support Services, Learning & Information Services,
JG125, James Graham Building, Leeds Metropolitan University,
Headingley Campus, LEEDS, LS6 3QS, United Kingdom
Email: [EMAIL PROTECTED]
Tel: +44 113 283 2600 extn 4730 Fax: +44 113 283 3211
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MultSelect ListBox hell!
Just to confirm,
This is what I'm going to start with:
//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 .= "state='".implode("' OR state='",
$_POST['state'])."'";
}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['state']))
$sql .= "state='".implode("' OR state='",
$_POST['state'])."'";
$sql .= ")";
}else{
$_POST['job'] is not an array
$sql .= "job = '".$_POST['job']."' ";
}
//end if
?>
--- "Ford, Mike" <[EMAIL PROTECTED]> wrote:
> > -Original Message-
> > From: Stuart Felenstein
> [mailto:[EMAIL PROTECTED]
> > Sent: 22 September 2004 16:45
> >
> > --- "Ford, Mike" <[EMAIL PROTECTED]> wrote:
> >
> > > >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 .= ")";
> > >
> > > PHP has the very nice implode function to make
> this
> > > much easier:
> > >
> > > $sql = "state='".implode(' OR state=',
> $_POST['state'])."'";
> > >
> > > (If your database supports the IN operator, this
> is
> > > probably even better:
> > > $sql = "state IN ('".implode("','",
> $_POST['state'])."')"; )
> >
> > Sorry, cause I know this is probably a stupid
> > question, but what block of code does the implode
> > statement replace ?
>
> Everything inside the if(is_array()). Ummm, that
> should probably still be a
> .= operator, then. There's also some quotes missing
> in the original
> version, so:
>
> if (is_array($_POST['state']))
>$sql .= "state='".implode("' OR state='",
> $_POST['state'])."'";
>
> Cheers!
>
> Mike
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MultSelect ListBox hell!
> -Original Message-
> From: Stuart Felenstein [mailto:[EMAIL PROTECTED]
> Sent: 22 September 2004 16:45
>
> --- "Ford, Mike" <[EMAIL PROTECTED]> wrote:
>
> > >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 .= ")";
> >
> > PHP has the very nice implode function to make this
> > much easier:
> >
> > $sql = "state='".implode(' OR state=', $_POST['state'])."'";
> >
> > (If your database supports the IN operator, this is
> > probably even better:
> > $sql = "state IN ('".implode("','", $_POST['state'])."')"; )
>
> Sorry, cause I know this is probably a stupid
> question, but what block of code does the implode
> statement replace ?
Everything inside the if(is_array()). Ummm, that should probably still be a
.= operator, then. There's also some quotes missing in the original
version, so:
if (is_array($_POST['state']))
$sql .= "state='".implode("' OR state='", $_POST['state'])."'";
Cheers!
Mike
-
Mike Ford, Electronic Information Services Adviser,
Learning Support Services, Learning & Information Services, JG125, James
Graham Building, Leeds Metropolitan University, Headingley Campus, LEEDS,
LS6 3QS, United Kingdom
Email: [EMAIL PROTECTED]
Tel: +44 113 283 2600 extn 4730 Fax: +44 113 283 3211
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MultSelect ListBox hell!
> -Original Message-
> From: John Holmes [mailto:[EMAIL PROTECTED]
> Sent: 22 September 2004 16:39
>
> From: "Ford, Mike" <[EMAIL PROTECTED]>
> >>if (is_array($_POST['state'])){
> >
> > This check isn't really necessary in PHP, since
> $_POST['state'] will
> > *always* be an array if the form field has NAME='state[]', even if
> > only 1 is selected.
>
> But remember that the form comes from the client. Just
> because you create
> the form with "state[]", that doesn't mean I'm going to send
> it that way. ;)
Yeah, true -- I have a very bad tendency to forget about security considerations like
that until someone reminds me (often a posting on this list does it ;). Just because
I have a well-defined set of well-behaved users...!!
Cheers!
Mike
-
Mike Ford, Electronic Information Services Adviser,
Learning Support Services, Learning & Information Services, JG125, James Graham
Building, Leeds Metropolitan University, Headingley Campus, LEEDS, LS6 3QS, United
Kingdom
Email: [EMAIL PROTECTED]
Tel: +44 113 283 2600 extn 4730 Fax: +44 113 283 3211
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MultSelect ListBox hell!
See mid post:
--- "Ford, Mike" <[EMAIL PROTECTED]> wrote:
> >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 .= ")";
>
> PHP has the very nice implode function to make this
> much easier:
>
> $sql = "state='".implode(' OR state=',
> $_POST['state'])."'";
>
> (If your database supports the IN operator, this is
> probably even better:
> $sql = "state IN ('".implode("','",
> $_POST['state'])."')"; )
Sorry, cause I know this is probably a stupid
question, but what block of code does the implode
statement replace ?
Stuart
> > }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
>
> Ditto for the job field.
>
> Cheers!
>
> Mike
>
>
-
> Mike Ford, Electronic Information Services Adviser,
> Learning Support Services, Learning & Information
> Services, JG125, James Graham Building, Leeds
> Metropolitan University, Headingley Campus, LEEDS,
> LS6 3QS, United Kingdom
> Email: [EMAIL PROTECTED]
> Tel: +44 113 283 2600 extn 4730 Fax: +44 113
> 283 3211
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MultSelect ListBox hell!
From: "Ford, Mike" <[EMAIL PROTECTED]>
if (is_array($_POST['state'])){
This check isn't really necessary in PHP, since $_POST['state']
will *always* be an array if the form field has NAME='state[]',
even if only 1 is selected.
But remember that the form comes from the client. Just because you create
the form with "state[]", that doesn't mean I'm going to send it that way. ;)
If it was changed, it could cause an error that might expose file structure
or whatever. So the check is a good thing to ensure nothing was changed.
---John Holmes...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MultSelect ListBox hell!
> -Original Message-
> From: Bastien Koert [mailto:[EMAIL PROTECTED]
> Sent: 22 September 2004 15:27
[]
> //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
This check isn't really necessary in PHP, since $_POST['state'] will *always* be an
array if the form field has NAME='state[]', even if only 1 is selected.
>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 .= ")";
PHP has the very nice implode function to make this much easier:
$sql = "state='".implode(' OR state=', $_POST['state'])."'";
(If your database supports the IN operator, this is probably even better:
$sql = "state IN ('".implode("','", $_POST['state'])."')"; )
> }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
Ditto for the job field.
Cheers!
Mike
-
Mike Ford, Electronic Information Services Adviser,
Learning Support Services, Learning & Information Services, JG125, James Graham
Building, Leeds Metropolitan University, Headingley Campus, LEEDS, LS6 3QS, United
Kingdom
Email: [EMAIL PROTECTED]
Tel: +44 113 283 2600 extn 4730 Fax: +44 113 283 3211
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MultSelect ListBox hell!
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 <[EMAIL PROTECTED]> wrote:
> Wow! Thank you!
>
> Stuart
> --- Bastien Koert <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
> > >To: Bastien Koert <[EMAIL PROTECTED]>,
> > [EMAIL PROTECTED]
> > >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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
> > > > >To: [EMAIL PROTECTED]
> > > > >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 m
RE: [PHP-DB] MultSelect ListBox hell!
Wow! Thank you!
Stuart
--- Bastien Koert <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
> >To: Bastien Koert <[EMAIL PROTECTED]>,
> [EMAIL PROTECTED]
> >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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
> > > >To: [EMAIL PROTECTED]
> > > >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))
> >
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 <[EMAIL PROTECTED]>
To: Bastien Koert <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
> >To: [EMAIL PROTECTED]
> >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."";
> >
> >return $VendorJobs_ds_BeforeExecuteSelect;
> >
> >If you notice in the the SQL t
RE: [PHP-DB] MultSelect ListBox hell!
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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
> >To: [EMAIL PROTECTED]
> >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."";
> >
> >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
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 <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
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."";
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
Re: [PHP-DB] MultSelect ListBox hell!
The list boxes are fed by tables that have state and state ID, same for industry. So all the joins are made in the main sql statement and working correctly. --- Doug Thompson <[EMAIL PROTECTED]> wrote: > You need LocationState to be, in your example, > Alabama OR California. Also, I would write that SQL > phrase "LocationState = 'Alabama' OR > LocationState='California' ". > > Doug > > Doug > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MultSelect ListBox hell!
Stuart Felenstein wrote:
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."";
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
You need LocationState to be, in your example, Alabama OR California. Also, I would write that
SQL phrase "LocationState = 'Alabama' OR LocationState='California' ".
Doug
Doug
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MultSelect ListBox hell!
K, I'll still take responses to this , but things
seemed to have improved once I stopped using same
variable for both boxes ooops!
Stuart
--- Stuart Felenstein <[EMAIL PROTECTED]> wrote:
> 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."";
>
> 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
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
