statistical process for art project - not sure what to call this

php, mysql, stats

[j milo taylor]

artist_id keyword_id
1 3
1 2
1 14
2 3
2 7
2 8
2 9
3 2
3 54
3 14
4 54
4 10
5 9
5 54

how can i count(sum) the number of instances of each keyword_id, and output the number of times each occurs with another when associated with a given artist_id?

i.e
3= 1 instance
14= 2 instances
etc
54= 3 instances

and
54 with 10 = 1 instances
54 with 9 = 1 instances
etc

eventually
so
54, 10, 9, 106 = 2 instances
3, 7, 8, 19 = 14 instances

i am using mysql and php for a digital sound art project

any ideas?
thanks

[PHP_Chimp]

Could you not use the SQL to count those results for you?

This is getting a bit beyond my SQL skills but here is an attempt for you -

"SELECT artist_id, keyword_id, COUNT(*) FROM table tbl, table tbl1 WHERE tbl.artist_id = tbl1.artist_id AND tbl.keyword_id = tbl1.keyword_id GROUP BY artist_id;"

As (hopefully) this will return a count where the artist and keyword are the same.