When I make an inquiry this way the data is returned correctly (Currently 2 rows):
SELECT * FROM noticias ORDER BY data_noticia DESC LIMIT 30 OFFSET 2
But I need to know how many rows this query returns me so intuitively it would look like this:
SELECT COUNT(*) FROM noticias ORDER BY data_noticia DESC LIMIT 30 OFFSET 2
But doing so returns me empty, what alternative would I have?
Does not "return". COUNT() results in the total amount of items that he found to satisfy the established query. How much information is it? One . Only the total amount, a single value. When you say you despise a value, you are already despising everything that has resulted, so it makes sense to have this behavior. It does not make sense to use OFFSET with COUNT() .
If you want to know how many rows the query has been, you can do this:
SELECT COUNT(*) AS total FROM (SELECT 1 FROM noticias ORDER BY data_noticia DESC LIMIT 30 OFFSET 2) AS resultado;
You make your query and the result of it you make the account. Remember that it will give a value up to 30. If you have more than 30 it will always be 30.
I can not guarantee it's the best way, but the basis is this.
If you are effectively picking up the data from the first query, you can count how many rows were delivered by the database to your application directly in the application. You can get the array size or you can use the mysqli_num_rows() .
The Count function returns only one line, so you can not offset, you will lose this line on your return.
One option would be to make 2 queries, using sql_calc_found_rows and FOUND_ROWS() to get total records, as DOC of MYSQL.
SELECT sql_calc_found_rows * FROM noticias ORDER BY data_noticia DESC LIMIT 30 OFFSET 2
SELECT FOUND_ROWS()
This way you do not need to repeat the same query with COUNT(*) , there would be 2 outputs, one with array of offset records and one with array( [found_rows()] => 9 ) .