Get count of all results with LIMIT in the query

Question

SELECT `p`.`name`, `f`.*
        FROM `players` `p`, `folks` `f`
        WHERE `p`.`id` = `f`.`guid` 
        AND `f`.`deleted` = 0
        AND `first` = {$id} 
        AND `text` LIKE ?
        LIMIT 10

As you can see I'm using a LIMIT condition here. I need to count all results that will be matched without LIMIT cond.

I've tried using: FOUND_ROWS(); but I got problems with using it, as it doesn't return 'all' results anywhere.

Answer 1

The following should do :) Important is the SQL_CALC_FOUND_ROWS, otherwise FOUND_ROWS() won't work!

SELECT SQL_CALC_FOUND_ROWS `p`.`name`, `f`.*
    FROM `players` `p`, `folks` `f`
    WHERE `p`.`id` = `f`.`guid` 
    AND `f`.`deleted` = 0
    AND `first` = {$id} 
    AND `text` LIKE ?
    LIMIT 10


SELECT FOUND_ROWS();