24

Right now I have a PHP file that does a MYSQL query and then counts rows like this:

$count=mysql_num_rows($result);


if ($count == 1) {
    $message = array('status' => 'ok');
} else {
    $message = array('status' => 'error');
}

This works fine but I'm trying to change all my PHP files to use PDO. So how can this be done with PDO?

user1323294
  • 632
  • 5
  • 15
  • 23

5 Answers5

24
$res = $DB->query('SELECT COUNT(*) FROM table');
$num_rows = $res->fetchColumn();

or

$res = $DB->prepare('SELECT COUNT(*) FROM table');
$res->execute();
$num_rows = $res->fetchColumn();

You can use this to ask if data exists or is selected, too:

$res = $DB->query('SELECT COUNT(*) FROM table');
$data_exists = ($res->fetchColumn() > 0) ? true : false;

Or with your variables:

$res = $DB->query('SELECT COUNT(*) FROM table');
$message = ($res->fetchColumn() > 0) ? array('status' => 'ok') : array('status' => 'error');
WolvDev
  • 3,182
  • 1
  • 17
  • 32
  • 3
    am i wrong or You will get only "first column of the next row" http://uk3.php.net/manual/en/pdostatement.fetchcolumn.php – fearis Sep 22 '14 at 22:18
  • Without a parameter in fetchColumn you will get the first column of the selected row. But we only get 1 result and that's the count. The next row means, when you didn't have fetched a row or column, you will begin with the first row of results. After you call fetchColumn again, you will get the content of the next row. – WolvDev Sep 23 '14 at 08:03
  • 2
    Yes, fetchcolumn() returns the first colmn of the row, rather, you need to use rowCount() – Asuquo12 Nov 15 '17 at 13:48
20
$stmt = $db->query('SELECT * FROM table');  
$row_count = $stmt->rowCount();  
echo $row_count.' rows selected';
Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
sekhar
  • 371
  • 2
  • 10
  • 2
    http://php.net/manual/en/pdostatement.rowcount.php says: _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 behavior is not guaranteed for all databases and should not be relied on for portable applications**._ – mickmackusa Dec 24 '18 at 06:50
6

Maybe you can use PDO's "fetchAll" method, which returns an array containing all the SELECT results. Then use "count" method to count the array's rows.

Ex:

$rows = $stmt->fetchAll();
$num_rows = count($rows);
priki
  • 709
  • 1
  • 7
  • 13
  • 1
    This could be expensive on a large dataset if all you need to know is the number of results. – Quinn Comendant Nov 26 '13 at 23:11
  • 2
    @QuinnComendant, the `rowCount()` function returns an accurate count only *after* you fetch all the rows anyway. – Bill Karwin Jan 21 '14 at 01:23
  • 1
    @BillKarwin, did you mean `mysql_num_rows()`? PDO's `rowCount()` method "returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement"—not the same thing. – Quinn Comendant Jan 21 '14 at 07:13
  • 1
    @QuinnComendant, check the code in ext/pdo_mysql/mysql_statement.c, in function `pdo_mysql_fill_stmt_from_result()`. In the MySQL driver at least, PDOStatement::rowCount() works for both affected rows and result-set rows. – Bill Karwin Jan 21 '14 at 08:08
3

If you are not using prepared statements then try:

$find = $dbh->query('SELECT count(*) from table');
if ($find->fetchColumn() > 0){
    echo 'found';
}

However, if you choose prepared statements, which i highly recommend, then:

$find = $dbh->prepare('SELECT count(*) from table');
$find->execute();
if ($find->fetchColumn() > 0){
    echo 'found';
}
shxfee
  • 5,188
  • 6
  • 31
  • 29
1

Can be like that...

$numRows = $conn->query("SELECT COUNT(*) FROM yourtable")->fetchColumn();
echo $numRows;
Jefferson Schiavetto
  • 19
  • 3