0

Need the number of rows of a table. 

$st = $db->query("select count(*) from images");
$total = $st->rowCount();
echo $total;

result - 1

The table images has 2430 rows.

How to get the correct number of rows?

qadenza
  • 9,025
  • 18
  • 73
  • 126

2 Answers2

5

using PDO you could use 

$result = $con->prepare("select count(*) from images"); 
$result->execute(); 
$total = $result->fetchColumn(); 
echo $total;

assuming $con is your connection to db

from PHP doc

DOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • and what is then the purpose of `rowCount()` function? – qadenza Nov 11 '19 at 20:43
  • for sure rowCount() function? return the number of rows affected by DELETE INSERT OR UPDATE https://www.php.net/manual/en/pdostatement.rowcount.php ..but not (sure) for SELECT – ScaisEdge Nov 11 '19 at 20:44
-3

Using PHP and MYSQLi functions


$query = "select * from images";
$result = mysqli_query($db, $query);

echo mysqli_num_rows($result);

This will output the total number of rows in the table

Of Course this presupposes that a connection has already been established to the database using $db

Psalms Kalu
  • 95
  • 1
  • 5
  • `rowCount()` is PDO, not `mysqli_*`. – GrumpyCrouton Nov 11 '19 at 20:40
  • 3
    This is a relatively bad answer if you have a large table with lots of columns, it's ineffective to select everything when unnecessary. – Samyok Nepal Nov 11 '19 at 20:40
  • @SamyokNepal, is then `select count(*)` also a bed solution - the same reason? – qadenza Nov 11 '19 at 20:51
  • @qadenza As I understand it, that's trivially ineffective as it is run on the SQL server instead of the PHP server (all the columns are never sent to PHP) -- for your case, it's fine. – Samyok Nepal Nov 11 '19 at 21:10
  • @SamyokNepal perhaps. Another simple hack I do is to just run select for a single column possibly the "_id" column and test how many records are returned – Psalms Kalu Jan 08 '20 at 20:15