COUNT, IF, in a mysql_fetch_assoc query

Question

I've recently taken over a site with some SQL queries that seem to be a bit beyond me. What the query has to do is find a Trade that belongs to a User, then find the Feedback score (1,2, or 3) attached to that trade. It's supposed to count all the ratings (1 being negative, 2 neutral, 3 positive) for me to use later. Here's the code:

$user = mysql_fetch_assoc($this->db_query('
            SELECT
                `UserAccess`.*,
                COUNT(IF(`Feedback`.`rating`=1, 1, NULL)) AS "negative",
                COUNT(IF(`Feedback`.`rating`=2, 1, NULL)) AS "neutral",
                COUNT(IF(`Feedback`.`rating`=3, 1, NULL)) AS "positive"
            FROM
                `UserAccess`
            LEFT JOIN
                `Trades`
            ON
                `Trades`.`owner` = `UserAccess`.`ID`
            LEFT JOIN
                `Feedback`
            ON
                `Feedback`.`trade` = `Trades`.`ID`
            WHERE
                `UserAccess`.`ID` = "'.$_COOKIE[Auth::$config['cookie_name_id']].'"
            GROUP BY
                `UserAccess`.`ID`
            LIMIT
                1
        '));

The idea being I can use these numbers to calculate an overall rating, using user['negative'], user['positive'] etc.

This involves a lot of new concepts for me with sql, so I'm having trouble pinning down the fault. It may also be relevant that I think the old developer was using an earlier version of PHP to me (so it may have been working properly for him).

Thanks

score 2 · Answer 1 · edited May 23 '17 at 11:49

2

You want SUM(), not COUNT():

$user = mysql_fetch_assoc($this->db_query('
        SELECT
            `UserAccess`.*,
            SUM(IF(`Feedback`.`rating`=1, 1, 0)) AS "negative",
            SUM(IF(`Feedback`.`rating`=2, 1, 0)) AS "neutral",
            SUM(IF(`Feedback`.`rating`=3, 1, 0)) AS "positive"
        FROM
            `UserAccess`
        LEFT JOIN
            `Trades`
        ON
            `Trades`.`owner` = `UserAccess`.`ID`
        LEFT JOIN
            `Feedback`
        ON
            `Feedback`.`trade` = `Trades`.`ID`
        WHERE
            `UserAccess`.`ID` = "'.$_COOKIE[Auth::$config['cookie_name_id']].'"
        GROUP BY
            `UserAccess`.`ID`
        LIMIT
            1
    '));

Also, don't use mysql_* functions; use MySQLi or PDO.

Also, from your comments, it looks like you are misusing AS -- AS modifies whatever it follows. So, if you put it after SUM(), as in SUM(IF(Feedback.rating=1, 1, 0)) AS "negative", then you can access that pseudo-column by the name negative. If you put it after a table, then you can refer to that table by that alias elsewhere in the query.

Note that if there is no row matching your WHERE clause, you will get an empty result set (so the array $user will not contain anything).

Finally, please note that your code is currently vulnerable to SQL injection, because cookie values can be faked. You should use prepared statements to prevent this.

edited May 23 '17 at 11:49

Community

1
1

answered Dec 06 '13 at 06:52

elixenide

44,308
16
74
100

Thanks for your reply, unfortunately it didn't work for me. If after the query, I set the values manually with $user['positive'] = 2; the rest of my code works fine, so it's definitely part of the query. As far as I can see, all the table and column names are correct, too. Argh! – themartin Dec 06 '13 at 07:02
Can you explain what you mean by "it didn't work?" What did you get, and what did you expect? – elixenide Dec 06 '13 at 07:07
have you changed NULL to 0 as described? – niyou Dec 06 '13 at 07:09
I copied to code entirely. I expected I'd be able to access the sums or counts by using `$user['positive']` or `$user['negative']`. I suspect the problem may be in that, because if I change the query to something simple like `SELECT COUNT(rating) FROM Feedback AS positive`, I still get a zero when I use `$user['positive']`. Thanks again for your help. – themartin Dec 06 '13 at 07:15
Perhaps I'm misunderstand the 'AS' part. Neither positive, negative, or neutral exist in the database anywhere. As I understand, it just counts the rows with the rating specified in the IF statement, and then tallies them up into the part of the array called 'positive'. Then when I access $user['positive'], it gives me that number. Correct? – themartin Dec 06 '13 at 09:28
Please see my edit and see if it helps. `SELECT COUNT(rating) FROM Feedback AS positive` won't work, because you are aliasing the table, not a column or function result. – elixenide Dec 06 '13 at 14:15
Hi Ed, thanks again. Just to verify, your code isn't at all edited is it, just the note at the bottom? I'll try and put your note into practise if that's the case. I don't think I want to reuse the 'negative' values in the query for instance. I just want it to tally of 'rating' columns of that value. – themartin Dec 06 '13 at 16:08
Well it seems your code is for exactly what I need, to access that pseudo-column by the name negative; `$user['negative']`. I want the sum, as negative. Still, i'm not getting any value. I'm also not getting any errors of any kind, which is making it hard for me to troubleshoot. I'll keep plugging away unless my oversight becomes glaringly obvious to any of you :) – themartin Dec 06 '13 at 16:18
Well don't I feel silly. You were right Ed, and I feel this may have been working all along. I was misreading the data in the database. There really WERE no matching results. Eight hours. – themartin Dec 06 '13 at 17:48
Ugh. Well, glad to help! – elixenide Dec 06 '13 at 17:50

COUNT, IF, in a mysql_fetch_assoc query

1 Answers1