CODEWITHSUNDEEP
phpmysqlphpmyadmin
michael jones
michael jones

Reputation: 740

SQL Count Not Working From Inside of PHP Code

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.

Upvotes: 2

Views: 411

Answers (1)

jeroen
jeroen

Reputation: 91734

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

Upvotes: 1

Related Questions