[RESOLVED] New Problem - Pulling data from db using a select form

redfraggle

Hi all. I posted earlier with a different problem and have revised my code to reflect his suggestions. I am running into a new problem however that Google doesn't seem to help with (as in I don't know what to search for).

I am trying to have the user be able to select a product category from a drop down list and then it will fill in the table with all of the products in that category. It seems to work up until it needs to head into the while loop to display the products.

The categories for the drop down are in one table, and there is a category field for the products in the other table as well. I made sure that the data matched as in Hoops in one is the same as the Hoops entry in the other.

<html>
<body>

<?php
// Open connection with the MySQL database
$link = mysql_connect('host','username','password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';

// make connection to the current db
$db_selected = mysql_select_db('database', $link);
if (!$db_selected) {
    die ('Can\'t use db : ' . mysql_error());
}
echo 'Connected successfully';

?>
<!-- create selection box for categories - this part seems to work -->
<form action="category_filter_test.php" method="post">
<select name="category">

<?php

$sql = "SELECT * FROM product_category ".
"ORDER BY category_name";

$rs = mysql_query($sql) or die(mysql_error());

while($row = mysql_fetch_array($rs))
{
  echo "<option value=\"".$row['category_id']."\">".$row['category_name']."</option>\n  ";
}
?>

</select>
</form>

<?php

// this creates the table data from the form selection in theory

// build and execute the query
$input = mysql_real_escape_string($_POST['category']);
$select = "SELECT * FROM products WHERE product_category = '$input'"; 
$result = mysql_query($select) or die(mysql_error());
?>

<!--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>
   <th>Add To Cart</th>
 </tr>

<?php
//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'];
$paypal = $row['product_paypal'];

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

<?php
 mysql_close($link);
?>

</body>
</html>

Thanks for your time and any pointing or help that you can give is appriciated. =)

Kate

bradgrafelman

Since there's no submit button, how are you submitting the form after you select the category?

redfraggle

D'oh. submit button it is. although is there a way to get it to do that automatically?

redfraggle

added the submit. it is still not posting to the table.

Derokorian

You would to use jquery or ajax... or maybe onchange to automatically reload the page.

anoopmail

is it getting reloaded when you press the submit button? can you post the code with submit button?

redfraggle

anoopmail;10983497 wrote:
is it getting reloaded when you press the submit button? can you post the code with submit button?

Hi. =) yup it is reloading when I hit the button, it does print the table headers, but not the data. Thanks! =) It is pulling the select box data from one table and that works fine, the product info comes from another table with matching categories.

<html>
<body>

<?php
// Open connection with the MySQL database
$link = mysql_connect('localhost','root','password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';

// make connection to the current db
$db_selected = mysql_select_db('donkey', $link);
if (!$db_selected) {
    die ('Can\'t use db : ' . mysql_error());
}
echo 'Connected successfully';

?>
<!-- create selection box for categories - this part seems to work -->
<form action="category_filter_test.php" method="get">
<select name="category">

<?php
$sql = "SELECT * FROM product_category ".
"ORDER BY category_name";

$rs = mysql_query($sql) or die(mysql_error());

while($row = mysql_fetch_array($rs))
{
  echo "<option value=\"".$row['category_id']."\">".$row['category_name']."</option>\n  ";
}
?>

</select>
<input type="Submit" name="categories" value="Show Me">
</form>

<?php

// this creates the table data from the form selection in theory

// build and execute the query

if (isset($_GET['categories'])) {

$input = mysql_real_escape_string($_GET['category']);
$select = "SELECT * FROM products WHERE product_category = '$input'"; 
$result = mysql_query($select) or die(mysql_error());

//loop through the results 
//get each element and put it in a variable

if (!$result) {

exit(mysql_error());

} else {

echo <<<END
<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>
   <th>Add To Cart</th>
 </tr>
END;

while ($row = mysql_fetch_array($result)) {
$id = $row['product_id'];
$thumnail = $row['product_thumbnail'];
$name = $row['product_name'];
$short_desc = $row['product_short_desc'];
$price = $row['product_price'];
$paypal = $row['product_paypal'];

//Print out the results
echo <<<END
 <TR>
  <TD>$id</TD>
  <TD>$thumbnail</TD>
  <TD>$name</TD>
  <TD>$short-desc</TD>
  <TD>$price</TD>
  <TD>$paypal</TD>
 </TR>
END;
}
}
}


echo "</TABLE>";


 mysql_close($link);
?>

</body>
</html>

bradgrafelman

Have you tried outputting the second SQL query to see if it looks correct? What values does the products.product_category column contain?

redfraggle

bradgrafelman;10983506 wrote:
Have you tried outputting the second SQL query to see if it looks correct? What values does the products.product_category column contain?

I tried echoing $result just now and got the query. the last part of it is product.product_category = 1 or 2 which corresponds to the ID numbers of the category_name in table product_category.

When I change the query to a join:

$select = "SELECT * FROM products, product_category WHERE products.product_category = product_category.category_name"; 
$result = mysql_query($select) or die(mysql_error());

It prints everything as expected. but when I change it back to:

$select = "SELECT * FROM products WHERE product_category = '$input'"; 
$result = mysql_query($select) or die(mysql_error());

it does nothing.

So it would seem to me that $input is not passing its info. The code for that section is this:

if (isset($_GET['categories'])) {

$input = mysql_real_escape_string($_GET['category']);
$select = "SELECT * FROM products WHERE product_category = '$input'"; 
$result = mysql_query($select) or die(mysql_error());

}

$GET['categories;] is the submit button and $GET['category'] is the select box holding the data from the product_category which simple consists of id and category_name

redfraggle

the products.product_category has (2) Hoops items and (2) Jewelry items as test products. product_category.category_name has Hoops and Jewelry

bradgrafelman

Erm... if products.product_category is supposed to correspond to the ID numbers of a category (which is what you're passing), then this:

$select = "SELECT * FROM products, product_category WHERE products.product_category = product_category.category_name";

should not work, since you're JOIN'ing the rows that have product_category equal to category_name...

redfraggle

Yeah. The join prints all of the products. I want to filter by Hoops and Jewelry thus the submit box.

bradgrafelman

The point is you should not see "Hoops" or "Jewelry" in the products table; you should instead see the corresponding category ID's for those two categories.

redfraggle

Got it to work. I had to change This:

  echo "<option value=\"".$row['category_id']."\">".$row['category_name']."</option>\n  ";

to this:

  echo "<option value=\"".$row['category_name']."\">".$row['category_name']."</option>\n  ";

It was pulling the category id which does not correspond to the category name in the other table since there is no id for that one. YAY!

Thanks for your help. =)

Kate