4WebHelp
 FAQ  •  Search  •  User Groups  •  Forum Admins  •  Smilies List  •  Statistics  •  Rules   •  Login   •  Register
Toggle Navigation Menu

 Keyword's plurality in MYSQL query
Post New TopicReply to Topic
View Previous Topic Print this topic View Next Topic
Author Message
tomda
Junior WebHelper
Junior WebHelper


Joined: 23 Jan 2004
Posts: 6
Location: Nairobi

PostPosted: Fri Jan 23, 2004 8:17 am (13 years, 10 months ago) Reply with QuoteBack to Top

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
OfflineView User's ProfileFind all posts by tomdaSend Personal Message
OllieMaitland
Junior WebHelper
Junior WebHelper


Joined: 06 Dec 2003
Posts: 25

PostPosted: Sat Jan 24, 2004 11:29 am (13 years, 10 months ago) Reply with QuoteBack to Top

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...
OfflineView User's ProfileFind all posts by OllieMaitlandSend Personal MessageSend email
tomda
Junior WebHelper
Junior WebHelper


Joined: 23 Jan 2004
Posts: 6
Location: Nairobi

PostPosted: Mon Jan 26, 2004 5:53 am (13 years, 10 months ago) Reply with QuoteBack to Top

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
OfflineView User's ProfileFind all posts by tomdaSend Personal Message
OllieMaitland
Junior WebHelper
Junior WebHelper


Joined: 06 Dec 2003
Posts: 25

PostPosted: Mon Jan 26, 2004 2:22 pm (13 years, 10 months ago) Reply with QuoteBack to Top

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');
OfflineView User's ProfileFind all posts by OllieMaitlandSend Personal MessageSend email
tomda
Junior WebHelper
Junior WebHelper


Joined: 23 Jan 2004
Posts: 6
Location: Nairobi

PostPosted: Tue Jan 27, 2004 8:44 am (13 years, 10 months ago) Reply with QuoteBack to Top

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
OfflineView User's ProfileFind all posts by tomdaSend Personal Message
Display posts from previous:      
Post New TopicReply to Topic
View Previous Topic Print this topic View Next Topic


 Jump to:   




You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot edit your posts in this forum.
You cannot delete your posts in this forum.
You cannot vote in polls in this forum.


Page generation time: 0.056341 seconds :: 17 queries executed :: All Times are GMT
Powered by phpBB 2.0 © 2001, 2002 phpBB Group :: Based on an FI Theme