Alternative for mysql_num_rows using PDO

Question

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?

You don't need such a function at all. See getting row count with PDO — Your Common Sense
– Your Common Sense, Commented Jan 11, 2017 at 15:46

WolvDev · Accepted Answer · 2012-07-03 06:17:10Z

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');

answered Jul 3, 2012 at 6:17

WolvDev

3,2362 gold badges19 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

fearis Over a year ago

am i wrong or You will get only "first column of the next row" uk3.php.net/manual/en/pdostatement.fetchcolumn.php

WolvDev Over a year ago

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.

Asuquo12 Over a year ago

Yes, fetchcolumn() returns the first colmn of the row, rather, you need to use rowCount()

Funk Forty Niner · Accepted Answer · 2018-01-17 15:02:39Z

20

$stmt = $db->query('SELECT * FROM table');  
$row_count = $stmt->rowCount();  
echo $row_count.' rows selected';

edited Jan 17, 2018 at 15:02

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

answered Apr 12, 2016 at 6:54

sekhar

3712 silver badges10 bronze badges

1 Comment

mickmackusa Over a year ago

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.

priki · Accepted Answer · 2013-01-26 04:18:20Z

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);

answered Jan 26, 2013 at 4:18

priki

7191 gold badge9 silver badges13 bronze badges

4 Comments

Quinn Comendant Over a year ago

This could be expensive on a large dataset if all you need to know is the number of results.

Bill Karwin Over a year ago

@QuinnComendant, the rowCount() function returns an accurate count only after you fetch all the rows anyway.

Quinn Comendant Over a year ago

@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.

Bill Karwin Over a year ago

@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.

shxfee · Accepted Answer · 2014-01-21 01:17:12Z

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';
}

edited Jan 21, 2014 at 1:17

user2486953

answered Jul 3, 2012 at 6:04

shxfee

5,3166 gold badges33 silver badges29 bronze badges

Comments

user2248078 · Accepted Answer · 2013-04-05 07:43:31Z

1

Can be like that...

$numRows = $conn->query("SELECT COUNT(*) FROM yourtable")->fetchColumn();
echo $numRows;

answered Apr 5, 2013 at 7:43

user2248078

Collectives™ on Stack Overflow

Alternative for mysql_num_rows using PDO

5 Answers 5

3 Comments

1 Comment

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

1 Comment

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related