How to make categories with PHP and SQL

Question

I'm doing my project with an e-commerce website.

I got index.php that shows cards with product categories, such as "TVs, Computers, Smarwatches..."

I got products.php that shows product list, so far including all the products that I have in database. I got a column called 'type' in database that holds names of categories each product belongs to, such as "phone, tv, camera...".

Now, I need to sort the products into categories, so after clicking eg. "Computers" on index.php, I'll only see computers on products.php. Could anyone please help me?

The cards look like this:

enter image description here

index.php code:

        <p>
            <?php
            $TV = ["id" => "1", "name" => "TVs", "img" => "<img src='img/TV.png'>", "price" => "$1000"];
            $Computer = ["id" => "2", "name" => "Computers", "img" => "<img src='img/computer.png'>", "price" => "$2000"];
            $Laptop = ["id" => "3", "name" => "Laptops", "img" => "<img src='img/laptop.png'>", "price" => "$750"];
            $Camera = ["id" => "4", "name" => "Cameras", "img" => "<img src='img/camera.png'>", "price" => "$500"];
            $Phone = ["id" => "5", "name" => "Phones", "img" => "<img src='img/phone.png'>", "price" => "$400"];
            $Smartwatch = ["id" => "6", "name" => "Smartwatches", "img" => "<img src='img/smartwatch.png'>", "price" => "$300"];

            // echo "<img src='img/computer.jpg'>";

            $catalog = array ($TV, $Computer, $Laptop, $Camera, $Phone, $Smartwatch);

            // print_r($catalog);

                foreach ($catalog as $item) {
                    echo 
                    "<div class='all-items'>
                        <div class='catalog-item'>
                            <div class='catalog-img'>
                            ".$item ["img"]."

                            </div>
                        
                            <h3>
                            ".$item ["name"]."
                            </h3>

                            <div>
                            ".$item ["price"]."
                            </div>"
                            ?>

                            <a href='products.php?category=<?= $item["name"]; ?>' class='catalog-more-button'>
                            See more
                            </a>

products.php looks this way for now (the prices are just fictional):

enter image description here

and here products.php code:

<?php
$result = mysqli_query($conn,"SELECT * FROM `product_details` ORDER BY type");
// $result = mysqli_query($conn,"SELECT * FROM `product_details` WHERE type = 'tv'");

while($row = mysqli_fetch_assoc($result)) {
        echo "<div class='product_wrapper'>
                <form method='post' action=''>
                    <input type='hidden' name='code' value=".$row['code']." />
                        <div class='img'><img src='".$row['img']."' /></div>
                        <div class='name'>".nl2br ($row['name'])."</div>
                        <div class='price'>"."$".$row['price']."</div>
                    <button type='submit' class='buy'>Add to basket</button>
                </form>
              </div>";
        }

mysqli_close($conn);
?>

Try it yourself and ask a question if you come across a specific problem. — GrumpyCrouton, Jun 20 '23 at 14:42
@GrumpyCrouton Well, thanks. I have been trying it for the previous 4 days and I just don't know what else to try to make it work. That's why I asked for help but yeah I'll keep on trying it by myself of course :) — Sisa, Jun 20 '23 at 15:03
Well what did you try and what specifically is the issue you're having? It's unclear exactly where you need help. See also [ask]. Are you having trouble with how to store the data in the database, or how to query it, or how to do the filtering in the page? Or some combination of these? — ADyson, Jun 20 '23 at 15:06
@ADyson okay, sorry for the unclarity. My issue is the filtering. So far there are all kinds of products (tvs, computers, laptops...) displaying on products.php and I only need specific products that belong to the certain category to be there. The user is on index.php, clicks on the "TVs" card, gets to products.php and I only need the TVs to be there and same for other categories. I have tried with $cat = $_GET['type']; .. I've tried to filter it with a function and I've tried it with query, setting specific query for each category but I know that's not the way having 6 times query in the code — Sisa, Jun 20 '23 at 15:30
`I have tried with $cat = $_GET['type'];`...well that would certainly be a start. Then you'd need to use the selected category as a parameter in a SQL query to restrict the results (as part of a WHERE clause in the SQL) — ADyson, Jun 20 '23 at 15:41
@Sisa The problem is that StackOverflow questions are meant to be helpful to future visitors. In this case, the answer would be too broad to be helpful to anyone but you. — GrumpyCrouton, Jun 20 '23 at 16:46
@ADyson hello again! I have been trying to figure this out and I think I might be close with this: $sql = "SELECT * FROM product_details WHERE type = ". $_GET["category"]; but for some reason I'm getting "Unknown column 'TVs' ('Phones'/'Laptops'...) in 'where clause' " error and changing WHERE to HAVING is no help here. Don't you please have any advice? Thanks a lot! — Sisa, Jul 17 '23 at 17:14
The problem is you're not building the query properly. MySQL Thinks `TVs`...etc is a column name. Please read [How to include a PHP variable inside a MySQL statement](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) - you need to use prepared statements with parameters. Not only does it prevent silly syntax mistakes like this (in raw SQL, text values would be quoted), it also protects you from serious hacking attempts against your database. — ADyson, Jul 18 '23 at 07:56

How to make categories with PHP and SQL

0 Answers0