From: <dpgirago@mdanderson.org>
> 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:
<input type="checkbox" name="artist[]" value="Hendrix">
<input type="checkbox" name="artist[]" value="Violent Femmes">
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
