Search function

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
MaNiAC
Forum Newbie
Posts: 20
Joined: Fri Dec 23, 2005 4:20 am

Search function

Post 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
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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)
MaNiAC
Forum Newbie
Posts: 20
Joined: Fri Dec 23, 2005 4:20 am

Post 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?
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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)
MaNiAC
Forum Newbie
Posts: 20
Joined: Fri Dec 23, 2005 4:20 am

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

With MySQL you can try fulltext search.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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)
MaNiAC
Forum Newbie
Posts: 20
Joined: Fri Dec 23, 2005 4:20 am

Post by MaNiAC »

Thx alot raghavan20.. that ought to do the trick :D
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Don't ignore timvw's comment though.... that's by far a more universal way to do it ;)
MaNiAC
Forum Newbie
Posts: 20
Joined: Fri Dec 23, 2005 4:20 am

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
Post Reply