How to return a COUNT(*) results from a query PHP

Question

public function votesThisMonth()
{
    $this->query = $this->pdo->prepare
    ("
        SELECT
            COUNT(*) AS num_votes
        FROM
            votes
        WHERE
            YEAR(year) = YEAR(CURRENT_DATE)
        AND MONTH(month) = MONTH(CURRENT_DATE)
    ");
    $result = $this->query->execute();

    return $result['num_votes'];
}

I want to return ALL rows that are on year 2013 and month 5. But it returns '1' while I have 10+ rows with the same data?

What did I do wrong?

These are the rows that are located in my database:

id  ip          year    month
1   127.0.0.1   2013    5
2   127.0.0.1   2013    5
3   127.0.0.1   2013    5
4   127.0.0.1   2013    5
5   127.0.0.1   2013    5
6   127.0.0.1   2013    5
7   127.0.0.1   2013    5
8   127.0.0.1   2013    5
9   127.0.0.1   2013    5
10  127.0.0.1   2013    5
11  127.0.0.1   2013    5
12  127.0.0.1   2013    5
13  127.0.0.1   2013    5
14  127.0.0.1   2013    5
15  127.0.0.1   2013    5

EDITED MY METHOD PLEASE LOOK:

    public function votesThisMonth()
    {
        $this->query = $this->pdo->prepare
        ("
            SELECT
                *
            FROM
                votes
            WHERE
                YEAR(year) = YEAR(CURRENT_DATE)
            AND MONTH(month) = MONTH(CURRENT_DATE)
        ");
        $this->query->execute();

        return $this->query->rowCount();
    }

This returns '0', why?

count($result->fetchAll()). Returns all the rows fetched. This will work if you select a column instead of count(*). — Luigi Siri, May 13 '13 at 21:28
take the query, and run it manually in mysql and see what you get — Ascherer, May 13 '13 at 21:35
Look at the answer of this one: http://stackoverflow.com/questions/2700621/php-pdo-num-rows — Luigi Siri, May 13 '13 at 21:37

score 2 · Accepted Answer · answered May 13 '13 at 21:29

2

The query should be

SELECT * FROM votes
WHERE `year`  = YEAR(CURRENT_DATE)
  AND `month` = MONTH(CURRENT_DATE)

answered May 13 '13 at 21:29

Orangepill

24,500
3
42
63

Good one, you deserve it. Lil bit jealous, since I was first with this answer, what a repwhore am I. – Jan Turoň May 13 '13 at 21:41
@JanTuroň: Your answer is not the same as this answer. Play attention to the SELECT clause of this query. He wanted the actual results instead of just the COUNT. – Paolo Bergantino May 13 '13 at 21:42
@jantuon Plus I had you by a minute :) – Orangepill May 13 '13 at 21:43

score 1 · Answer 2 · answered May 13 '13 at 21:28

1

year and month are keywords, regardless the case, so wrap them in backticks

public function votesThisMonth()
{
    $this->query = $this->pdo->prepare
    ("
        SELECT
            COUNT(*) AS num_votes
        FROM
            votes
        WHERE
            `year` = YEAR(CURRENT_DATE)
        AND `month` = MONTH(CURRENT_DATE)
    ");
    $result = $this->query->execute();

    return $result['num_votes'];
}

answered May 13 '13 at 21:28

Jan Turoň

31,451
23
125
169

That's not actually accurate. `year` and `month` are functions - however, mysql allows you use to function names as column names without any issues. See http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html – Sam Dufel May 13 '13 at 22:35

score 0 · Answer 3 · answered May 13 '13 at 21:28

You should do it as following

public function votesThisMonth()
{
    $this->query = $this->pdo->prepare
    ("
        SELECT
            COUNT(*) AS num_votes
        FROM
            votes
        WHERE
            YEAR(year) = YEAR(CURRENT_DATE)
        AND MONTH(month) = MONTH(CURRENT_DATE)
    ");
    $this->query->execute();
    $result = $this->query->fetch(PDO::FETCH_ASSOC);

    return $result['num_votes'];
}

How to return a COUNT(*) results from a query PHP

3 Answers3