count row numbers by using single sql statement

Question

I have a query like SELECT * table from etc order by something LIMIT 10 but in the same time I also want to know the TOTAL row results in the table. Do I need to do another sql?

Answer 1

If you want to see total rows return by the query you use mysqli_num_rows($sql)

$sql = mysqli_query($con,"SELECT * from etc order by something LIMIT 10");

$total_rows = @mysqli_num_rows($sql);

Edit :- If you want total of specifc coloumn say 'id' you can do like this :-

$total = 0;

while($result = mysqli_fetch_assoc($sql))
{

    $total = $total + $result['id'];

}

echo $total;

Answer 2

I had the same question, I had to do another sql... Here is what I used to count the numbers of users (userid>16 was due to sueprusers and deleted accounts):

SELECT
COUNT(*) AS "Number of users"
FROM 
Users
where  userid>16

Answer 3

If you are using mysql use this:

    $conn = mysql_connect('localhost', 'usename', 'password');
    if (!$conn) {
        die('Could not connect: ' . mysql_error());
    }
    mysql_select_db('database_name');


    mysql_query('SELECT * table from etc order by something LIMIT 10');
    echo mysql_affected_rows();