Search function
Moderator: General Moderators
Search function
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
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
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
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 ...
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)- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
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)I was more trying to combine those... say
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.
EDIT:
gives me:
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 1Code: Select all
SELECT topicid, count(*) FROM `posts` WHERE body like '%foot%' GROUP BY topicid
topicid count(*)
1 2
3 1
7 2Code: Select all
????
Result:
1 4
3 2
4 1
6 1
7 3
8 1
9 1
10 1
11 1EDIT:
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 topicidCode: 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 1With MySQL you can try fulltext search.
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
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)- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
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.
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.
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
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.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.