4
When I make a query this way are returned me the data correctly(At the moment 2 lines):
SELECT * FROM noticias ORDER BY data_noticia DESC LIMIT 30 OFFSET 2
But I need to know the number of lines that this query returns so intuitively would do so:
SELECT COUNT(*) FROM noticias ORDER BY data_noticia DESC LIMIT 30 OFFSET 2
But by doing so it returns to me empty, what alternative would I have?
5
No "returns". COUNT() results in the total amount of items he has found that satisfy the established query. How much information results? One. Just the total amount, a single amount. When you say despise a value, you are already despising everything that has worked, so it makes sense to have this behavior. It makes no sense to use OFFSET with COUNT().
If you want to know how many lines a query resulted can do this:
SELECT COUNT(*) AS total FROM (SELECT 1 FROM noticias ORDER BY data_noticia DESC LIMIT 30 OFFSET 2) AS resultado;
I put in the Github for future reference.
You make your query and the result of it you make the account. Remembering that it will give a value up to 30. If you have more than 30 will always be 30.
I can’t guarantee it’s the best way, but the basis is this.
If you are actually taking the data of the first query then you can count how many lines were delivered by the database for your direct application in the application. You can take the size of array obtained or can use the function mysqli_num_rows().
It lacked the alternative I would have ?
Alternative to?
He wants to do a Count on top of the select with offset that he did.
2
The Count function returns only one line, so there is no way to apply offset, you will lose this line on your return.
As I do then ?
The alternative is to "select from select": Select count(*) from SELECT * FROM noticias ORDER BY data_noticia DESC LIMIT 30 OFFSET 2;
Tips: use count(1) (gives in the same and can be even more performatic if there is no optimization) and avoid doing select *, prefer to name the fields you want to bring.
1
One option would be to make 2 queries, using sql_calc_found_rows and FOUND_ROWS() to pick up the total of records, as per the 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 don’t need to repeat the same query with COUNT(*), would be 2 outputs, one with array of records with offset and the other with array( [found_rows()] => 9 ).
I did a quick test, I don’t know if it’s right, but it came back 0.
@Maniero are 2 separate queries; I tested here before posting and came out the expected result. I have a class for my BD - in most queries I need paging - so the class itself does the calculation using this method I posted.
Browser other questions tagged php mysql sql
You are not signed in. Login or sign up in order to post.
then just make two querys, one to count and the other to return the data.
– Guilherme Nascimento
Take this * out of the query, put only the columns you will actually use, if you want to optimize.
– Bacco
First of all, do you want to know the total of the results, or the total only after applying the offset? For each case the solution is different.
– Bacco