Home / Questions / 90228
Accepted
J. Mick
Asked: 2020-07-25 13:09:37 +0800 CST 2020-07-25 13:09:37 +0800 CST

How to add a category from another table to the product that is related?

  • 772

I have the idea of ​​displaying in the query results the name of the category that the product is related to.

This design is suggested to me by @Lixus in his comment...

This table layout would be ideal for just one related product category.

Category table

id_cat       title     
  1           PHP
  2         MYSQLI
  3         JQUERY

But if the product is related to more than one category, the ideal would be to add the product to the categoryid_pro table

id_cat       title    id_pro   
  1           PHP       1
  2         MYSQLI     NULL
  3         JQUERY     NULL
  4          HTML       1

And so to be able to show a result in this way:

enter image description here

product table

  id_pro          title          categoria
    1           live glypicon        1

As can be seen in the categories table in the column id_prois the number 1that is based on the number 1of the productsid_pro table .

And in the products table is the number 1in the column categoriathat is referring to the name of the category it belongs to.

NOTE: I don't know if the design of the database and the tables are correct, but my idea is to be able to show the following result of the image. Bearing in mind that the product may be related to more than one category as shown in the first image where there were two categories for the same product PHP&HTML

enter image description here

My query:

function newpro(){
    global $con;
    $sql = "Select * from products where id_product and active='1' order by id_product ASC limit 1";
    $rows = mysqli_query($con, $sql);
    $new_products = mysqli_num_rows($rows);

    if($new_products==0){
        echo"No hay cursos nuevos para mostrar";
    }else{
        while ($pro = mysqli_fetch_array($rows)) {

            for ($i=0; $i<8; $i++) {

            echo '<div class="four-column middle-pro">
        <div class="frame-item">
          <a href="'.$pro['url'].'">
            <img src="assets/img/upload/image/'.$pro['image'].'" alt="'.$pro['title'].'" />
          </a>
          <div class="all-description">
            <div class="women">
              <h2><a href="#">'.$pro['title'].'</a></h2>
              <h3><a href="">'.$pro['subtitle'].'</a></h3>
              <h6><a href="">AQUI QUIERO MOSTRAR LA CATEGORIA</a></h6>
            </div>
            <div class="price align-right">
              <p><label>$'.$pro['price_old'].'</label><em class="item_price">$'.$pro['price'].'</em></p>
                          <!--<div class="clearfix"></div>-->
            </div>
            <!--<div class="center add">
              <a href="#">más información...</a>
            </div>-->
          </div>
        </div>
      </div>';
        }}
    }
}
php

1 Answers

  1. Best Answer

    From what you say, without focusing on the code, your database design is wrong.

    Each category can have multiple products, and each product can have multiple categories. Therefore, you should have 3 tables.

    One of categories:

    id_cat title

    other products:

    id_prod title

    And another one that unites the two (produ-cat):

    id_cat id_prod

    And your query would become:

        select * from productos left join produ-cat on id_prod = id_prod left join
 categorias on id_cat = id_cat where title = 'producto'

    (The query is just the idea of ​​what you should have)

    With a query like this, you would obtain the desired result of the categories for a certain product. And so you can go through them calmly.

    • 3