[Resolved] Filtering my database with a dropdown menu.. ???

[MichaelHe]

Hi..
I am new i PHP programming and I'm stuck in a filter system script

I have a simpel database with some mucisians.. It has 3 fields, Name, Category (solo, duo, trio) and Location (Denmark, Sweden, Norway)...

What I want is a site where you see all the mucisians in a list.
Above the list I need 2 dropdown menu's with the category and the location.. If you choose 'Duo' in the category the list below should automaticly show the Duo's only... Without having to click on any other buttons.. only by clicking Duo in the dropdown menu..
If you then choose Denmark in the Location menu it should list duo's in Denmark.. And if you then set the Category menu to 'not choasen' it should list all musicians from Denmark... and so on..

I have the table list which looks like this:
<table width="500" border="0">
<tr>
<td width='200'><?echo $row["Name"]."</td>
<td width='100'>".$row["Category"]."</td>
<td width='200'>".$row["Location"]."</td>
</tr>
</table>";

And I have connected the the database like this:
<?php
$conn = mysql_connect("localhost", "my username", "my password");
mysql_select_db("my database");
?>

This works fine with a simepl
$result = mysql_query("select * from Music");
while ($row = mysql_fetch_array($result)) {

But I can't get the menu's to work... I have tried finding scripts, but I can't get it to work..
If you have any good ideas or already have a working script doing this I'm very interested in hearing from you..

Thanks in advance
Michael

[hmemnon]

<?
if($_POST){
 $loc=$_POST['location']; //your autosubmitted var
 $cat=$_POST['category']; //same hre 
$query=mysql_query("SELECT * FROM table_name WHERE category='$cat' AND location='$loc'");
}
?>




<select name=location onChange=this.form.submit();>
<option value="Denmark" <?=($_POST['location']=="Denmark")?"selected":""?>>Denmark</option>
<?//repeat option for other values?>
</select>
<? //same procedure for category?>

Cheers

[MichaelHe]

Thank you very very much... It seems to be working..

I have only one question:

When i run the site, it starts by choosing one musician in the list only... I have one test musician in my database where location and category is empty...
When it is loaded it should list all the musicians...

It seems as filter is set to "" in both filters to start with, so it only shows the musicians where location and category is "" instead of showing them all to begin with..

Have to solve this ??

And how do I put of a filter again, after it has been selected for the first time.. ??

Besides these two isuies it work perfectly.. :o)

ones again thank for your help

Michael

[hmemnon]

Try this query instead then

$newquery="SELECT * FROM table_name WHERE 1".($cat!="")?" AND category='$cat'":"".($loc!="")?" AND location='$loc'":"".";

For the second question, just add a new option do the box width a "" value and it will override that particular filter

Cheers

[MichaelHe]

No...

My codes are like this now..:

<form method="post" action='<?=$_SERVER[PHP_SELF]?>'>

<select name=category onChange=this.form.submit();>
<option value="Solo" <?=($POST['category']=="Solo")?"selected":""?>>Solo</option>
<option value="Duo" <?=($POST['category']=="Duo")?"selected":""?>>Duo</option>
</select>

<select name=location onChange=this.form.submit();>
<option value="Denmark" <?=($POST['location']=="Denmark")?"selected":""?>>Denmark</option>
<option value="Sweden" <?=($POST['location']=="Sweden")?"selected":""?>>Sweden</option>
</select>

</form>

<?
if($POST){
$cat=$POST['category'];
$loc=$_POST['location'];
}

$result = mysql_query("SELECT * FROM Music WHERE 1".($cat!="")?" AND category='$cat'":"".($loc!="")?" AND location='$loc'":""."; ---- isn't there missing a ) in the end here... I have tried typing that too but it didn't help the problem ----
{
while ($row = mysql_fetch_array($result))

and then my tables...

It now says :
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /customers/enjoymusic.dk/enjoymusic.dk/httpd.www/Filterform.php on line 39

line 39 is the while ($row..... line.

One again thank you for helping me... I have really spend a lot of time on this project without luck..

[hmemnon]

My bad there...
For the sake of readibility:

$opt1=($cat!="")?" AND category!='$cat'":""; //check if empty
$opt2=($loc!="")?" AND location!='$loc'":"";  //same here

$newquery="SELECT * FROM table_name WHERE 1 $opt1 $opt2";// use them in our query

Now it should work... If not post here again and this time include query echoing and such so i can know what's wrong...

Cheers

PS - Tired 😛, so excuse some coding errors

[MichaelHe]

OK...
The first codes where a test... Now I have written it for the real use..
It is a database of songs and composers...

I have 7 songs in the sql database for test right now...
Komponist means composer
Stil means style
and Titel means Title

Right now, as you can see here http://www.enjoymusic.dk/Filterform.php
it filters the name i choose of the list.. It was supposed to list only the name I choose... So when I go to the site both the drop down menu's should be at Not Chosen (these are not made yet, since I don't know how) and all the 7 songs should be listed.. Then you should be able to choose Rock in the second menu, and it should list all the Rock songs only... If if there are still many songs, you should be able to choose a composer in the first menu, so it only shows Rock songs by this composer... And off cause you should be able to switch off one of the filters again...

Here are my codes, and I really really apresiate you help...

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">

<?php
$conn = mysql_connect("localhost", "My udername", "My password");
mysql_select_db("enjoymusic_dk");
?>

<form method="post" action='<?=$_SERVER[PHP_SELF]?>'>

<select name=Komponist onChange=this.form.submit();>
<option value="Michael Henriksen" <?=($POST['Komponist']=="Michael Henriksen")?"selected":""?>>Michael Henriksen</option>
<option value="Morten Andersen" <?=($POST['Komponist']=="Morten Andersen")?"selected":""?>>Morten Andersen</option>
</select>

<select name=Stil onChange=this.form.submit();>
<option value="Pop" <?=($POST['Stil']=="Pop")?"selected":""?>>Pop</option>
<option value="Rock" <?=($POST['Stil']=="Rock")?"selected":""?>>Rock</option>
</select>

</form>

<?
if($POST){
$kom=$POST['Komponist'];
$sti=$_POST['Stil'];
}

$opt1=($kom!="")?" AND Komponist!='$kom'":"";
$opt2=($Sti!="")?" AND Stil!='$sti'":"";

$result = mysql_query("SELECT * FROM Titler WHERE 1 $opt1 $opt2");
{
while ($row = mysql_fetch_array($result)) {?>

<table width="500" border="0">
<tr>
<td width='200'><?echo $row["Titel"]."</td>
<td width='200'>".$row["Komponist"]."</td>
<td width='100'>".$row["Stil"]."</td>
</tr>
</table>";
}}

mysql_close($conn);
?>
</body>
</html>

[hmemnon]

For the Not Chosen just add <option value="">Not Chosen</option> on top of each <select>
Now, can you please break the $result into $sql="query" and then $result=mysql_query($sql) and echo the $sql? So I can see what's going through the query... I don't have any chance of testing this for myself right now... Also echo the $_POST[vars] plz

Cheers

[MichaelHe]

OK..

now the source looks like this :

<?
if($POST){
$kom=$POST['Komponist'];
$sti=$_POST['Stil'];
}
$opt1=($kom!="")?" AND Komponist!='$kom'":"";
$opt2=($sti!="")?" AND Stil!='$sti'":"";

$sql = "SELECT * FROM Titler WHERE 1 $opt1 $opt2";
$result = mysql_query($sql);

echo $sql; ?><br><?
echo $kom; ?><br><?
echo $sti; ?><br><br><?

{
while ($row = mysql_fetch_array($result)) {?>

[hmemnon]

Man, Im really dumb 🙂

Change

$opt1=($kom!="")?" AND Komponist!='$kom'":"";
$opt2=($sti!="")?" AND Stil!='$sti'":"";

to

$opt1=($kom!="")?" AND Komponist=='$kom'":"";
$opt2=($sti!="")?" AND Stil=='$sti'":"";

and it iwill work...
Try it

Cheers

Ops, mistake here

change the == to =
Komponist = '$kom'" for ex

[MichaelHe]

Ups... try it now
http://www.enjoymusic.dk/Filterform.php

It writes an error now.. ?

[hmemnon]

Read above... matching error, my bad, geez Im all the way down today...

$opt1=($kom!="")?" AND Komponist='$kom'":"";
$opt2=($sti!="")?" AND Stil='$sti'":"";

[MichaelHe]

It WORK.....

Wow... you are a god...

Thank you very very very much.... If you ever write a song, you can be on my site for free :o)))

Thanks a lot..

[hmemnon]

No problem, sry for my "errors", mark the thread as solved plz

Cheers

Good luck to your project

[MichaelHe]

How do I do that.. ?

[hmemnon]

At the bottom of the posts there's a link Mark Thread Resolved, click there...

To solve this one you can also click here

Btw, I've checked your site and you have a composer and a type of song with the same name, that can lead to strange behaviors 🙂

Cheers