Number of result in each page

[nodi]

Hi all

i'm searching in mysql database and i want to know number of search word in each topic , is that possible ?

Thanks

[GreyBoy]

Im not sure if this will help:

SELECT count(*) FROM table

or you can use:
$sql = //your statement
$result = mysql_query($sql, $connect);

$srch_rslt_num = mysql_num_rows($result)

[nodi]

your code will return total number of topics , but i want number of match in each topic : (

[majiclab]

The easiest way to do this would be using PHP.

Do your basic SQL query:

SELECT * FROM topic WHERE text LIKE '%whatever%'

Then, when looping through your results, do something like this:

<?= substr_count($result[ 'text' ], 'whatever') ?>

That will print out the number of times 'whatever' is in the topic text. Unfortunately MySQL does not seem to have a similar function... 🙁

So, unfortunately, there won't be an easy way to sort the results by the number of times the text is found in the topic.

If someone knows of a way to do this in MySQL, please share, as I don't think it is currently possible... !

More on substr_count():

http://ca.php.net/manual/en/function.substr-count.php

NOTE: If what you are trying to do is sort the results by the number of times that word is in the field, consider using FULLTEXT indexing on the table. Then you could do the following:

SELECT * FROM topic WHERE MATCH(text) AGAINST ('search for this')

would automatically sort the results by relevance. If you have MySQL 4, you can do even fancier searches using boolean searches such as '+yes -no >good <bad'.

For more on FULLTEXT indexing:

http://www.mysql.com/doc/en/Fulltext_Search.html

Hope that helps.

-Percy