How to connect two MySQL tables

Question

I have in my MySqli Database a table called "products".

Products TABLE 
product_id       | INT      primary KEY
product_name     | VARCHAR(50)
product_price    | float

From PHP i enter rows in the table products like this way:

   mysqli_query($con,"INSERT INTO products (product_id,product_name,product_price) VALUES 
                                           ('$product_id','$product_name','$price')");                                      

So far all work perfectly. Now i want to have a second table called "category", this table will include all the possible categories that a product can have

The Category table must have a category_id and a category_name as columns

Category TABLE 
category_id       | INT      primary KEY
category_name     | VARCHAR(50)

I'm trying to figured out a way to connect a product with the category in my PHP file

  for example:
  $get=mysqli_query($con, "SELECT * FROM `category`");
  while ($row = mysqli_fetch_assoc($get)) { 
         echo $row['category_name'];   //...here show all the categories
         //...
         //.. pick the category that the product belong
         $category_Selected= .....;
                    }?>
   ..... And make the connection (with INSERT? or something) between product and category

Ι want to be able when i'm doing a search at the product table, (for a product X) to show also and the category that it belongs (so far i can show the Product_id, product_name and product_price)

Thank you

Answer 1

As it is, your database does not allow you to represent the relationshup between categories and products. You would need to alter your design.

I can imagine that one product belongs to a category, and that one category can have several products.

If so, I would recommend creating a categories table to store the categories, with (at least) columns category_id and category_name.

CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(100)
);

In the product table, you want to add a column in the products table that stores a reference to the id of the corresponding category :

ALTER TABLE products ADD 
    COLUMN category_id INT 
    FOREIGN KEY (category_fk) REFERENCES categories(id) ON DELETE CASCADE;

---

With this modified database design, when you insert into products, you pass the reference of the category (one could expect that the user of your application will select it from some kind of drop down list when creating the product) :

INSERT INTO products (product_id, product_name, product_price, category_id) 
VALUES ( :product_id, :product_name, :price, :category_id );

And when you want to display a product along with its category name, you can use a simple JOIN :

SELECT p.*, c.category_name
FROM products p
INNER JOIN categories c ON c.category_id = p.category_id
WHERE p.produt_id = :product_id

PS : never pass POSTed values to your SQL queries like this : this exposes you to SQL injection (and also makes your queries less readable and efficient). I changed the queries to use named parameters.

Answer 2

If a product can be only in one category then you can add a category_id in your Products table.

I would suggest a third table the:

Product_category
id         | PK
product_id | Foreign key to Products.id
category_id| Foreign key to Categories.id

Now every time you insert a product you need to get also the id of your category and do an insert statement to Product_category table.

To retrieve your data you could do something like this:

 $get=mysqli_query($con, "SELECT * FROM `category`");
    while ($row = mysqli_fetch_assoc($get)) {

      echo $row['category_name'];   //...here show all the categories

      $products=mysqli_query($con, "SELECT * FROM `Products` WHERE id IN 
      (SELECT product_id from Product_category WHERE category_id= ".(int)$row['category_id'] . ")");

      while ($product = mysqli_fetch_assoc($products)) {

           echo $product["product_name"] . ", " . $product["product_price"]; 

      }
    }

The above statement is as example, you could use JOIN and prepared statements.

If you choose to alter the product table and add the category_id there, then the example code would be this:

 $get=mysqli_query($con, "SELECT * FROM `category`");
    while ($row = mysqli_fetch_assoc($get)) {

      echo $row['category_name'];   //...here show all the categories
      $products=mysqli_query($con, "SELECT * FROM `Products` WHERE category_id = " . (int) $row["category_id"]);

      while ($product = mysqli_fetch_assoc($products)) {

           echo $product["product_name"] . ", " . $product["product_price"]; 

      }
    }

Answer 3

You want to join your Tables.

Take a look here:

Join Tables - w3schools

MySQL Join Tables Syntax