Can't return correct row count from database using php pdo

Question

I am trying to return the row count from a table in my database but continue to get the wrong value. I need the row count to process subset values for pagination. There are 11 items in my table but I am only returning 1 and can't figure out why :(

My external connection file:

try {
    $pdo = new PDO('mysql:host=localhost;dbname=name', 'admin', 'password');

} catch (PDOException $e) {
    exit('Database error.');
}

My Article class:

class Article {

    public function fetch_all_articles($start, $max) {
        global $pdo;

        $query = $pdo->prepare("SELECT * FROM articles ORDER BY article_timestamp DESC LIMIT $start, $max");
        $query->execute();
        $articles = $query->fetchAll();
        $query->closeCursor();

        return $articles;
    }

    public function fetch_num_rows() {
        global $pdo;

        $query = $pdo->prepare("SELECT COUNT(*) FROM articles");
        $query->execute();
        $rowCount = $query->rowCount();
        $query->closeCursor();

        return $rowCount;
    }
}

And my index.php file:

$maxArticles = 4;                             //Show only 4 articles per page
$page = $_GET['page'] ? $_GET['page'] : 0;  //Get current page number or assign $page = 0 if no page number exists
$startRow = $page * $maxArticles;           //Get current article subset value                                          

$article = new Article;
$articles = $article->fetch_all_articles($startRow, $maxArticles);    //articles array
$numArticles = $article->fetch_num_rows();                          //number of articles
$rowCount = $article->fetch_num_rows();                             //number of articles

echo $rowCount;
echo $numArticles;

And yes I do need both $rowCount and $numArticles, they are being used for two different purposes.

Can anyone help me?

Iłya Bursov · Accepted Answer · 2013-11-12 01:34:26Z

1

you should replace

$rowCount = $query->rowCount();

with

$rowCount = $query->fetchColumn();

also, take a look at FOUND_ROWS()

there is no sense in calling fetch_num_rows 2 times, it is enough to do:

$numArticles = $rowCount = $article->fetch_num_rows();

answered Nov 12, 2013 at 1:34

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

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

Comments

Barmar · Accepted Answer · 2013-11-12 01:35:40Z

1

SELECT COUNT(*) FROM tableName just returns a single row. The data in that row is the number of rows in the table. So $query->rowCount() will always be 1. If you want the number of rows, you need to use:

$query = $pdo->prepare("SELECT COUNT(*) ct FROM articles");
$query->execute();
$rowCount = $query->fetch(PDO::FETCH_OBJ)->ct;

answered Nov 12, 2013 at 1:35

Barmar

789k57 gold badges555 silver badges669 bronze badges

Collectives™ on Stack Overflow

Can't return correct row count from database using php pdo

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related