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

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

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- gt set charset  utf8mb4        your SQL statement  stmt    mysqli- gt prepare  SELECT COUNT    FROM some table WHERE col1       stmt- gt bind param  s    someVariable    stmt- gt execute     result    stmt- gt get result        now fetch 1st column of the 1st row   count    result- gt 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- gt prepare  SELECT col1  col2 FROM some table WHERE col1       stmt- gt bind param  s    someVariable    stmt- gt execute     result    stmt- gt get result        If you want to use the results  but still know how many records were fetched  rows    result- gt fetch all MYSQLI ASSOC    echo  result- gt 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   gt  false       PDO  ATTR ERRMODE   gt   PDO  ERRMODE EXCEPTION         your SQL statement  stmt    pdo- gt prepare  SELECT COUNT    FROM some table WHERE col1       stmt- gt execute        someVariable         Fetch the first column of the first row  count    stmt- gt 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- gt prepare  SELECT col1  col2 FROM some table WHERE col1       stmt- gt execute        someVariable         If you want to use the results  but still know how many records were fetched  rows    stmt- gt 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

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

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

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

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

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

gt  SELECT COUNT    AS total FROM foo WHERE bar   value

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

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?

Examples related to php

Examples related to mysql