I suggest writing a mysql function to count the number of words and
another to return the nth word from a string, join your table to a table
that just contains the numbers from 1 to the highest n you'll need, then
do this:
create table tbl_count (count_id int);
you'll need to fill tbl_count yourself. And make count_id the primary key.
select word(search_string,count_id,' ') as one_word, count(*) as
word_count from tbl_search_terms, tbl_count where tbl_count.id <=
words(search_string,' ') group by word(search_string,count_id,' ');
I've provided my VB versions of the word and words functions as well,
but I leave them to you to translate into SQL.
Function word(s As Variant, w As Integer, Optional delimiter As String =
" ") As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
s = "" & s
If w > 0 Then
For i = 1 To w - 1
k = InStr(s, delimiter)
If k > 0 Then
s = Mid(s, k + Len(delimiter))
Else
s = ""
End If
Next i
k = InStr(s, delimiter)
If k > 0 Then
s = Left(s, k - 1)
End If
End If
word = s
End Function
Function words(s As Variant, Optional delimiter As String = " ") As Integer
Dim i As Integer
Dim j As Integer
s = Trim("" & s)
i = InStr(s, delimiter)
If i > 0 Then j = 1
While (i > 0)
j = j + 1
i = InStr(i + 1, s, delimiter)
Wend
words = j
End Function
Giles Smith wrote:
>
> Further to my previous answer, I don't think that this is possible using
> SQL alone.
>
> I think your only options are to manually count using php and an array,
> or to consider normalising your database further.
>
> A search engine I built recently stores searched terms as well as the
> entire phrase using a foreign key.
>
> eg I would have 2 tables "phrase" to many "term"
>
> phrase
> =======
> phrase_id
> phrase
>
> term
> =======
> term_id
> term
> phrase_id
>
> where phrase_id is the foreign key in term.
>
> Each term is then stored individually allowing you to count them.
>
> Hope that helps.
>
> Giles
>
> Imran wrote:
> >
> >
> > Friends,
> >
> > I'm looking for a script or (ideally) a mysql query that could show
> the most
> > popular keyword searched. The table below stores all the keywords
> searched
> > from the website. Now the admin wants to see which keyword is "The Most
> > Popular" being searched by users. Mr.Bean or Bean or Bean Joker
> should show
> > 3 occurences with "The Most Popular" keyword.
> >
> > Plz Note: Admin will not query (or search) it using forms etc. He simply
> > wants to see the table with top occurence.
> >
> > *tbl_keywordsearch*
> >
> > **
> > *search_id | keyword_searched*
> > + = = = = = = = = = = =
> > 1 | Mr. Bean
> > 2 | Bean
> > 3 | Bush
> > 4 | George Bush
> > 5 | Bean Joker
> > + = = = = = = = = = = =
> >
> > Thanks,
> > Quest
> >
> > [Non-text portions of this message have been removed]
> >
> >
>
>
