Keyword's plurality in MYSQL query

Posted: Fri Jan 23, 2004 8:17 am (20 years, 2 months ago)

Hi everyone,

First post in this forum, just login. I am looking for a friendly place to post and respond to posts and therefore continuously learning through active participation. Hope this is the place to be (I am tired to move from one forum to another).

Here is my question :
I have a picture database 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

Result for the two items above should be :
key1 = 1
key2 = 2
key3 = 2
key4 = 1
key5 = 1

Thanks for your help
Very Happy

Tommy

Junior WebHelper Joined: 06 Dec 2003 Posts: 25

Hey -

In theory this would be quite easy but it can also be quite difficult depends exactly on what you wnat to do...

Can the key words occur more than once in a row?

Do you want to search for a predefined set of keywordsor do you want to return an array of all the different words and how often they occur?

...if you want to use the keywords as search criteria, mysql has a full text searching built into it now...

Posted: Mon Jan 26, 2004 5:53 am (20 years, 2 months ago)

Hi Ollie,

Sorry, I am always out during the WE.

Keywords occur only once in a row.
Indeed, I want to return an array of all keywords and how often they occur (search is done in multiple rows)

Thanks

Tommy

Junior WebHelper Joined: 06 Dec 2003 Posts: 25

Argh - you've got me thinking here!

I don't think there is a convenient way to way to query your current table structure as mysql deals with rows and record, not rows, records and commas...

With this in mind I would convert your current table structure to something like

pic | keyword
---------------
pic1 | key1
pic1 | key2
pic1 | key3
pic1 | key4
pic2 | key2
pic2 | key3
pic2 | key5

...this is easily done... then from here you can do something like this:

SELECT keyword, COUNT( pics ) AS occurance FROM pics GROUP BY keyword

Hope this helps.

--- SQL Dump for testing ---

Code:

#
# Table structure for table `pics`
#

CREATE TABLE pics (
id int(11) NOT NULL auto_increment,
pics varchar(10) NOT NULL default '',
keyword text,
PRIMARY KEY (id)
) TYPE=MyISAM;

#
# Dumping data for table `pics`
#

INSERT INTO pics VALUES (1, '1', 'key1');
INSERT INTO pics VALUES (2, '1', 'key2');
INSERT INTO pics VALUES (3, '1', 'key3');
INSERT INTO pics VALUES (4, '1', 'key4');
INSERT INTO pics VALUES (5, '2', 'key2');
INSERT INTO pics VALUES (6, '2', 'key3');
INSERT INTO pics VALUES (7, '2', 'key5');

Posted: Tue Jan 27, 2004 8:44 am (20 years, 2 months ago)

Thanks for trying Ollie,

But it is a bit too late for me to reshufle the all database. I will try to stick to multiple keywords per item.

I though I could explode the data per item and then using variable to count the number of time the word appears using if/elseif statements.

Explode -> key1
if key1 exists then add 1 to the variable countkey1
if not create the variable count key1

then key2...
ect...

Tommy