How can I count the numbers of rows that a mysql query returned? using PHP..
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";
Just use COUNT(*) - see Counting Rows in the MySQL manual. For example:
SELECT COUNT(*) FROM foo WHERE bar= 'value';
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.
In the event you have to solve the problem with simple SQL you might use a subquery.
select count(*) from (select * from foo) as x;