filter recordset category to sub category not working, what am i doing wrong

[Jonathan_Fortis]

I have done this lots of times but must have made a mistake because i cant get it to work

i have a category list displaying all categories

ymysql_select_db($database_lotties, $lotties);

$query_rsProductData = "SELECT * FROM lottieCat ORDER BY lottieCat.CatName";

$rsProductData = mysql_query($query_rsProductData, $lotties) or die(mysql_error());

$row_rsProductData = mysql_fetch_assoc($rsProductData);

$totalRows_rsProductData = mysql_num_rows($rsProductData);

?>

        <?php do { ?>

          <a href="subCat-list.php?subCat=<?php echo $row_rsProductData['subCatID']; ?>"><?php echo $row_rsProductData['CatName']; ?><br />

</a>

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

<?php

mysql_free_result($rsProductData);

?>

but on the link i want to send the to the sub category page but it isnt working. below is the sql for the sub category page

$varCat_rsProductData = "0";

if (isset($_GET["subCat"])) {

  $varCat_rsProductData = $_GET["subCat"];

}

mysql_select_db($database_lotties, $lotties);

$query_rsProductData = sprintf("SELECT * FROM LOTTIE_subCats, lottieCat WHERE lottieCat.CatID = LOTTIE_subCats.CatID AND lottieCat.CatID = %s", GetSQLValueString($varCat_rsProductData, "int"));

$query_limit_rsProductData = sprintf("%s LIMIT %d, %d", $query_rsProductData, $startRow_rsProductData, $maxRows_rsProductData);

$rsProductData = mysql_query($query_limit_rsProductData, $lotties) or die(mysql_error());

$row_rsProductData = mysql_fetch_assoc($rsProductData);

the joins in the table are

table = lottieCat

CatID

Catname

table=LOTTIE_subCats

subCatID

CatID

then the products is

table = LOTTIE_products

subCatID

what am i doing wrong

[Jonathan_Fortis]

ok i think i need to add some more to the statement of the category list so i changed this to

mysql_select_db($database_lotties, $lotties);

$query_rsProductData = "SELECT * FROM lottieCat, LOTTIE_subCats WHERE LOTTIE_subCats.subCatID = lottieCat.CatID  ORDER BY lottieCat.CatName";

$rsProductData = mysql_query($query_rsProductData, $lotties) or die(mysql_error());

$row_rsProductData = mysql_fetch_assoc($rsProductData);

$totalRows_rsProductData = mysql_num_rows($rsProductData);

this then gives me a sub category value the values are of the categories and not the sub categories

[Jonathan_Fortis]

ok i have now done the following

mysql_select_db($database_lotties, $lotties);

$query_rsProductData = "SELECT * FROM lottieCat, LOTTIE_subCats WHERE LOTTIE_subCats.CatID = lottieCat.CatID  ORDER BY lottieCat.CatName";

$rsProductData = mysql_query($query_rsProductData, $lotties) or die(mysql_error());

$row_rsProductData = mysql_fetch_assoc($rsProductData);

$totalRows_rsProductData = mysql_num_rows($rsProductData);

it is showing now in the list the correct subcategories BUT if the are two products in that sub category (example Tools a red one and a blue one) it is showing tools twice

[bregent]

>$query_rsProductData = "SELECT * FROM lottieCat, LOTTIE_subCats

>WHERE LOTTIE_subCats.CatID = lottieCat.CatID  ORDER BY lottieCat.CatName";

That query doesn't even have a filter. Not sure what you are trying to do here.

>it is showing now in the list the correct subcategories BUT

>if the are two products in that sub category (example Tools

>a red one and a blue one) it is showing tools twice

Then you have a problem with your data, because you are not even including the product table in that query.

[Jonathan_Fortis]

i have never set a sub category senario up before i have always taken it from category list to product list to product details.

this time i need to go from category list to sub category to product list to product detail

[Jonathan_Fortis]

>Then you have a problem with your data, because you are not even including the product table in that query.

i dint think i needed the product table in that becuase i am not interested in the product yet just the category and sub category or should i included the products aswell?

[Jonathan_Fortis]

can anyone else give me help omn this issue. If i need to provide more information what do i need to show?

thanks

[bregent]

>i dint think i needed the product table in that becuase

>i am not interested in the product yet just the category

>and sub category or should i included the products aswell?

Right. But you said "BUT if the are two products in that sub category (example Tools a red one and a blue one) it is showing tools twice".  If your query is not using the product table, then that can't be the reason the subcat shows twice. We need to see all the data in the cat and subcat tables.

[Jonathan_Fortis]

>>>We need to see all the data in the cat and subcat tables.

sorry being a bit slow, so i need to change my sql to included LOTTIE_subCats.productD = LOTTIE_products.productID

is that what you mean?

[bregent]

No, that is not what I meant. The last problem you reported is that you are getting 'duplicated' rows. In order to determine the problem we need to see the data in the tables used in the query.

[Jonathan_Fortis]

so lottieCat

CatID

CatName

CatImage

LOTTIE_subCats

subCatID

CatID

subCatName

subCatImage

[bregent]

Those look like column names. I don't see any data there.

[Jonathan_Fortis]

I have included content now, will this help?

lottieCat

CatID

1

2

3

4

CatName

tools

cutters

bags

equiptment

CatImage

image1.jpg

image2.jpg

image3.jpg

image4.jpg

-----------------------------------

LOTTIE_subCats

subCatID

1

2

3

CatID

1

1

2

subCatName

Plastic Tools

Metal Tools

Plunger Cutters

subCatImage

cat-dec-tools1365679004.JPG

metal1365679023.jpg

flower plunger1366019771.jpg

thanks

[bregent · Accepted solution]

OK, so if you want to produce a list of subcategories, you don't need to join to the category table.

$query_rsProductData = "SELECT * LOTTIE_subCats WHERE LOTTIE_subCats.CatID = %s ;

[Jonathan_Fortis]

ok getting there, thanks so much for your help so far, all working great except i need to show other products from the subcategory ,  i think you showed me this in a previous post but a slight variation

$var1_rsProductData = "-1";

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

  $var1_rsProductData = $_GET['recordID'];

}

mysql_select_db($database_lotties, $lotties);

$query_rsProductData = sprintf("SELECT * FROM LOTTIE_products, LOTTIE_subCats WHERE LOTTIE_subCats.subCatID = LOTTIE_products.subCatID AND LOTTIE_products.subCatID = %s", GetSQLValueString($var1_rsProductData, "int"));

$rsProductData = mysql_query($query_rsProductData, $lotties) or die(mysql_error());

$row_rsProductData = mysql_fetch_assoc($rsProductData);

$totalRows_rsProductData = mysql_num_rows($rsProductData);

mysql_select_db($database_lotties, $lotties);

$query_rsCategory = "SELECT * FROM LOTTIE_products WHERE LOTTIE_products.subCatID = (SELECT subCatID from LOTTIE_products WHERE LOTTIE_products.ProductID = $var1_rsProductData)";

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

$row_rsCategory = mysql_fetch_assoc($rsCategory);

$totalRows_rsCategory = mysql_num_rows($rsCategory);

the above isnt working

[bregent]

>the above isnt working

Always include the exact symptoms with your questions. "isn't working" is not enough information for me.

You are showing two recordsets. The first will return all products of a specific subcat. It's using a value from the querystring called 'recordID'.

The second query appears to be returning all products where the subcat is equal to the subcat of a product who's ProductID equals the 'recordID' querystring.

Obviously, both of these queries can not work.

[Jonathan_Fortis]

understood. No working is not clear enough, the results show nothing.

i am aware that these SQL statements are incorrect but i am unsure what they should be, thats why i was asking

thanks

[Jonathan_Fortis]

hang on i have missed a step out i have gone straight from the subcategory list to the product detail. i need to go from the subcategory to the product list first.

[Jonathan_Fortis]

after some sleep i have fixed the issue by following previous logic, thanks