SQL Count Not Working From Inside of PHP Code

Question

I have the weirdest issue going on. COUNT() is not working from inside my PHP code, but it is working in the SQL input space in the PHPMyAdmin database. For example if I use the following query:

SELECT COUNT(*) FROM posts WHERE category = "coding"

It will return the correct results from the SQL input in the PHPmyadmin part, but from the PHP code it will always return 1. Here is the code I am using for the PHP:

function numberofposts($category, $connection) {
    $query = "SELECT COUNT(*) FROM posts WHERE category = :category";
    $params = array(':category' => $category);
    try{
        $stmt = $connection->prepare($query);
        $result = $stmt->execute($params);
    }
    catch(PDOException $ex){
        echo ("Failed to run query: " . $ex->getMessage());
    }
    return $result;
}

echo "Number of Posts: " . numberofposts("art", $connection);

What it is doing is in the first code at the top it will return the correct results, but in the PHP code it is always returning 1. Is there a problem with me PHP? Please just post if you do not understand what I am asking, or if you would like more information.

You are doing a select and you execute the statement, but you are not fetching the row with the results. — jeroen
– jeroen, Commented Apr 12, 2015 at 19:03

jeroen · Accepted Answer · 2015-04-12 21:26:37Z

1

You are doing a select and you execute the statement, but you are not fetching the row with the results.

You probably want something like:

function numberofposts($category, $connection) {
    $query = "SELECT COUNT(*) as cnt FROM posts WHERE category = :category";
                                 ^^^ for easy access
    $params = array(':category' => $category);
    try{
        $stmt = $connection->prepare($query);
        $stmt->execute($params);
        $row = $stmt->fetch();
    }
    catch(PDOException $ex){
        echo ("Failed to run query: " . $ex->getMessage());
    }
    return $row['cnt'];
    // if you don't alias the column you can do:
    // return $row[0];
}

edited Apr 12, 2015 at 21:26

answered Apr 12, 2015 at 19:07

jeroen

91.8k22 gold badges117 silver badges132 bronze badges

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

3 Comments

michael jones Over a year ago

When I try and run your code I get the error: " Fatal error: Call to a member function fetch() on a non-object". It says it is on this line: "$row = $result->fetch();". Any ideas? :( Thank You

michael jones Over a year ago

FIXED! :) The issue was $row = $result->fetch() should be $row = $stmt->fetch(). Thank you for your help! :)

jeroen Over a year ago

@michaeljones Good catch, I overlooked that.

Collectives™ on Stack Overflow

SQL Count Not Working From Inside of PHP Code

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related