COUNT of sql SELECT query with LIMIT in mySQL

Question

In php-mysql, how to count number of rows returned within a SELECT query?
let's think of the SELECT query something like this:

$q = "SELECT * from users LIMIT 20,60"

But if total number of records was just for example 55, The results is users from 20 to 55 with a total number of 35 not 40!
How can I have the number 35 without querying the DB twice?
now I am doing like this:

$q = "SELECT COUNT(*) from users LIMIT 20, 60";
$result = mysqli_query($db_connection, $1);
$count = mysqli_fetch_row($result);
$count = $count[0];

But here I am querying the DB twice and I think this is not so good.
I know that I could have the total number of rows without considering the LIMIT, using SQL_CALC_FOUND_ROWS and FOUND_ROWS(), but this is not my case, as I want to consider the LIMIT.
Thanks!

aynber · Accepted Answer · 2013-06-21 16:24:48Z

4

You want to num_rows

$count = $result->num_rows;

answered Jun 21, 2013 at 16:24

aynber

23.2k9 gold badges57 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peyman Mohamadpour Over a year ago

Thanks! I will check it and accept you answer. but is this the same az mysqli_num_rows($result); ?

aynber Over a year ago

Yup. According to the page: Object oriented style -- int $mysqli_result->num_rows; Procedural style -- int mysqli_num_rows ( mysqli_result $result )

Nisarg · Accepted Answer · 2013-06-21 16:54:19Z

0

you can use

$q = "SELECT COUNT(*) from users LIMIT 20, 60";
$result = mysqli_query($db_connection, $1);
$count = mysqli_num_rows($result);

mysqli_num_rows() returns no. of rows

edited Jun 21, 2013 at 16:54

answered Jun 21, 2013 at 16:38

Nisarg

3,2975 gold badges34 silver badges55 bronze badges

2 Comments

Peyman Mohamadpour Over a year ago

Thanks! does your answer the same result as anyber's answer? I mean $result->num_rows.

Nisarg Over a year ago

i think in your case my answer is perfect, because anyber's answer is for Object oriented query

Collectives™ on Stack Overflow

COUNT of sql SELECT query with LIMIT in mySQL

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related