One item, two categories

Frozen_Kiwi

I apologize in advance if it looks like a stupid question to you but somehow I'm unable to wrap my brains around it. 🙁 Say I have a simple table like this:

text_title                category_id
-------------------------------------
dummy text1                2
-------------------------------------
dummiest text ever         5
-------------------------------------
not so dummy text          1
-------------------------------------

$query = "SELECT text_title from some_table
        WHERE category_id = 'category_id'";

Here comes the problem: I want some texts to fall under 2 or more categories. So the whole thing should look like that:

text_title                category_id
-------------------------------------
dummy text1                2
-------------------------------------
dummiest text ever         5|2
-------------------------------------
not so dummy text          1|5
-------------------------------------

But how on Earth can I select the necessary rows now? Any help is appreciated...

bastien

do multiple records and select a distinct value based on cat

select

text_title category_id

dummy text1 2

dummiest text ever 5

dummiest text ever 2

not so dummy text 5

not so dummy text 1

Frozen_Kiwi

Thanks but that's hardly possible, I'm afraid. I gave a simplified example of the table, the real one contains texts themselves and some are quite lengthy. Is there another way - except splitting the table in two so that categories and texta are separated?

wempy

sql normalisation means that you only have one value in a cell in a table.

to achieve this you will need to have a seperate categories table, and maybe a seperate map table as well to map cat id's to text id's.

so you would then have:

text table:
id  text
1   some text
2   text some more
3   even more text
4   some more text though not as much as before

category table:
id  category
1   dim
2   dum
3   dom
4   dam


category -> text mapping table
id  cat_id   text_id
1     1          3
2     2          3
3     1          1
4     3          2

That way whenever you select items from one category, even if an item falls into two or more categories then it will be retrieved thus:

select A.id, A.text from text A,category B,cat_text_map C where B.cat_id=2 and B.cat_id=C.cat_id and c.text_id=A.text_id

Frozen_Kiwi

Thanks a bunch! A trifle more work for the poor me but it does the trick nevertheless. 🙂