Get number of records with SQL while using LIMIT and OFFSET

I’m trying to implement pages into my webpage. I have got it so that a user can put the page number they want into the url bar that they want to go to. I did this by using that number in the URL bar and multiplying it by 10 for the OFFSET parameter in the SQL statement to get 10 records for each page.

But i need to know how many records that could be returned by that SQL statement if i removed the LIMIT and OFFSET parameters. Is this possible within the SQL statement or will i have to request every record from the database and count the length on every page? thanks.

EDIT:

I want the amount of records that this statement COULD return if i didn’t use LIMIT or OFFSET

SELECT nfc_film.*, nfc_category.name FROM nfc_film
        JOIN nfc_film_category
          ON nfc_film.film_id = nfc_film_category.film_id
        JOIN nfc_category
          ON nfc_category.category_id = nfc_film_category.category_id
        WHERE title
          LIKE :searchterm OR nfc_category.name = :category
        LIMIT 10
        OFFSET :page

Answer

To get the number of rows returned by the current query, since you tagged php with PDO, you can use fetchAll() along with count()

In example :

$sth = $dbh->prepare("<your query>");
$sth->execute();

$result = $sth->fetchAll();
$numberOfRows = count($result);

---

To get the total number of rows of your table, but still using LIMIT, you can either

Count that in a separate query :

SELECT COUNT(*)
FROM nfc_film
JOIN nfc_film_category
ON nfc_film.film_id = nfc_film_category.film_id
JOIN nfc_category
ON nfc_category.category_id = nfc_film_category.category_id
WHERE title LIKE :searchterm OR nfc_category.name = :category;

Or do it in a single query, using a GROUP BY (this will prevent you the bad practice to use a *)

--                          v------------- Random datas ------------------------v
SELECT COUNT(*) AS "Count", nfc_film.id, nfc_film.name, nfc_film.otherInformation, nfc_category.name FROM nfc_film
JOIN nfc_film_category
ON nfc_film.film_id = nfc_film_category.film_id
JOIN nfc_category
ON nfc_category.category_id = nfc_film_category.category_id
WHERE title
LIKE :searchterm OR nfc_category.name = :category
GROUP BY nfc_film.id, nfc_film.name, nfc_film.otherInformation, nfc_category.name
LIMIT 10
OFFSET :page