basic filtering PHP...

[Jonathan_Fortis]

i have a product page showing all products. this is linked to the product-detail page and working fine

<div id="indexImage"><a href="SS13product-description.php?ID=<?php echo $row_Recordset1['ProductID']; ?>"><img src="../images/SS13/thumbs/<?php echo $row_Recordset1['ImageDetail']; ?>"/></a></div>

on the description page i have the following SQL

$var1_rsProduct = "-1";

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

  $var1_rsProduct = $_GET['ID'];

}

mysql_select_db($database_beau, $beau);

$query_rsProduct = sprintf("SELECT * FROM beauSS13_Cat, beauSS13_products, beauSS13_Stock, beauSS13_SizeList WHERE beauSS13_products.CatID = beauSS13_Cat.catID AND beauSS13_products.ProductID = beauSS13_Stock.ID AND beauSS13_Stock.SizeID = beauSS13_SizeList.SizeID AND beauSS13_products.ProductID = %s", GetSQLValueString($var1_rsProduct, "int"));

$rsProduct = mysql_query($query_rsProduct, $beau) or die(mysql_error());

$row_rsProduct = mysql_fetch_assoc($rsProduct);

$totalRows_rsProduct = mysql_num_rows($rsProduct);

what i want to do on this product description page aswell is show all the products from that category

i made the following but this just shows the same product and not all the other products

mysql_select_db($database_beau, $beau);

$query_rsCategory = "SELECT * FROM beauSS13_Cat, beauSS13_products WHERE beauSS13_products.CatID = beauSS13_Cat.catID";

$rsCategory = mysql_query($query_rsCategory, $beau) or die(mysql_error());

$row_rsCategory = mysql_fetch_assoc($rsCategory);

$totalRows_rsCategory = mysql_num_rows($rsCategory);

where am i gonig wrong?

[bregent]

>where am i gonig wrong?

You did not include an expression in the WHERE clause to filter by category.

[Jonathan_Fortis]

thats what i tried but when i do

$var2_rsCategory = "-1";

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

  $var2_rsCategory = $_GET['ID'];

}

mysql_select_db($database_beau, $beau);

$query_rsCategory = sprintf("SELECT * FROM beauSS13_Cat, beauSS13_products WHERE beauSS13_products.CatID = beauSS13_Cat.catID AND beauSS13_products.ProductID = %s", GetSQLValueString($var2_rsCategory, "text"));

$rsCategory = mysql_query($query_rsCategory, $beau) or die(mysql_error());

$row_rsCategory = mysql_fetch_assoc($rsCategory);

$totalRows_rsCategory = mysql_num_rows($rsCategory);

it is giving me no results

[bregent]

>$query_rsCategory = sprintf("SELECT * FROM beauSS13_Cat,

>beauSS13_products WHERE beauSS13_products.CatID = beauSS13_Cat.catID

>AND beauSS13_products.ProductID = %s", GetSQLValueString($var2_rsCategory, "text"));

Looks like you are filtering on the ProductID column, but the value you are passing is a Category ID. If you alrady have the category then don't you want to filter on the CategoryID column? But in your original post, the querystring ID represented is the ProductID. It can't be both!

If you are trying to display a list of products that belong to the same category as the product in the detail page, that would be a different query. You could either use a subquery, or use the category value obtained from the first query.

[Jonathan_Fortis]

Yes I am trying to display all products that have the same value as the product on the detail page. So I need to make a new query but give it the same value as the first query?

[bregent]

Then use a subquery:

$query_rsCategory = sprintf("SELECT * FROM beauSS13_products WHERE

beauSS13_products.CatID = (SELECT CatID from beauSS13_products WHERE beauSS13_products.ProductID = %s", GetSQLValueString($var1_rsProduct, "int")));

Question: Why does does CatID start with upper case in the product table and lower case in the category table?

[Jonathan_Fortis]

i tried the above but it was showing error prompts so i changed it slightly

mysql_select_db($database_beau, $beau);

$query_rsCategory = sprintf("SELECT * FROM beauSS13_products WHERE

beauSS13_products.CatID = (SELECT CatID from beauSS13_products WHERE beauSS13_products.ProductID = %s)", GetSQLValueString($var1_rsProduct, "int"));

$rsCategory = mysql_query($query_rsCategory, $beau) or die(mysql_error());

$row_rsCategory = mysql_fetch_assoc($rsCategory);

$totalRows_rsCategory = mysql_num_rows($rsCategory);

then there were no errors but when i loaded the page all information from the first query $query_rsProduct was not being displayed

below are both queries

$var1_rsProduct = "-1";

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

  $var1_rsProduct = $_GET['ID'];

}

mysql_select_db($database_beau, $beau);

$query_rsProduct = sprintf("SELECT * FROM beauSS13_Cat, beauSS13_products, beauSS13_Stock, beauSS13_SizeList WHERE beauSS13_products.CatID = beauSS13_Cat.catID AND beauSS13_products.ProductID = beauSS13_Stock.ID AND beauSS13_Stock.SizeID = beauSS13_SizeList.SizeID AND beauSS13_products.ProductID = %s", GetSQLValueString($var1_rsProduct, "int"));

$rsProduct = mysql_query($query_rsProduct, $beau) or die(mysql_error());

$row_rsProduct = mysql_fetch_assoc($rsProduct);

$totalRows_rsProduct = mysql_num_rows($rsProduct);

mysql_select_db($database_beau, $beau);

$query_rsCategory = sprintf("SELECT * FROM beauSS13_products WHERE

beauSS13_products.CatID = (SELECT CatID from beauSS13_products WHERE beauSS13_products.ProductID = %s)", GetSQLValueString($var1_rsProduct, "int"));

$rsCategory = mysql_query($query_rsCategory, $beau) or die(mysql_error());

$row_rsCategory = mysql_fetch_assoc($rsCategory);

$totalRows_rsCategory = mysql_num_rows($rsCategory);

[bregent]

>i tried the above but it was showing error prompts so i changed it slightly

Yep, mine had a typo - I put the closing paren in the wrong place - yours is correct.

>then there were no errors but when i loaded the page all

>information from the first query $query_rsProduct was not being displayed

The second query should not have any impact of the first. Was it working previously? What information is missing?

[Jonathan_Fortis]

ok i have just added the SQL statments manually as above and the page is loading. But in order to to see if it is displaying all the products from the category i need to repeat region the output

i have done

<?php do { ?>

            <p><?php echo $row_rsCategory['Product']; ?></p>

            <?php } while ($row_rsCategory = mysql_fetch_assoc($row_rsCategory)); ?>

but it is showing error

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

inE:\Domains\b\beau.com\user\htdocs\SS13\SS13product-description.php on line 286

line 286 is

<?php } while ($row_rsCategory = mysql_fetch_assoc($row_rsCategory)); ?>

[bregent · Accepted solution]

You are supplying the wrong argument to the function:

   <?php } while ($row_rsCategory = mysql_fetch_assoc($row_rsCategory)); ?>

should be

   <?php } while ($row_rsCategory = mysql_fetch_assoc($rsCategory)); ?>

[Jonathan_Fortis]

Thanks that worked great

[bregent]

You're welcome.