[RESOLVED] How can i do this !?

[A__de_Sousa]

HI All,

I have 2 tables:

tbl_product
pd_id
cat_id
pd_name
...

tbl_category
cat_id
cat_name
...

And i want to store 2 or 3 or more categories in tbl_product cat_id.
1 product can have more categories.
😕How can i do this please !?😕
Thnks

[laserlight]

Create another table that associates a product with a category using their respective ids. That is:
tbl_product
pd_id
pd_name
...

tbl_category
cat_id
cat_name
...

tbl_product_category
pd_id
cat_id

[A__de_Sousa]

Theres is no a way to do something like this :

tbl_product

| pd_id | cat_id | pd_name |
|----------------------------------
| 234 |1,23,45| xxxxxxxx|
| 4324 |7, 32,74| asdsada|

thnks

[laserlight]

A. de Sousa wrote:

Theres is no a way to do something like this :

You could do that, but multi-valued fields tend to be harder and less efficient to work with than well designed databases where joins are used appropriately.

[A__de_Sousa]

Ok, but i dont undestand how can i do a multiple category per product with that 3 table. Mi tables r already asociated with cat_id

tbl_product
pd_id
cat_id
pd_name
...

tbl_category
cat_id
cat_name
...

but i can only store 1 category per product. Im a begginer in this ...
Can u please show me example !?
thnks

[laserlight]

A. de Sousa wrote:

Mi tables r already asociated with cat_id

Notice that I removed the cat_id column from tbl_product.

A. de Sousa wrote:

Can u please show me example !?

Suppose you have two categories and two products, and both products belong in both categories. Your tables might look like this:

tbl_product:
pd_id pd_name
1     product A
2     product B

tbl_category:
cat_id cat_name
1      category A
2      category B

tbl_product_category:
pd_id cat_id
1     1
1     2
2     1
2     2

So, we know that product A is in categories A and B since pd_id 1 is associated with cat_id 1 and 2 in tbl_product_category. Likewise, we know that category B has product A and product B since cat_id 2 is associated with pd_id 1 and 2 in tbl_product_category. The corresponding SQL queries would be respectively:

SELECT tbl_category.cat_name
FROM tbl_product, tbl_category, tbl_product_category
WHERE tbl_product.pd_id=tbl_product_category.pd_id
    AND tbl_category.cat_id=tbl_product_category.cat_id
    AND tbl_product.pd_name='product A';

SELECT tbl_product.pd_name
FROM tbl_product, tbl_category, tbl_product_category
WHERE tbl_product.pd_id=tbl_product_category.pd_id
    AND tbl_category.cat_id=tbl_product_category.cat_id
    AND bl_category.cat_name='category B';

The combination of pd_id and cat_id would be the primary key of tbl_product_category (or you could add an auto-incrementing integer column to serve as the primary key).

[A__de_Sousa]

Ok i think i undestand,
Now, my tables r populated, how can i match all values in tbl_product_category !?

[laserlight]

A. de Sousa wrote:

Now, my tables r populated, how can i match all values in tbl_product_category !?

What do you mean?

[A__de_Sousa]

Hi again laserlight,
My "problem" is, i have a database with 786 books, in al my books i have associated 1 category. Works just fine ! Now i want to add in some of the books 2,3, ..., categories.
Example,
Book A belongs to category A. - Like this works fine

And i want,
Book A Belongs to category A,B,C ...

tbl_product

pd_id --> primary/autoincrement
cat_id --> index
pd_titre --> index
...

And tbl_category

cat_id --> primary/autoincrement
cat_name --> unique

I tried to add 2 fields to my tbl_product, like cat_id2 and cat_id3 to add more categories, on same record. Like that i just add 1 record about 2 or 3 categories in 1 row. Works but comes out as a number, the category number, and i cant show it as text, as category name.
Comes like this, Book A - Roman - 32 - 0. I cant change that 32(in categorylist is humor) to the category name. And i dont know what 2 do !! Tried to change cat_id2 and cat_id3 as text, char, varchar but is awlays the category number that comes out. What m i doing wrong !?

Tried to explain better, but my inglish is not good as it should
Thnks again

[djjjozsi]

you have to add the product ID and the Category ID pairs
into new rows into the tbl_product_category table.
AND you have to use the select, (Laserlight suggested to you) , to list not the PRIMARY Keys but the Category names AND Product names...

tbl_category
cat_id	cat_name
1	Category1
2	Category2
3	Category3

tbl_product
pd_id	pd_name
1	Product 1
2	Product 2
3	Product 3
4	Product 4

tbl_product_category
pd_id	cat_id
1	1
1	2
1	3
3	3
4	3

[djjjozsi]

After the SELECT list those fields you wanted to use ( tbl_product.pd_name, tbl_category.cat_name )

SELECT tbl_product.pd_name, tbl_category.cat_name
FROM tbl_product, tbl_category, tbl_product_category
WHERE 
(
(tbl_product.pd_id=tbl_product_category.pd_id) 
AND 
(tbl_category.cat_id=tbl_product_category.cat_id)
)
ORDER BY tbl_product.pd_name;

[A__de_Sousa]

And theres no a way, like a array a function or a script, that i can use to match cat_id2 and cat_id3 with cat_id to get the category name !?

[Weedpacket]

djjjozsi's query does give the category name ("tbl_category.cat_name").

If you already have a category number then it's just

SELECT cat_name FROM tbl_category where cat_id=?

[djjjozsi]

And theres no a way, like a array a function or a script, that i can use to match cat_id2 and cat_id3 with cat_id to get the category name !?

You should learn the method what laserlight suggested,
then you didn't need to hardcode the category name selector, just use one query.

There are daily threads about table relation "problems" in this forum, lets see their explanations 🙂

Hello,
jjozsi.

[A__de_Sousa]

Well before nothing, i want to thank you, laserlight, djjjozsi and Weedpacket for responses🆒
Im tring laserlight sugestion, but im going to take my time 🙂
Thnks