Page 1 of 1
Search function
Posted: Fri Jan 20, 2006 1:12 pm
by MaNiAC
Say I have a table with forum posts called `posts`. That table has 'id', 'topicId' and 'body' (which contains the post).. in fact there are more, but that's not important for now.
I'm trying to create a search function... but I want to find out how often a word is used in a post/topic. Say I'm searching for 'yellow'.. that word could be in a topic once, multiple times or even multiple times per post, or not at all off course. Does any1 know how to do this in SQL?
Thx
Posted: Fri Jan 20, 2006 3:15 pm
by raghavan20
I have a forum db and I have tried on it. I able to get whether a topic has a keyword or how many topics in a forum has a keyword but I could not find any function that would count number of occurences of a substring in a field value.
see output ...
Code: Select all
mysql> select * from topics_tbl;
+----+---------+----------+----------------------------------------------+-------------------------------------
---------------------------------------------------------------------------+------------+---------------------+
| Id | ForumId | MemberId | Title | Body
| ViewsCount | Date |
+----+---------+----------+----------------------------------------------+-------------------------------------
---------------------------------------------------------------------------+------------+---------------------+
| 1 | 1 | 1 | How to use GridBagLayout??? | is GridBagLayout the best of all?
| 9 | 2006-01-12 22:01:10 |
| 2 | 2 | 2 | Is awt the ony package supported in mobiles? | It is said that Swing does not work
with mobiles or any other devices other than desktop appln...is that true? | 10 | 2006-01-12 21:57:57 |
| 3 | 1 | 2 | Which layout is the simples of all? | Is borderlayout the simples of all o
r you could think of some other efficient easy layout? | 16 | 2006-01-12 21:11:35 |
| 4 | 1 | 1 | card | kewl
| 3 | 2006-01-12 21:36:56 |
| 8 | 1 | 1 | How do you normally change screens in Swing | I used to add and remove panels to t
he JFrame | 1 | 2006-01-06 21:00:07 |
| 9 | 1 | 1 | how how how how | use use use useuse use use
| 4 | 2006-01-20 20:59:02 |
+----+---------+----------+----------------------------------------------+-------------------------------------
---------------------------------------------------------------------------+------------+---------------------+
6 rows in set (0.00 sec)
mysql> select Title like '%how%' from topics_tbl;
+--------------------+
| Title like '%how%' |
+--------------------+
| 1 |
| 0 |
| 0 |
| 0 |
| 1 |
| 1 |
+--------------------+
6 rows in set (0.00 sec)
mysql> select sum(Title like '%how%') from topics_tbl where forumId =1;
+-------------------------+
| sum(Title like '%how%') |
+-------------------------+
| 3 |
+-------------------------+
1 row in set (0.00 sec)
Posted: Sat Jan 21, 2006 4:29 pm
by MaNiAC
Yeah ok... but say I'm searching for "The Luck"... it should search for posts with "The" and for posts with "Luck".
Yet, a post can contain both strings... is it possible to count the post twice in that case?
Posted: Sat Jan 21, 2006 5:06 pm
by raghavan20
Code: Select all
mysql> select * from topics_tbl;
+----+---------+----------+----------------------------------------------+----------------------------------------------
------------------------------------------------------------------+------------+---------------------+
| Id | ForumId | MemberId | Title | Body
| ViewsCount | Date |
+----+---------+----------+----------------------------------------------+----------------------------------------------
------------------------------------------------------------------+------------+---------------------+
| 1 | 1 | 1 | How to use GridBagLayout??? | is GridBagLayout the best of all?
| 9 | 2006-01-12 22:01:10 |
| 2 | 2 | 2 | Is awt the ony package supported in mobiles? | It is said that Swing does not work with mobi
les or any other devices other than desktop appln...is that true? | 10 | 2006-01-12 21:57:57 |
| 3 | 1 | 2 | Which layout is the simples of all? | Is borderlayout the simples of all or you cou
ld think of some other efficient easy layout? | 16 | 2006-01-12 21:11:35 |
| 4 | 1 | 1 | card | kewl
| 3 | 2006-01-12 21:36:56 |
| 8 | 1 | 1 | How do you normally change screens in Swing | I used to add and remove panels to the JFrame
| 1 | 2006-01-06 21:00:07 |
| 9 | 1 | 1 | sdljf dslfj this dsjflsd luck sdfl | use use use useuse use use
| 4 | 2006-01-21 23:01:44 |
| 10 | 1 | 1 | luck 234324lj dsfl | fsdafsa
| 2 | 2006-01-21 23:05:05 |
+----+---------+----------+----------------------------------------------+----------------------------------------------
mysql> select (topics_tbl.Title like ('%This%') || topics_tbl.Title like ('%luck%')) from topics_tb
+------------------------------------------------------------------------+
| (topics_tbl.Title like ('%This%') || topics_tbl.Title like ('%luck%')) |
+------------------------------------------------------------------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 1 |
| 1 |
+------------------------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> select sum(topics_tbl.Title like ('%This%') || topics_tbl.Title like ('%luck%')) from topics_tbl;
+---------------------------------------------------------------------------+
| sum(topics_tbl.Title like ('%This%') || topics_tbl.Title like ('%luck%')) |
+---------------------------------------------------------------------------+
| 2 |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
Posted: Sun Jan 22, 2006 2:52 am
by MaNiAC
I was more trying to combine those... say
Code: Select all
SELECT topicid, count(*) FROM `posts` WHERE body like '%this%' GROUP BY topicid
topicid count(*)
1 2
3 1
4 1
6 1
7 1
8 1
9 1
10 1
11 1
Code: Select all
SELECT topicid, count(*) FROM `posts` WHERE body like '%foot%' GROUP BY topicid
topicid count(*)
1 2
3 1
7 2
And what I want is a query that combines those to... take topicid 1, it has 'this' 2 times, and foot 2 times... it should add them... so 1 would be 4.
Code: Select all
????
Result:
1 4
3 2
4 1
6 1
7 3
8 1
9 1
10 1
11 1
EDIT:
Code: Select all
SELECT topicid, count(*) FROM `posts` WHERE body like '%the%' Group By topicid
UNION
SELECT topicid, count(*) FROM `posts` WHERE body like '%foot%' GROUP BY topicid
gives me:
Code: Select all
topicid count(*)
1 3
3 2
4 1
5 2
6 1
7 2
8 1
9 1
11 1
1 2
3 1
Posted: Sun Jan 22, 2006 4:46 am
by timvw
With MySQL you can try
fulltext search.
Posted: Sun Jan 22, 2006 5:16 am
by raghavan20
Code: Select all
mysql> select * from topics_tbl
-> ;
+----+---------+----------+----------------------------------------------+----------------------------------------------
------------------------------------------------------------------+------------+---------------------+
| Id | ForumId | MemberId | Title | Body
| ViewsCount | Date |
+----+---------+----------+----------------------------------------------+----------------------------------------------
------------------------------------------------------------------+------------+---------------------+
| 1 | 1 | 1 | How to use GridBagLayout??? | is GridBagLayout the best of all?
| 9 | 2006-01-12 22:01:10 |
| 2 | 2 | 2 | Is awt the ony package supported in mobiles? | It is said that Swing does not work with mobi
les or any other devices other than desktop appln...is that true? | 10 | 2006-01-12 21:57:57 |
| 3 | 1 | 2 | Which layout is the simples of all? | Is borderlayout the simples of all or you cou
ld think of some other efficient easy layout? | 16 | 2006-01-12 21:11:35 |
| 4 | 1 | 1 | card | kewl
| 3 | 2006-01-12 21:36:56 |
| 8 | 1 | 1 | How do you normally change screens in Swing | I used to add and remove panels to the JFrame
| 1 | 2006-01-06 21:00:07 |
| 9 | 1 | 1 | sdljf dslfj this dsjflsd luck sdfl | use use use useuse use use
| 4 | 2006-01-21 23:01:44 |
| 10 | 1 | 1 | luck 234324lj dsfl | fsdafsa
| 2 | 2006-01-21 23:05:05 |
+----+---------+----------+----------------------------------------------+----------------------------------------------
------------------------------------------------------------------+------------+---------------------+
7 rows in set (0.00 sec)
mysql> select ((topics_tbl.Title like ('%This%')) + (topics_tbl.Title like ('%luck%')))
-> from topics_tbl;
+---------------------------------------------------------------------------+
| ((topics_tbl.Title like ('%This%')) + (topics_tbl.Title like ('%luck%'))) |
+---------------------------------------------------------------------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 2 |
| 1 |
+---------------------------------------------------------------------------+
7 rows in set (0.00 sec)
Posted: Sun Jan 22, 2006 7:22 am
by MaNiAC
Thx alot raghavan20.. that ought to do the trick

Posted: Sun Jan 22, 2006 7:38 am
by Chris Corbyn
Don't ignore timvw's comment though.... that's by far a more universal way to do it

Posted: Sun Jan 22, 2006 9:07 am
by MaNiAC
I know.. and it's probably alot easier to use, and I really appreciate the input.
Yet, running that on a database with over 500 000 posts seems like a bad plan... as mentioned on that site, it's a heavy script to run..
I wanted to have the other way running before discussing it with the other coders.. or before trying it out.
Posted: Sun Jan 22, 2006 11:31 am
by raghavan20
MaNiAC wrote:I know.. and it's probably alot easier to use, and I really appreciate the input.
Yet, running that on a database with over 500 000 posts seems like a bad plan... as mentioned on that site, it's a heavy script to run..
I wanted to have the other way running before discussing it with the other coders.. or before trying it out.
full text search won't work in your case because you are asking to sum up the number of the keywords found in each post.
Posted: Mon Jan 23, 2006 9:19 am
by timvw
Agreed that it doesn't solve the OP problem..
I simply mentionned fulltext search because i had the feeling (i'm becoming female with that intuition :p) he was trying to implement a "weighted" search function...