Keyword's plurality in MYSQL query

How to return array of keywords

[tomda]

Hi everyone,

MYSQL request

I have a picture databank and for each picture, there is a list of keywords (in only one field), such as :
pic1 -> key1, key2, key3, key 4
pic2 -> key2, key3, key5

I am looking for a query to count of many times the keyword appear (or if you want an array of keywords and how often they appear)

Result for the two rows above (pic1 and pic2) should be :
key1 = 1
key2 = 2
key3 = 2
key4 = 1
key5 = 1

Thanks for your help.

Tommy

[danieljean]

hi tomda

Assuming it's normalized and you have tables looking like this:

pics 
 pic_id int 
 pic_name varchar 
 pic_text varchar 
keys 
 key_id int 
 key_description 
pic_keys 
 fk_key_id int 
 fk_pic_id int 

a query I use often would look like this:

SELECT fk_key_id, count(*) as counts 
 FROM pic_keys 
 GROUP BY fk_key_id 
 ORDER BY counts DESC 

HTH!

[tomda]

Thanks for your input Jean (or Daniel),

I have currently everything in one table.

I will work on the idea of having three tables and will try the query...

Tommy

[danieljean]

tomda- without normalized data making useful queries will be next to impossible.

Barry Wise has an excellent article about "Database Normalization And Design Techniques" on devarticles. I highly recommend reading it; it has saved me enormous amounts of time and headache medicine.