[PHP-DB] Query Filter in GUI
Good Morning PHP-DB Listers,
I have a PHP GUI in which a select box gets populated by a query of a
MySQL DB for all CD's by all artists when the page is first opened. I am
now trying to implement
the same query but with a WHERE clause that filters the returned CD's to
be displayed in the same select box according to the artist selected via a
group of checkboxes (one per artist). The problem so far is that if I try
to implement some flow control (if checbox one is selected, append "where
artist='hendrix' to the select all CDs query, mysql_query() etc...), I get
an error message saying that the query string is empty. So I am looking
for some advice about how to implement the logic of having the same select
box display the results of different but related queries.
For example,
$query_1 = "SELECT CD_title FROM CDs order by CD_title";
$result_1 = mysql_query($query_1) or die("Can't select CD_title from CDs"
. mysql_error());
while($row=mysql_fetch_array($result_1, MYSQL_BOTH)) ..etc...
works just fine.
But if I try to use some flow control to alter which query statement is
assigned to $query_1...
if(checkbox one is selected)
{
$query_1 = "SELECT CD_title FROM CDs WHERE artist_name = 'Hendrix'
order by CD_title";
}
elseif(no checkboxes are checked)
{
$query_1 = "SELECT CD_title FROM CDs order by CD_title";
}
$result_1 = mysql_query($query_1) or die("Can't select CD_title from CDs"
. mysql_error());
while($row=mysql_fetch_array($result_1, MYSQL_BOTH)) ..etc...
produces the error message stated above ... that the query string is
empty. I've also tried to duplicate the "mysql_query($query_1) or
die()..." within each section of the IF group, but then the select box
does not get populated at all.
I am using session variables, and I'm certain that the checkboxes are
correctly assigning the necessary values to the variables associated with
them because I am echo'ing the variables and I can see the values that
they have.
And when I run the query with the where clause in the MySQL client, it
works just fine.
I'm guessing I'm missing something fairly simple because this type of
functionality must have been implemented before.
Thanks for reading the rather long question.
David
[PHP-DB] Query FIlter in GUI
Thank you CPT John W. Holmes. I'll give it a try. David
RE: [PHP-DB] Query Filter in GUI
how are you testing the check boxes?, looks like the the query string is empty because
both the tests you are doing in the code below return false.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 22 September 2003 15:01
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Query Filter in GUI
Good Morning PHP-DB Listers,
I have a PHP GUI in which a select box gets populated by a query of a
MySQL DB for all CD's by all artists when the page is first opened. I am
now trying to implement
the same query but with a WHERE clause that filters the returned CD's to
be displayed in the same select box according to the artist selected via a
group of checkboxes (one per artist). The problem so far is that if I try
to implement some flow control (if checbox one is selected, append "where
artist='hendrix' to the select all CDs query, mysql_query() etc...), I get
an error message saying that the query string is empty. So I am looking
for some advice about how to implement the logic of having the same select
box display the results of different but related queries.
For example,
$query_1 = "SELECT CD_title FROM CDs order by CD_title";
$result_1 = mysql_query($query_1) or die("Can't select CD_title from CDs"
. mysql_error());
while($row=mysql_fetch_array($result_1, MYSQL_BOTH)) ..etc...
works just fine.
But if I try to use some flow control to alter which query statement is
assigned to $query_1...
if(checkbox one is selected)
{
$query_1 = "SELECT CD_title FROM CDs WHERE artist_name = 'Hendrix'
order by CD_title";
}
elseif(no checkboxes are checked)
{
$query_1 = "SELECT CD_title FROM CDs order by CD_title";
}
$result_1 = mysql_query($query_1) or die("Can't select CD_title from CDs"
. mysql_error());
while($row=mysql_fetch_array($result_1, MYSQL_BOTH)) ..etc...
produces the error message stated above ... that the query string is
empty. I've also tried to duplicate the "mysql_query($query_1) or
die()..." within each section of the IF group, but then the select box
does not get populated at all.
I am using session variables, and I'm certain that the checkboxes are
correctly assigning the necessary values to the variables associated with
them because I am echo'ing the variables and I can see the values that
they have.
And when I run the query with the where clause in the MySQL client, it
works just fine.
I'm guessing I'm missing something fairly simple because this type of
functionality must have been implemented before.
Thanks for reading the rather long question.
David
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query Filter in GUI
From: <[EMAIL PROTECTED]>
> I have a PHP GUI in which a select box gets populated by a query of a
> MySQL DB for all CD's by all artists when the page is first opened. I am
> now trying to implement
> the same query but with a WHERE clause that filters the returned CD's to
> be displayed in the same select box according to the artist selected via a
> group of checkboxes (one per artist). The problem so far is that if I try
> to implement some flow control (if checbox one is selected, append "where
> artist='hendrix' to the select all CDs query, mysql_query() etc...), I get
> an error message saying that the query string is empty. So I am looking
> for some advice about how to implement the logic of having the same select
> box display the results of different but related queries.
>
> For example,
>
> $query_1 = "SELECT CD_title FROM CDs order by CD_title";
> $result_1 = mysql_query($query_1) or die("Can't select CD_title from CDs"
> . mysql_error());
> while($row=mysql_fetch_array($result_1, MYSQL_BOTH)) ..etc...
>
> works just fine.
>
> But if I try to use some flow control to alter which query statement is
> assigned to $query_1...
>
> if(checkbox one is selected)
> {
> $query_1 = "SELECT CD_title FROM CDs WHERE artist_name = 'Hendrix'
> order by CD_title";
> }
>
> elseif(no checkboxes are checked)
> {
> $query_1 = "SELECT CD_title FROM CDs order by CD_title";
> }
>
> $result_1 = mysql_query($query_1) or die("Can't select CD_title from CDs"
> . mysql_error());
> while($row=mysql_fetch_array($result_1, MYSQL_BOTH)) ..etc...
>
> produces the error message stated above ... that the query string is
> empty. I've also tried to duplicate the "mysql_query($query_1) or
> die()..." within each section of the IF group, but then the select box
> does not get populated at all.
In order for you to get that error, neither of your two conditions are TRUE.
Since you have pseudo-code, it's hard to tell where the problem is,
though.Something in (checkbox one is selected) and (no checkboxes are
checked) is not right.
Here's an easy way to do this, though, so pay attention. :)
Create your checkboxes like this:
etc...
Notice how they are all named the same and have different value.
Now, when processing this form, you'll have a variable $_GET['artist']
that's an array. If a checkbox was checked, then the array will have some
elements, otherwise it'll be empty and not set... so use something like
this:
if(isset($_GET['artist']) && is_array($_GET['artist']) &&
!empty($_GET['artist']))
{
$artist_list = "'" . implode("','",$_GET['artist']) . "'";
$query = "SELECT CD_title FROM CDs WHERE artist_name IN ($artist_list)
order by CD_title";
}
else
{
$query = "SELECT CD_title FROM CDs order by CD_title";
}
Then run your query and retrieve the results like you are doing now. What
this will to is take multiple checkboxes selected by the user (or just one
if you want) and make it into a comma separated list to send to the query.
So if two checkboxes are selected, you end up with a query such as:
WHERE artist_name IN ('Hendrix','Violent Femmes')
Hope that helps.
---John Holmes...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
