[RESOLVED] Help with retrieving and displaying from a form selection and mySQL

[redfraggle]

Good morning all. =) I hope everyone's day is going well. I am working on a pet project and have run into a wall. I was hoping someone here could help me out or point me in the right direction. =)

What I am doing:

Basically I am trying to have a select box that pulls my product categories from the database. When the user selects one of the categories from the select box, the products in that category will display in a table below it. Ultimately I would also like to have a menu that does this, but first steps first.

There is only one table in the database called products. the structure is thus:

product_id, product_category, product_name, product_short_desc, product_price

I am looking to pull the category for the drop down box and the rest for the table.

Here is my code so far. I was following a couple of other threads from other forums, so I am afraid I made quite a mess of this.

<form action="category_filter.php" method="post">
<input type="select" name="category">

<?php
// Open connection with the MySQL database
$link = mysql_connect("localhost","user","password");

mysql_select_db("database");

$category = $_POST['category'];
   $select1 = "SELECT * FROM products WHERE product_category = '$category'";  

   $result1 = mysql_query($link, $select1);

// loop through to bring up all the database locations
 while ($row = mysql_fetch_array($result1)) {
     $category = $row['product_category'];
	echo "<option>".$category."</option>";
}
?>

<?php
 $input = $_POST['category'];
?>

<!--Set up a table-->
<table border="1" cellpadding="5">
 <tr>
  <th>Product Thumbnail</th>
  <th>Product ID</th>
  <th>Product Name</th>
  <th>Product Short Description</th>
  <th>Product Price</th>
 </tr>
<?php
// build and execute the query
$select = "SELECT product_thumbnail, product_id, product_name, product_short_desc, product_price FROM products WHERE product_category=\"$input\""; 
$result = mysql_query($select);

//loop through the results 
while ($row = mysql_fetch_array($result)) {
//get each element and put it in a variable
$id = $row['product_id'];
$thumnail = $row['product_thumbnail'];
$name = $row['product_name'];
$short_desc = $row['product_short_desc'];
$price = $row['product_price'];

//Print out the results
echo <<<END
 <TR>
  <TD>$id</TD>
  <TD>$thumbnail</TD>
  <TD>$name</TD>
  <TD>$short_desc</TD>
  <TD>$price</TD>
 </TR>
END;
}
?>
<!--End the Table-->
</TABLE>

<php
 mysql_close($link);
?>

Have a wonderful day all. =)

Kate

[redfraggle]

These are the two current errors I am getting

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in C:\inetpub\wwwroot\donkey\category_filter_test.php on line 14

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\inetpub\wwwroot\donkey\category_filter_test.php on line 17

line 14 is $result1 = mysql_query($link, $select1);

and 17 is while ($row = mysql_fetch_array($result1)) {

[johanafm]

Read the php documentation for mysql_query and look at the argument it takes, and the order those argument should be in, and compare that to the arguments you provide.
Moreover, you do not check that $link != false after mysql_connect(), which would be true if you fail to connect for some reason (database no up and running, wrong name/pass etc) and you also don't check that $result != false which it would be if you supply an invalid SQL statement etc.
And finally executing a query by directly inserting user supplied data makes you vulnerable to "sql injection" which is the same as saying they can execute any query they like and possible get better user access, drop users table etc.
When dealing with input data, always (very very always!)

# if it's a string, after you have established your database connetio
$str = mysql_real_escape_string($_POST['string_data']);

# if it's an integer, cast it to int
$int = (int) $_POST['int'];

# if it's a float, cast it to float
$float = (float) $_POST['float'];

[redfraggle]

Thanks for pointing me in the right direction! =)

Kate