Filtering Out MySQL Results Using Drop-Down Menu

tycragg

Hi, I'm looking to filter out my results from a MySQL database table using a drop-down menu. Here is my code:

"shows.html"

<form method="post" action="shows.php">
  <label>
  <select name="bandselection" id="bandselection">
    <option value="BandA" selected="selected">BandA</option>
    <option value="BandB">BandB</option>
    <option value="BandC">BandC</option>
    <option value="BandD">BandD</option>
  </select>
  </label>
  <label>
  <input type="submit" name="Submit" value="Submit" />
  </label>
</form>

"shows.php"

<?
$host="***";
$username="***";
$password="***";
$database="***";

$bandselection = $_POST['bandselection'];

mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM shows WHERE `date` >= CURDATE() ORDER by `date` ASC WHERE `band` = '" . $bandselection ."'";
$result=mysql_query($query);
$num=mysql_numrows($result);


mysql_close();

$i=0;
while ($i < $num) {
$date=mysql_result($result,$i,"date");
$band=mysql_result($result,$i,"band");
$venue=mysql_result($result,$i,"venue");
$time=mysql_result($result,$i,"time");
$band1=mysql_result($result,$i,"band1");
$band2=mysql_result($result,$i,"band2");
$band3=mysql_result($result,$i,"band3");

echo "<b><font size=2>$date: $band</font></b><font size=1>  @ $venue, $time<br>w/ $band1, $band2<br></font><br>";
$i++;
}
?>

I get the error message: "
Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in /***/shows.php on line 11"

Can anyone help me to see what's wrong?

tron00

It should be

mysql_num_rows()

not

mysql_numrows()

tycragg

tron00 wrote:
It should be
mysql_num_rows()
not
mysql_numrows()

I still get the same eroor message:

"Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /***/shows.php on line 11".

Could it be line 9 that's causing the problem?

krotkruton

That might not be the cause of your problem, but it's definitely a problem. It should be either of the following:

<?php
//option 1:
$bandselection .= "'";
//option 2:
$bandselection = $bandselection ."'";
?>

It's the same as doing "$index += 4" or "$index = $index + 4".

Don't forget to mark your thread as resolved if your question has been answered.

tycragg

krotkruton wrote:
That might not be the cause of your problem, but it's definitely a problem. It should be either of the following:
<?php
//option 1:
$bandselection .= "'";
//option 2:
$bandselection = $bandselection ."'";
?>
It's the same as doing "$index += 4" or "$index = $index + 4".

Don't forget to mark your thread as resolved if your question has been answered.

So are you saying that line should read:

$query="SELECT * FROM shows WHERE `date` >= CURDATE() ORDER by `date` ASC WHERE `band` = '" $bandselection .= "'";

krotkruton

Nope, I misread that line. I thought it was the start of a new line, but it's ok.

Try this:

$conn = mysql_connect($host,$username,$password) or die("Could not connect:" . mysql_error());
$db = mysql_select_db("$database", $conn) or die("Unable to select database" . mysql_error());
$query="SELECT * FROM shows WHERE `date` >= CURDATE() ORDER by `date` ASC WHERE `band` = '" . $bandselection ."'";
$result=mysql_query($query);
$num=mysql_num_rows($result);

I haven't tested it so I'm not completely sure, but I think you're using mysql_select_db incorrectly. See if that works better for you.

bogu

Always use or die after you send your query so that your script will stop if you query has syntax error ...
Change this line

$result=mysql_query($query);

to this

$result=mysql_query($query) or die(mysql_error());

Where did you read that SELECT syntax with two WHERE condition or WHERE condition before ORDER BY?

Here is the SELECT syntax and look over your select ...

tycragg

Ok, it works when there's only one WHERE. Is there any way I can have both of them?

bogu

tycragg wrote:
Ok, it works when there's only one WHERE. Is there any way I can have both of them?

Why do you need both of them?

"SELECT * FROM shows WHERE `date` >= CURDATE() AND `band` = '{$bandselection}' ORDER by `date` ASC"

tycragg

bogu wrote:

Why do you need both of them?

"SELECT * FROM shows WHERE `date` >= CURDATE() AND `band` = '{$bandselection}' ORDER by `date` ASC"

Awesome, thanks!

Now, one more thing... Whenever I make a selection from the dropdown menu, it displays the correct results, but the dropdown menu returns to the initially selected list value. Would there be any way to display the value I've selected after it fetches the MySQL results?

bogu

But you have the value in this var $bandselection...

tycragg

bogu wrote:
But you have the value in this var $bandselection...

Yeah, ok. How would I then get that $bandselection to be the selected value in the drop-down box after the SQL query?

bogu

tycragg wrote:
Yeah, ok. How would I then get that $bandselection to be the selected value in the drop-down box after the SQL query?

Create an array with all the list-box options, generate the list-box option by parsing the array.
When you parse the array to generate the list-box if the option equal var $bandselection then add the selected properties to that option ...

Something like that, I hope you understand what I`m telling you ...