items and categories-difficult problem

[firelior]

Hello,

I have 2 tables:

items

id | cats_id | title 

cat

id | pid | title 

:

This is the value inside cats_id

1|11|2|5|6

 

those values are the ids of the cat table

 

Now, say I have the an item from the items table

How can I get all the names from cats table in one query?

 

And how say I have a category,

How can I get the number of items that belong to that category?

 

I know that it isn't a good way of doing it.

Can you tell me of a good way to do it?

 

Thank you very much..

[Corona4456]

So you are trying to grab all the categories that the said item belongs to?

If so then we'll assume the item's data is stored in an associative array named $item.

<?php
$query = "SELECT title from `cat` WHERE id IN (" . str_replace("|", "," $item[cats_id]) . ")";
?>

[Barand]

A better way to do it

 

[pre]

item table          item_cats            cat table

===========          ===========          ==========

id    -----------<  item_id        +---- id

title                cat_id  >------+    title

[/pre]

 

So if item 20 belongs to cats 1,11,2,5,6

item_cats would have these records

 

[pre]

                      20  |  1

                      20  |  11

                      20  |  2

                      20  |  5

                      20  |  6

[/pre]

[Corona4456]

A better way to do it

 

[pre]

item table          item_cats            cat table

===========          ===========          ==========

id    -----------<  item_id        +---- id

title                cat_id  >------+    title

[/pre]

 

So if item 20 belongs to cats 1,11,2,5,6

item_cats would have these records

 

[pre]

                      20  |  1

                      20  |  11

                      20  |  2

                      20  |  5

                      20  |  6

[/pre]

That would be a better way and then you'd have to do a similar query like I showed you in order to get the titles for your categories.

[firelior]

thank you both

I did it barands way

thanks

[Corona4456]

Welcome.

[Barand]

The queries you might need in that case are

 

1 ) list of cats to which $item belongs

 

SELECT GROUP_CONCAT(c.title SEPARATOR ', ') as cats
FROM item_cat i
INNER JOIN cat c ON i.cat_id = c.id
WHERE i.item_id = '$item'

 

EG --> Cat1, Cat2, Cat5, Cat6, Cat11

 

 

2 ) Count of items in $cat

 

SELECT COUNT(*) as num FROM item_cat
WHERE cat_id = '$cat'