PHP Shopping cart - Categories page not working as wanted

Question

I have 2 tables

products

and categories

(CatParentId 0 is the parent category).

What I am trying to achieve is when a user clicks on the parent category, it should fetch the products listed in that category as well as it's child category products and if the user clicks on any of the child category, it should fetch only those products listed in the child category.

Here's the complete code that I have used so far, but to no 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 pretty sure that I have a logical error but I cannot find it where I have made it. Kindly help me out. Any help will be highly appreciated.

Update 1:

I have solved it on my own. Used 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 guess I have come up with other bug/error :->

The above queries inside the update 1 works just fine provided there are no products at all in the parent category but there are products inside the child category. If there are products in the parent category (having child category), the parent category product(s) is/are not shown and only child category product(s) is/are shown.

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

How do I resolve this bug/error ?

Norge

Kindly help me out. I am trying this since 3 days.. – Norge

I am curious on how this had not been answered then? – Countryandwestern

Please be warned that your queries are highly vulnerable for SQL injection. Have a look at prepared statements to avoid getting hacked – Hessian

Answer 1

as I understand, and I am far from being a pro, when using 2 tables as 1, you would need a same key in both tables and then use an interjoin. something like this.

    $products = [];
if (isset($_GET['component_id'])) {
    $sql = "SELECT inventory.* FROM inventory 
            JOIN components ON inventory.type = components.type 
            WHERE components.component_id = " . $_GET['component_id'];
    $result = $conn->query($sql);

Want

Please be warned that this query is highly vulnerable for SQL injection. Have a look at prepared statements to avoid getting hacked – Hessian

thank you, looking in to that. my page is not published, therefore not too worried yet. as a beginner, i am learning. thank you again. – Want

thanks again. I updated my code to use bind stmt and prepare. – Want