How can I count the numbers of rows that a MySQL query returned?

by

Getting total rows in a query result…

You could just iterate the result and count them. You don’t say what language or client library you are using, but the API does provide a mysql_num_rows function which can tell you the number of rows in a result.

This is exposed in PHP, for example, as the mysqli_num_rows function. As you’ve edited the question to mention you’re using PHP, here’s a simple example using mysqli functions:

$link = mysqli_connect("localhost", "user", "password", "database");

$result = mysqli_query($link, "SELECT * FROM table1");
$num_rows = mysqli_num_rows($result);

echo "$num_rows Rows\n";

Getting a count of rows matching some criteria…

Just use COUNT(*) – see Counting Rows in the MySQL manual. For example:

SELECT COUNT(*) FROM foo WHERE bar="value";

Get total rows when LIMIT is used…

If you’d used a LIMIT clause but want to know how many rows you’d get without it, use SQL_CALC_FOUND_ROWS in your query, followed by SELECT FOUND_ROWS();

SELECT SQL_CALC_FOUND_ROWS * FROM foo
   WHERE bar="value" 
   LIMIT 10;

SELECT FOUND_ROWS();

For very large tables, this isn’t going to be particularly efficient, and you’re better off running a simpler query to obtain a count and caching it before running your queries to get pages of data.

More Related Contents:

  1. Query database then email posts from where user is subbed to
  2. Laravel – only a zero is saved to the database
  3. storing user_id in session variable
  4. I am seeing this error on submitting form Error: query was empty [closed]
  5. Reference: What is a perfect code sample using the MySQL extension? [closed]
  6. Convert from MySQL datetime to another format with PHP
  7. Correct way to use LIKE ‘%{$var}%’ with prepared statements?
  8. Passing an array to a query using a WHERE clause
  9. Whether to use “SET NAMES”
  10. Escaping single quote in PHP when inserting into MySQL [duplicate]
  11. PHP/MySQL Insert null values
  12. How can I get enum possible values in a MySQL database?
  13. Mechanisms for tracking DB schema changes [closed]
  14. Inserting data using mysqli
  15. Import file size limit in PHPMyAdmin
  16. PDO Connection Test
  17. PHP: Calling MySQL Stored Procedure with Both INPUT AND OUTPUT Parameters (NOT “INOUT”)
  18. Detect mysql update/insertion failure due to violated unique constraint
  19. PHP + MySql + Stored Procedures, how do I get access an “out” value?
  20. How do I construct a cross database query in PHP?
  21. How to bind SQL variables in PHP?
  22. Having issue with matching rows in the database
  23. PHP – Secure member-only pages with a login system
  24. MySQL ORDER BY IN()
  25. mysql – INSERT date range into date columns IF dates don’t overlap with existing ones
  26. php password_verify() hash and pass won’t match
  27. PHP mySQL – Insert new record into table with auto-increment on primary key
  28. mysql_connect VS mysql_pconnect [closed]
  29. Strange problem with the length of the field gotten from database [duplicate]
  30. mysql_fetch_array skipping first row

Leave a Comment