Forms - code for mysql to AND each option

how to code for mulitple selects...?

[omfLondon]

Hi there,

I have a form with 3 parts to it - I just don't know how to properly code the sql bit. there are 2 drop downs and 1 checkbox that user can choose (these are to search database using ANDs for each option)
I have just tried to query select for the first 2 dropdowns but can't get the result - as its from the same table should an AND not suffice? Sorry; totally lost with these!

<form action="TCTest5a.php" method="post">
<p>
<select name="releaseyear" size="1">
<option>1999</option>
<option>2003</option>
<option>2006</option>
</select>
<select name="price" size="1">
<option>1.99 - 4.99</option>
<option>5.00 - 9.99</option>
<option>10.00 - 19.99</option>
<option>20.00 - 29.99</option>
<option>30.00 - 39.99</option>
</select>
<input type="submit" />
</p>
<p>
Price of Film </p>
film type Comedy
<input type="checkbox" value="1" name="type[]" />
Thriller
<input type="checkbox" value="2" name="type[]" />
Drama
<input type="checkbox" value="3" name="type[]" />
Action
<input type="checkbox" value="4" name="type[]" />
<input type="submit" name="submit" />
</form>

<?php
$link = mysql_connect("localhost","","");
mysql_select_db("db1", $link) or die("Unable to select database");
#
$selectSql = "SELECT * FROM music WHERE releaseyear = '".mysql_real_escape_string($_POST['releaseyear'])."'
AND price = '".mysql_real_escape_string($_POST['price'])."";
$result = mysql_query($selectSql, $link) or die(mysql_error());
while($row = mysql_fetch_assoc($result)) {
echo '<pre>';
print_r($row);
echo '</pre>';
}
mysql_close($link);
?>

[joelgreen]

print sql query before executing. Does it print what you expect?

It looks like you missed closing quote after the price: mysql_real_escape_string($_POST['price'])."'";

$selectSql = "SELECT * FROM music WHERE releaseyear = '".mysql_real_escape_string($_POST['releaseyear'])."'
AND price = '".mysql_real_escape_string($_POST['price'])."'";
echo $selectSql;
$result = mysql_query($selectSql, $link) or die(mysql_error());

[omfLondon]

Hi there, thanks for that; yes had missed a quote.

However, the output:

SELECT * FROM music WHERE releaseyear = '2003' AND price = '5.00 - 9.99'

But it hasn't actually queried the database & given results back from it.... e.g. it hasn#t given me title of CD, ...... what next? Many thanks

[joelgreen]

Try adding following code just after the <?php, and run again

ini_set('error_reporting', E_ALL);
ini_set('display_errors', 1);

Does it show any errors?

[omfLondon]

Hi,

No, doesn#t show any errors just this:

SELECT * FROM music WHERE releaseyear = '1999' AND price = '1.99 - 4.99'

I thought it might be 'cos I didnt set the price up properly (integers..) but even when I use another detail form the table it comes up with the above message just diff detail....

If I just use the releaseyear dropdown and submit - it shows details of that array.... but as I need to use several options....

Thanks

[omfLondon]

Hi, i've redone the php:

<?php
ini_set('error_reporting', E_ALL);
ini_set('display_errors', 1);

$link = mysql_connect("localhost","","");
mysql_select_db("db1", $link) or die("Unable to select database");

$selectSql = "SELECT * FROM music WHERE releaseyear = '".mysql_real_escape_string($_POST['releaseyear'])."'
AND price = '".mysql_real_escape_string($_POST['price'])."'";
echo $selectSql;
$result = mysql_query($selectSql, $link) or die(mysql_error());

if (($result)¦¦(mysql_errno == 0))
{
echo "<table width='100%'><tr>";
if (mysql_num_rows($result)>0)
{
//loop thru the field names to print the correct headers
$i = 0;
while ($i < mysql_num_fields($result))
{
echo "<th>". mysql_field_name($result, $i) . "</th>";
$i++;
}
echo "</tr>";

//display the data
while ($rows = mysql_fetch_array($result,MYSQL_ASSOC))
{
echo "<tr>";
foreach ($rows as $data)
{
echo "<td align='center'>". $data . "</td>";
}
}
}else{
echo "<tr><td colspan='" . ($i+1) . "'>No Results found!</td></tr>";
}
echo "</table>";
}else{
echo "Error in running query :". mysql_error();
}
?>

++++++++++++++++RESULT ON-SCREEN IS;

SELECT * FROM music WHERE releaseyear = '1999' AND price = '8.40'
Notice: Undefined variable: i in C:\Ecommerce\www\TCTest5a.php on line 38

No Results found!
mysql_close($link);?>

[joelgreen]

Did you try running this query in PhpMyAdmin? Does it show some results?

SELECT * FROM music WHERE releaseyear = '1999' AND price = '8.40'

BTW, where did it get 8.40 if your select named "price" does not have such option? Did you change it to <input> field?

[omfLondon]

Hi, have tried running it in myphpadmin and results are:
-----------------------------------------------------------
Notice: Undefined index: releaseyear in C:\Ecommerce\www\TCTest5a.php on line 9

Notice: Undefined index: price in C:\Ecommerce\www\TCTest5a.php on line 10
SELECT * FROM film WHERE releaseyear = '' AND price = ''
Notice: Undefined variable: i in C:\Ecommerce\www\TCTest5a.php on line 38

No Results found!
mysql_close($link);?>
--------------------------------------------------------------

Eh, not sure what I did with the price - I had a selection of prices to choose from Dropdwn option - I picked 1999 and 8.40 as I knew there was an entry in the database for this one; but I'm not sure if I have that one right: here's the form:

<form action="TCTest5a.php" method="post">
<p>
<select name="releaseyear" size="1">
<option>1999</option>
<option>2003</option>
<option>2006</option>
</select>
<select name="price" size="1">
<option>*</option>
<option>1.99 - 4.99</option>
<option>18.00</option>
<option>8.40</option>
<option>20.00 - 29.99</option>
<option>30.00 - 39.99</option>
</select>
<input type="submit" />