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

Question

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

User · Answer

The basics

To get the number of matching rows in SQL you would usually use COUNT(*). For example:

SELECT COUNT(*) FROM some_table

To get that in value in PHP you need to fetch the value from the first column in the first row of the returned result. An example using PDO and mysqli is demonstrated below.

However, if you want to fetch the results and then still know how many records you fetched using PHP, you could use count() or avail of the pre-populated count in the result object if your DB API offers it e.g. mysqli's num_rows.

Using MySQLi

Using mysqli you can fetch the first row using fetch_row() and then access the 0 column, which should contain the value of COUNT(*).

// your connection code
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new \mysqli('localhost', 'dbuser', 'yourdbpassword', 'db_name');
$mysqli->set_charset('utf8mb4');

// your SQL statement
$stmt = $mysqli->prepare('SELECT COUNT(*) FROM some_table WHERE col1=?');
$stmt->bind_param('s', $someVariable);
$stmt->execute();
$result = $stmt->get_result();

// now fetch 1st column of the 1st row 
$count = $result->fetch_row()[0];

echo $count;

If you want to fetch all the rows, but still know the number of rows then you can use num_rows or count().

// your SQL statement
$stmt = $mysqli->prepare('SELECT col1, col2 FROM some_table WHERE col1=?');
$stmt->bind_param('s', $someVariable);
$stmt->execute();
$result = $stmt->get_result();

// If you want to use the results, but still know how many records were fetched
$rows = $result->fetch_all(MYSQLI_ASSOC);

echo $result->num_rows;
// or
echo count($rows);

Using PDO

Using PDO is much simpler. You can directly call fetchColumn() on the statement to get a single column value.

// your connection code
$pdo = new \PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'root', '', [
    \PDO::ATTR_EMULATE_PREPARES => false,
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION
]);

// your SQL statement
$stmt = $pdo->prepare('SELECT COUNT(*) FROM some_table WHERE col1=?');
$stmt->execute([
    $someVariable
]);

// Fetch the first column of the first row
$count = $stmt->fetchColumn();

echo $count;

Again, if you need to fetch all the rows anyway, then you can get it using count() function.

// your SQL statement
$stmt = $pdo->prepare('SELECT col1, col2 FROM some_table WHERE col1=?');
$stmt->execute([
    $someVariable
]);

// If you want to use the results, but still know how many records were fetched
$rows = $stmt->fetchAll();

echo count($rows);

PDO's statement doesn't offer pre-computed property with the number of rows fetched, but it has a method called rowCount(). This method can tell you the number of rows returned in the result, but it cannot be relied upon and it is generally not recommended to use.

User · Answer

Assuming you re using the mysql  or mysqli  functions  your question should already have been answered by others   However if you re using PDO  there is no easy function to return the number of rows retrieved by a select statement  unfortunately   You have to use count   on the resultset  after assigning it to a local variable  usually    Or if you re only interested in the number and not the data  PDOStatement  fetchColumn   on your SELECT COUNT 1     result

User · Answer

In the event you have to solve the problem with simple SQL you might use an inline view  select count    from  select   from foo  as x

User · Answer

This is not a direct answer to the question  but in practice I often want to have an estimate of the number of rows that will be in the result set  For most type of queries  MySQL s  EXPLAIN  delivers that   I for example use that to refuse to run a client query if the explain looks bad enough   Then also daily run  ANALYZE LOCAL TABLE   outside of replication  to prevent cluster locks  on your tables  on each involved MySQL server

User · Answer

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

User · Answer

If you re fetching data using Wordpress  then you can access the number of rows returned using  wpdb- num rows    wpdb- gt get results   wpdb- gt prepare  select   from mytable where foo    s    searchstring    echo  wpdb- gt num rows    If you want a specific count based on a mysql count query then you do this    numrows    wpdb- gt get var  wpdb- gt prepare  SELECT COUNT    FROM mytable where foo    s    searchstring    echo  numrows    If you re running updates or deletes then the count of rows affected is returned directly from the function call    numrowsaffected    wpdb- gt query  wpdb- gt prepare      update mytable set val  s where myid    d    valuetoupdate   myid      This applies also to  wpdb- update and  wpdb- delete

User · Answer

SELECT SQL CALC FOUND ROWS   FROM   table1 WHERE        SELECT FOUND ROWS      FOUND ROWS   must be called immediately after the query

User · Answer

gt  SELECT COUNT    AS total FROM foo WHERE bar   value

User · Answer

If your SQL query has a LIMIT clause and you want to know how many results total are in that data set you can use SQL CALC FOUND ROWS followed by SELECT FOUND ROWS    This returns the number of rows A LOT more efficiently that using COUNT    Example  straight from MySQL docs    mysql gt  SELECT SQL CALC FOUND ROWS   FROM tbl name     - gt  WHERE id  gt  100 LIMIT 10  mysql gt  SELECT FOUND ROWS

User · Answer

As it is 2015  and deprecation of mysql   functionality  this is a PDO-only visualization    lt  php        Begin Vault  this is in a vault  not actually hard-coded       host  hostname        username  GuySmiley        password  thePassword        dbname  dbname          End Vault       b   lt  br gt        try            theCategory  fruit        value from user  hard-coded here to get one in           dbh   new PDO  mysql host  host dbname  dbname charset utf8    username   password            dbh- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION               prepared statement with named placeholders          stmt    dbh- gt prepare  select id foodName from foods where category  theCat and perishable 1             stmt- gt bindParam   theCat    theCategory  PDO  PARAM STR 20            stmt- gt execute            echo  rowCount   returns     stmt- gt rowCount    b       See comments below from the Manual  varies from driver to driver           stmt    dbh- gt prepare  select count    as theCount from foods where category  theCat and perishable 1             stmt- gt bindParam   theCat    theCategory  PDO  PARAM STR 20            stmt- gt execute             row  stmt- gt fetch          fetches just one row  which is all we expect         echo  count    returns     row  theCount    b            stmt   null             PDO closes connection at end of script       catch  PDOException  e            echo  PDO Exception       e- gt getMessage            exit            gt    Schema for testing  create table foods     id int auto increment primary key      foodName varchar 100  not null      category varchar 20  not null      perishable int not null    insert foods  foodName category perishable  values    kiwi   fruit  1    ground hamburger   meat  1     canned pears   fruit  0    concord grapes   fruit  1     For my implementation  I get the output of 2 for both echos above  The purpose of the above 2 strategies is to determine if your driver implementation emits the rowCount  and if not  to seek a fall-back strategy   From the Manual on PDOStatement  rowCount      PDOStatement  rowCount   returns the number of rows affected by a   DELETE  INSERT  or UPDATE statement       For most databases  PDOStatement  rowCount   does not return the   number of rows affected by a SELECT statement  Instead  use   PDO  query   to issue a SELECT COUNT    statement with the same   predicates as your intended SELECT statement  then use   PDOStatement  fetchColumn   to retrieve the number of rows that will   be returned  Your application can then perform the correct action

User · Answer

If you want the result plus the number of rows returned do something like this   Using PHP    query    SELECT   FROM Employee    result   mysql query  query   echo  There are   mysql num rows  result    Employee s

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

The basics

Using MySQLi

Using PDO

Examples related to php

Examples related to mysql