0

I am looking to make a search tool for my site which contains several courses.

I would like basically to type a word and we will look in all the pages of course if the word is present, and then the pages will be classified according to the number of appearance of the word.

So I made a SQL table containing "coursnumero", "textcours", "liencours", and in course text I have all the text of the course in question.

I have : 

SELECT count(*) FROM `cours` WHERE `textcours` like '%super%' groupby `coursnumero`

It shows 1 but I got 2 words "super" in my sentence, how can I fix this?

CompuChip
  • 9,143
  • 4
  • 24
  • 48
Paul Tanné
  • 79
  • 10
  • 3
    https://stackoverflow.com/questions/12344795/count-the-number-of-occurrences-of-a-string-in-a-varchar-field?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – Devsi Odedra May 07 '18 at 08:01
  • Ty :) i will look to this, i search a lot of example but never saw him, ty :) sorry for the double post so :/ – Paul Tanné May 07 '18 at 08:04
  • This is not how search engines are built, this is not going to scale, it will put a huge load on your system. Instead you either use a separate search engine with its own database or maintain separate search tables where you store all words with references to your original entries. That way you do not need the expensive LIKE operator at all. – arkascha May 07 '18 at 08:09
  • ty for your advice, it's just a local website and there is not so much courses in it, thats why i just search a solution and i don't look to optimize it :) – Paul Tanné May 07 '18 at 08:12

2 Answers2

0

I think this will help you

SELECT count(*) FROM `cours` WHERE `textcours` LIKE 'super' OR `textcours` like '%,super' OR `textcours` like 'super,%' groupby `coursnumero`
Vipul Solanki
  • 313
  • 1
  • 2
  • 19
0

This is a bit tricky. For your particular case, you can use:

SELECT COUNT(*) as numrows,
       SUM( LENGTH(REPLACE(textcours, 'super', 'superx')) - LENGTH(textcours)) as nummatches
FROM cours c
WHERE textcours like '%super%'
GROUP BY coursnumero;

The difference of lengths counts the number of appearances of "super".

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786