PHP Shopping cart - page with pages does not work as required

Question

I have 2 tables

the products

and categories

(CatParentId 0 is the parent category).

What I'm trying to achieve is when the user clicks on the parent category, he should retrieve the products listed in this category, as well as products of the child category, and if the user clicks on any child category, he should retrieve only those products which are listed in the child category.

Here is the complete code that I have used so far, but without success:

<section class="col-lg-9 col-md-9 col-sm-9"> <?php $catId = $catName = $n = ""; $id = 0; require_once 'Classes/class.Validation.php'; $validate = new Validation(); if ( isset( $_GET['name'] ) && $_GET['name'] != "" ) { $catName = $_GET['name']; $u = "SELECT CatId, CatName, CatParentId FROM categories WHERE CatName = '".$catName."'"; $validate->Query($u); if ($validate->NumRows() >= 1) { while ($rows = $validate->FetchAllDatas()) { $id = $rows['CatId']; $n = $rows['CatName']; $query = "SELECT c.CatName, p.ProdCode, p.ProdName FROM products p, categories c WHERE c.CatId = p.CatId AND c.CatParentId = '".$id."'"; $validate->Query($query); if ($validate->NumRows() >= 1) { while ($row = $validate->FetchAllDatas()) { // show products here } } else { $query = "SELECT c.CatName, p.ProdCode, p.ProdName FROM products p, categories c WHERE c.CatParentId = p.CatId AND c.CatId = '".$id."' "; $validate->Query($query); if ($validate->NumRows() >= 1) { while ($row = $validate->FetchAllDatas()) { // show products here } } } } } } ?> </section> 

I am sure that I have a logical error, but I can not find it where I made it. Please help me. Any help would be greatly appreciated.

Update 1 :

I decided it myself. Used by INNER JOIN .

Here's the code for future reference: →

 <section class="col-lg-9 col-md-9 col-sm-9"> <?php $catId = $catName = $n = ""; $id = 0; require_once 'Classes/class.Validation.php'; $validate = new Validation('benef8w7_ecommerce'); if ( isset( $_GET['name'] ) && $_GET['name'] != "" ) { $catName = $_GET['name']; $query = "SELECT p.ProdCode, p.ProdRate, c1.CatId, c1.CatName, c2.CatParentId FROM categories c2 INNER JOIN categories c1 ON c2.CatId = c1.CatParentId INNER JOIN products p ON p.CatId = c1.CatId WHERE c2.CatName = '".$catName."'"; $validate->Query($query); if ($validate->NumRows() >= 1) { while ($row = $validate->FetchAllDatas()) { // show all the products here for both parent and child categories. } } else { $query = "SELECT p.ProdCode, p.ProdName, c.CatId, c.CatParentId, c.CatName FROM products p INNER JOIN categories c ON c.CatId = p.CatId WHERE c.CatName = '".$catName."'"; $validate->Query($query); if ($validate->NumRows() >= 1) { while ($row = $validate->FetchAllDatas()) { // show products here if there are no child categories. } } } } ?> </section> 

But after update 1, I think I came up with another error / error : →

The above queries inside Update 1 work great if there are no products in the parent category, but there are products inside the child category. If there are products in the parent category (having a child category), then the product (s) of the parent category / is not displayed and / or only child categories (s) are displayed.

I want to show all products of the parent category as well as the child category if the user clicks on the parent category.

How to fix this error / error?

Answer 1

I am very new to php and I just thought I could learn something to think about your problem. So this window came to my mind. I used some kind of pseudo code. I hope you understand this idea.

 SELECT CatId, CatParentId FROM categories WHERE CatName = '$catName'; you save CatId and CatParentId to php vars... //is it child? if( $CatParentId > 0 ) { SELECT ProdName FROM products WHERE CatId = '$CatId'; //display all products from this child category } else { //for when our category is a parent //we need to find how many childs it has. //I suggest using an array of all cat IDs we want to display products from SELECT CatId FROM categories WHERE CatParentID = '$CatId'; //we store it to an array //we cannot forget about our Parent category $categories[x]; //we want to create SELECT //now we need to add php code if(sizeof($categories) == 1 ){ //our parent has 0 childs SELECT ProdName FROM products WHERE CatId = '$CatId'; } else { SELECT ProdName FROM products WHERE CatId = '$CatId' + for( $i = 1; $i <= sizeof($categories); $i++) { //I assume we saved our parent to $categories[0] //here you add remaining conditions "OR CatId = '$categories[i]"; } + add last ";" } } 

I hate innerjoins:]

Good luck, and I hope that a good programmer will respond so that we get feedback:]