Row count with PDO

Question

There are many conflicting statements around  What is the best way to get the row count using PDO in PHP  Before using PDO  I just simply used mysql num rows  fetchAll is something I won t want because I may sometimes be dealing with large datasets  so not good for my use  Do you have any suggestions

User · Answer

I tried  count    stmt- gt rowCount    with Oracle 11 2 and it did not work  I decided to used a for loop as show below        count             stmt     conn- gt prepare  sql        stmt- gt execute       echo   lt table border  1  gt  n      while  row    stmt- gt fetch PDO  FETCH OBJ              count            echo   lt tr gt  n       foreach   row as  item        echo   lt td class  td2  gt     item     null   htmlentities  item  ENT QUOTES    amp nbsp      lt  td gt  n               foreach ends             while ends         echo   lt  table gt  n            echo   no of rows      oci num rows  stmt            equivalent in pdo  prepare statement        echo  no of rows     count

User · Answer

I ended up using this    result    db- gt query  query - gt fetchAll     if  count  result   gt  0        foreach   result as  row            echo  row  blah       lt br   gt            else       echo   lt p gt Nothing matched your query  lt  p gt

User · Answer

This is an old post  but getting frustrated looking for alternatives  It is super unfortunate that PDO lacks this feature  especially as PHP and MySQL tend to go hand in hand    There is an unfortunate flaw in using fetchColumn   as you can no longer use that result set  effectively  as the fetchColumn   moves the needle to the next row  So for example  if you have a result similar to    Fruit- Banana Fruit- Apple Fruit- Orange   If you use fetchColumn   you can find out that there are 3 fruits returned  but if you now loop through the result  you only have two columns  The price of fetchColumn   is the loss of the first column of results just to find out how many rows were returned  That leads to sloppy coding  and totally error ridden results if implemented    So now  using fetchColumn   you have to implement and entirely new call and MySQL query just to get a fresh working result set   which hopefully hasn t changed since your last query   I know  unlikely  but it can happen  Also  the overhead of dual queries on all row count validation  Which for this example is small  but parsing 2 million rows on a joined query  not a pleasant price to pay    I love PHP and support everyone involved in its development as well as the community at large using PHP on a daily basis  but really hope this is addressed in future releases  This is  really  my only complaint with PHP PDO  which otherwise is a great class

User · Answer

To use variables within a query you have to use bindValue   or bindParam    And do not concatenate the variables with      variable       statement    SELECT count account id  FROM account                   WHERE email     AND is email confirmed     preparedStatement    this- gt postgreSqlHandler- gt prepare  statement    preparedStatement- gt bindValue 1   account- gt getEmail      preparedStatement- gt execute     numberRows   preparedStatement- gt fetchColumn      GL

User · Answer

Have a look at this link  http   php net manual en pdostatement rowcount php It is not recommended to use rowCount   in SELECT statements

User · Answer

Here s a custom-made extension of the PDO class  with a helper function to retrieve the number of rows included by the last query s  WHERE  criteria   You may need to add more  handlers   though  depending on what commands you use  Right now it only works for queries that use  FROM   or  UPDATE     class PDO V extends PDO       private  lastQuery   null       public function query  query                 this- gt lastQuery    query              return parent  query  query             public function getLastQueryRowCount                  lastQuery    this- gt lastQuery           commandBeforeTableName   null          if  strpos  lastQuery   FROM       false               commandBeforeTableName    FROM           if  strpos  lastQuery   UPDATE       false               commandBeforeTableName    UPDATE             after   substr  lastQuery  strpos  lastQuery   commandBeforeTableName     strlen  commandBeforeTableName    1             table   substr  after  0  strpos  after                   wherePart   substr  lastQuery  strpos  lastQuery   WHERE               result   parent  query  SELECT COUNT    FROM  table      wherePart           if   result    null              return 0          return  result- gt fetchColumn

User · Answer

As it often happens  this question is confusing as hell  People are coming here having two different tasks in mind   They need to know how many rows in the table They need to know whether a query returned any rows  That s two absolutely different tasks that have nothing in common and cannot be solved by the same function  Ironically  for neither of them the actual PDOStatement  rowCount   function has to be used  Let s see why Counting rows in the table  Before using PDO I just simply used mysql num rows     Means you already did it wrong  Using mysql num rows   or rowCount   to count the number of rows in the table is a real disaster in terms of consuming the server resources  A database has to read all the rows from the disk  consume the memory on the database server  then send all this heap of data to PHP  consuming PHP process  memory as well  burdening your server with absolute no reason  Besides  selecting rows only to count them simply makes no sense  A count    query has to be run instead  The database will count the records out of the index  without reading the actual rows and then only one row returned  For this purpose the code suggested in the accepted answer is fair  save for the fact it won t be an  quot extra quot  query but the only query to run  Counting the number rows returned  The second use case is not as disastrous as rather pointless  in case you need to know whether your query returned any data  you always have the data itself  Say  if you are selecting only one row  All right  you can use the fetched row as a flag   stmt- gt execute     row    stmt- gt fetch    if    row       here  as simple as that     echo  No data found      In case you need to get many rows  then you can use fetchAll     fetchAll   is something I won t want as I may sometimes be dealing with large datasets  Yes of course  for the first use case it would be twice as bad  But as we learned already  just don t select the rows only to count them  neither with rowCount   nor fetchAll    But in case you are going to actually use the rows selected  there is nothing wrong in using fetchAll    Remember that in a web application you should never select a huge amount of rows  Only rows that will be actually used on a web page should be selected  hence you ve got to use LIMIT  WHERE or a similar clause in your SQL  And for such a moderate amount of data it s all right to use fetchAll    And again  just use this function s result in the condition   stmt- gt execute     data    stmt- gt fetchAll    if    data       again  no rowCount   is needed      echo  No data found      And of course it will be absolute madness to run an extra query only to tell whether your other query returned any rows  as it suggested in the two top answers  Counting the number of rows in a large resultset In such a rare case when you need to select a real huge amount of rows  in a console application for example   you have to use an unbuffered query  in order to reduce the amount of memory used  But this is the actual case when rowCount   won t be available  thus there is no use for this function as well  Hence  that s the only use case when you may possibly need to run an extra query  in case you d need to know a close estimate for the number of rows selected

User · Answer

Answering this because I trapped myself with it by now knowing this and maybe it will be useful    Keep in mind that you cant fetch results twice  You have to save fetch result into array  get row count by count  array   and output results with foreach  For example    query    your query here    STH    DBH- gt prepare  query    STH- gt execute     rows    STH- gt fetchAll      all your results is in  rows array  STH- gt setFetchMode PDO  FETCH ASSOC              if  count  rows   gt  0                     foreach   rows as  row              output your rows

User · Answer

As I wrote previously in an answer to a similar question  the only reason mysql num rows   worked is because it was internally fetching all the rows to give you that information  even if it didn t seem like it to you  So in PDO  your options are   Use PDO s fetchAll   function to fetch all the rows into an array  then use count   on it  Do an extra query to SELECT COUNT     as karim79 suggested  Use MySQL s FOUND ROWS   function UNLESS the query had SQL CALC FOUND ROWS or a LIMIT clause  in which case the number of rows that were returned by the query and the number returned by FOUND ROWS   may differ   However  this function is deprecated and will be removed in the future

User · Answer

function count x  connect        query      SELECT   FROM tbl WHERE id    0         statement    connect- gt prepare  query     statement- gt execute        total rows    statement- gt rowCount       return  total rows

User · Answer

lt table gt         lt thead gt              lt tr gt                   lt th gt Sn  lt  th gt                   lt th gt Name lt  th gt              lt  tr gt         lt  thead gt         lt tbody gt   lt  php       i 0        statement    db- gt prepare  SELECT   FROM tbl user ORDER BY name ASC          statement- gt execute          result    statement- gt fetchColumn         foreach  result as  row             i          gt           lt tr gt            lt td gt  lt  php echo  i    gt  lt  td gt            lt td gt  lt  php echo  row  name      gt  lt  td gt         lt  tr gt        lt  php                    gt        lt  tbody gt   lt  table gt

User · Answer

If you just want to get a count of rows  not the data  ie  using COUNT    in a prepared statement then all you need to do is retrieve the result and read the value    sql    SELECT count    FROM  table  WHERE foo   bar    statement    con- gt prepare  sql     statement- gt execute      count    statement- gt fetch PDO  FETCH NUM      Return array indexed by column number return reset  count      Resets array cursor and returns first value  the count    Actually retrieving all the rows  data  to perform a simple count is a waste of resources  If the result set is large your server may choke on it

User · Answer

For straight queries where I want a specific row  and want to know if it was found  I use something like   function fetchSpecificRow  amp  myRecord         myRecord   array         myQuery    some sql           stmt    this- gt prepare  myQuery        stmt- gt execute array  parm1   parm2             if   myRecord    stmt- gt fetch PDO  FETCH ASSOC   return 0      return  myErrNum

User · Answer

A quick one liner to get the first entry returned   This is nice for very basic queries    lt  php  count   current  db- gt query  select count    from table  - gt fetch       gt    Reference

User · Answer

When it is matter of mysql how to count or get how many rows in a table with PHP PDO I use this     count total number of rows  query    SELECT COUNT    as total rows FROM sometable    stmt    con- gt prepare  query       execute query  stmt- gt execute        get total rows  row    stmt- gt fetch PDO  FETCH ASSOC    total rows    row  total rows      credits goes to Mike   codeofaninja com

User · Answer

sql    SELECT count    FROM  table  WHERE foo         result    con- gt prepare  sql     result- gt execute   bar      number of rows    result- gt fetchColumn       Not the most elegant way to do it  plus it involves an extra query   PDO has PDOStatement  rowCount    which apparently does not work in MySql  What a pain   From the PDO Doc      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    EDIT  The above code example uses a prepared statement  which is in many cases is probably unnecessary for the purpose of counting rows  so    nRows    pdo- gt query  select count    from blah  - gt fetchColumn     echo  nRows

User · Answer

when you make a COUNT    in your mysql statement like in   q    db- gt query  SELECT COUNT    FROM          your mysql query is already counting the number of result why counting again in php  to get the result of your mysql   q    db- gt query  SELECT COUNT    as counted FROM         nb    q- gt fetch PDO  FETCH OBJ    nb    nb- gt counted    and  nb will contain the integer you have counted with  your mysql statement a bit long to write but fast to execute  Edit  sorry for the wrong post but as some example show query with count in  I was suggesting using the mysql result  but if you don t use the count in sql fetchAll   is efficient  if you save the result in a variable you won t loose a line    data    dbh- gt query  SELECT   FROM         table    data- gt fetchAll PDO  FETCH OBJ     count  table  will return the number of row and you can still use the result after like  row    table 0   or using a foreach  foreach  table as  row     print  row- gt id

User · Answer

fetchColumn    used if want to get count of record  effisien    sql      SELECT COUNT    FROM fruit WHERE calories  gt  100    res      conn- gt query  sql    count    res- gt fetchColumn       ex   2   query    used if want to retrieve data and count of record  options    sql    SELECT   FROM fruit WHERE calories  gt  100    res    conn- gt query  sql    if    res- gt rowCount    gt  0         foreach    res as  row             print  Name    row  NAME     lt br   gt             else       print  No rows matched the query        PDOStatement  rowCount

User · Answer

This is super late  but I ran into the problem and I do this   function countAll  table       dbh   dbConnect        sql    select   from   table         stmt    dbh- gt prepare  sql       try    stmt- gt execute         catch PDOException  e  echo  e- gt getMessage      return  stmt- gt rowCount      It s really simple  and easy

User · Answer

You can combine the best method into one line or function  and have the new query auto-generated for you   function getRowCount  q        global  db      return  db- gt query preg replace   SELECT  A-Za-z    FROM  i   SELECT count    FROM    q  - gt fetchColumn        numRows   getRowCount  query

User · Answer

This post is old but Getting row count in php with PDO is simple   stmt    db- gt query  SELECT   FROM table     row count    stmt- gt rowCount

User · Answer

There is a simple solution  If you use PDO connect to your DB like this   try        handler   new PDO  mysql host localhost dbname name of your db    your login    your password          handler - gt  setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION          catch  PDOException  e           echo  e- gt getMessage        Then  the query to DB will be    query    handler- gt query  SELECT id FROM your table WHERE          And finally  to count the rows matching your query write like this    amountOfRows    query- gt rowcount

User · Answer

Use parameter   array PDO  ATTR CURSOR   gt  PDO  CURSOR SCROLL   else show -1   Usen parametro  array PDO  ATTR CURSOR   gt  PDO  CURSOR SCROLL   sin ello sale -1   example    res1    mdb2- gt prepare  SELECT clave FROM  tb WHERE id usuario   username  AND activo 1 and id tipo usuario  4    array PDO  ATTR CURSOR   gt  PDO  CURSOR SCROLL     res1- gt execute      count  res1- gt rowCount    echo  count

[php] Row count with PDO

Examples related to php

Examples related to mysql

Examples related to pdo