how to select product according to the category-CodePudding

This is my code in categories

    $query = "SELECT * FROM categories ORDER BY cat_id ASC";
    $result = mysqli_query($query);
    if(mysqli_num_rows($result) > 0)
    {
      while($row = mysqli_fetch_array($result))
      {
    ?>
        <li><a href="product.php?id=<?php echo $row["cat_id"]; ?>"><?php echo $row["categoriesname"]; ?></a><li>

and This is my code in product.php

    $query = "SELECT * FROM product ORDER BY id";
    $result = mysqli_query($query);
    if(mysqli_num_rows($result) > 0)
    {
      while($row = mysqli_fetch_array($result))
      {
    ?>

  <div  style="margin-top:10px;">
    <form method="post" action="orders.php?action=add&id=<?php echo $row["id"]; ?>">
      <br>
      <div style="border:5px solid #5cb85c; background-color:whitesmoke; border-radius:10px; padding:20px; height:420px" align="center">
        <h4 ><?php echo $row["product_category"]; ?></h4>

        <img src="admin/public/img/<?php echo $row["images"]; ?>"  width="200" height="300"/>

        <h4 ><?php echo $row["product_name"]; ?></h4>

        <h4 >₱ <?php echo $row["product_price"]; ?></h4>

        <input type="number" name="quantity" value="1"  />

        <input type="hidden" name="hidden_name" value="<?php echo $row["product_name"]; ?>" />

        <input type="hidden" name="hidden_image" value="<?php echo $row["images"]; ?>" />

        <input type="hidden" name="hidden_price" value="<?php echo $row["product_price"]; ?>" />
        <br>

        <input type="submit" name="add_to_cart" style="margin-top:5px;"  value="Add to Cart" />

      </div>
    </form>
  </div>

how to do that? how to select according to the category? this is shopping cart...I want to show all of the product by selecting the category..

CodePudding user response：

You need a relationship between these 2 tables(product and categories) to do that.

add cat_id in product table as a foreign key(primary key located in another table). then use the following MySQL code:

SELECT * FROM `Product` 
        INNER JOIN 
              `Categories` 
        ON Product.cat_id = Categories.cat_id 
        WHERE categoriesname = <CategoryName>;

CodePudding user response：

Do you have the table for info?

Im just gessing, but if you have the cat_id as secondary_key in the product table, you should be able to filter your query with your given $_GET['id'] - not sure if you have done like this, but your query for the product list should look like this:

SELECT * FROM product WHERE `cat_id` = '$cat_id' ORDER BY id

Be careful when working with variables that can be set by a user, they can be modified - so always ( other security checks) use something like mysqli_real_escape_string($_GET['id']) to prevent SQL Injection.

If you need more info, you can post your table infos as well!