CODEWITHSUNDEEP
phpmysqlooppdo
Nathanael
Nathanael

Reputation: 7173

MySQL PDO Count Rows - Can I make this more efficient?

So, I've been learning PDO. So far, I am not at all impressed, honestly, due to the large amount of code needed to do small tasks. However, I am willing to convert nonetheless if I can get my code to be efficient and reusable.

My question is this: can I make this code any more efficient? By efficient, I mean both A) take up less lines, and B) run faster. I am worried that I am going about this all wrong. However, due to the lack of a num_rows() function, I can't think of a better way.

try
{
    $sth = $dbh->prepare("SELECT * FROM table_name");
    $sth->execute();

    if (count($result = $sth->fetchAll()))
    {
        foreach ($result as $value)
        {
            // Rows returned! Loop through them.
        }
    }
    else
    {
        // No rows returned!
    }
}
catch (PDOException $e)
{
    // Exception!
}

Is this written properly?

Upvotes: 2

Views: 2557

Answers (3)

Nathanael
Nathanael

Reputation: 7173

As far as my research has shown, no. There is no way to rewrite this code more concisely or logically--the way it stands is entirely optimized. :) It's easy to use, so that's definitely not a bad thing!

Upvotes: 2

Alon Adler
Alon Adler

Reputation: 4024

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

$sql = "SELECT COUNT(*) FROM table_name";
if ($res = $conn->query($sql))
{
/* Check the number of rows that match the SELECT statement */
$res->fetchColumn(); //This will give you the number of rows selected//
}

Make a general function that does that, and all you need to do is send a select count based on your needs. You can make in more general by dividing the $select to more variables.

function countRows($select)
{
    if ($res = $conn->query($select))
    {
    /* Check the number of rows that match the SELECT statement */
    return $res->fetchColumn(); //This will give you the number of rows selected//
    }
}

Upvotes: 1

Ananth
Ananth

Reputation: 4397

No, you need to use PDO's rowCount method.

try
{
    $sth = $dbh->prepare("SELECT * FROM table_name");
    $sth->execute();

    if ($sth->rowCount() > 0)
    {
        while ($result = $sth->fetch())
        {
            // Rows returned! Loop through them.
        }
    }
    else
    {
        // No rows returned!
    }
}
catch (PDOException $e)
{
    // Exception!
}

Upvotes: -1

Related Questions