I solved the problem as below
IFNULL(CONCAT(p.input_id,'X',p.output_id,' '),'')
now i have another problem
I collect properties of all products in “product_types” table
some columns are null by product, A section is visible in the example image
$sql = "SELECT c.name, p.id,
CONCAT(
IFNULL(CONCAT(p.input_id,'X',p.output_id,' '),''),
IFNULL(CONCAT(p.product_type_id,' '),''),
b.name,' ',
p.name
) b_p_name
FROM products p
INNER JOIN brands b ON p.brand_id = b.id
INNER JOIN product_categories c ON p.category_id = c.id
WHERE p.product_stock=? AND b.brand_active=?
ORDER BY c.name, b.name, p.name";
i think this is what i have to do
INNER JOIN product_types pt ON p.input_id = pt.id
INNER JOIN product_types pt ON p.output_id = pt.id
INNER JOIN product_types pt ON p.product_type_id = pt.id
I guess I need to do something like this, but it doesn’t work. How should I do INNER JOIN with more than one column of a table?
Databases are not speadsheets. You should only store data that exists. That table should have one row for each separate piece of product/property data.
1 Like
I solved this problem by using LEFT JOIN instead of INNER JOIN but another problem appeared
Example output_type, multiple products are using this column name.
Output type name appeared in unrelated products because I used output_type column name in CONCAT()
Even if I collect the Product Types in a table, each product’s type, feature, etc. columns will be separate.
You’re saying that no column will be common, right?
For each product. Do I create a separate table for its type, property, etc. or do I create them all in one table? What is your suggestion?
You would have one product_type(s) table. It would have columns for -
- an id (which you apparently have)
- product_id (which you apparently have)
- type (which would be whatever the existing column names are)
- value (which would be whatever the existing stored values are)
For each product separately, I have a table of product type, property etc.
Since I have gathered all the products in a single table, Is it correct to tap into a single table for product type, properties, etc.?
It is necessary to create a separate column for each product type.
ID | a_product_type | b_product_type | c_product_type | c_product_type | ....
This means that there will be close to 100 columns in a table in this format.
I used a “product_type” column in the “product_types” table for all products. The table below is shown in the picture
The problems shown in the picture below appear
Product type that does not belong to the product appears
The problem in the picture below is this.
input_name | output_name | there are columns
I used below query to show like 17X8, 17X12, 17X16
IFNULL(CONCAT(t.input_name,'X',t.output_name,' '),'')
I also used the following query for just the output of another product
IFNULL(CONCAT(,t.output_name,' '),''),
As seen in the picture below, these products should not have underlined data in the problem, but they are visible.
This is because all products use the same column.
Is there a solution to this?
Or should I make separate columns for each product type? This means that there will be close to 100 columns in a table, is this the right way?
