CODEWITHSUNDEEP
phpmysqlsqlpdo
user756659
user756659

Reputation: 3512

using count value in where clause of prepared statement

I am trying to create a statement that will return the count of event_target grouped by (username, event_title, and event_target) and ordered by that count.

In other words, totalsum is the count of rows where (username, event_title, and event_target) are all the same values.

Problem is the result is not ordered in DESC by totalsum.

$stmt = $db->prepare("SELECT *, COUNT(`event_target`) 'totalsum' FROM `testdb` WHERE `account_id` = ? GROUP BY `username`, `event_title`, `event_target` ORDER BY 'totalsum' DESC");

$stmt->execute(array($_SESSION['user']['account_id']));

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

//print out the array echo '<pre>',print_r($results,true),'</pre>';



Secondly, I would also like to use the totalsum value in my WHERE clause... so say I only want to return value where totalsum = 6. This returns an empty array. There ARE results with a totalsum = 6 if I use the above statement without this WHERE clause.

$stmt = $db->prepare("SELECT *, COUNT(`event_target`) 'totalsum' FROM `testdb` WHERE `account_id` = ? AND 'totalsum' = 6 GROUP BY `username`, `event_title`, `event_target` ORDER BY 'totalsum' DESC");

$stmt->execute(array($_SESSION['user']['account_id']));

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

//print out the array echo '<pre>',print_r($results,true),'</pre>';



What am I doing wrong here?

EDIT:

Thank you for the clarification on this! This seems to work best for me at the moment.

SELECT *, COUNT(`event_target`) `totalsum` FROM `testdb` WHERE `account_id` = ?
  GROUP BY `username`, `event_title`, `event_target` HAVING `totalsum`=6

I have run into another problem. The reason I was asking about a condition for totalsum was this is a modification to a search function. So... a typical search query might be something like this :

SELECT *, COUNT(`event_target`) `totalsum` FROM `testdb` WHERE `account_id` = ?
  AND (`event_target` LIKE 'searchquery' OR `event_title` LIKE 'searchquery' OR `event_name` LIKE 'searchquery')
  GROUP BY `username`, `event_title`, `event_target` ORDER BY `totalsum` DESC

How can I add in an or condition for totalsum? This is probably a poor example, but say someone searches for '6' then any result with a totalsum with a 6 in the value should be returned as well. In a perfect world I would like to be able to do :

OR `totalsum` LIKE 'searchquery'

Upvotes: 2

Views: 243

Answers (2)

Anthony Raymond
Anthony Raymond

Reputation: 7862

Try this :

SELECT 
    *,
    COUNT(`event_target`) 'totalsum' 
FROM 
    `testdb` 
WHERE 
    `account_id` = ? 
GROUP BY 
    `username`,
    `event_title`,
    `event_target`
HAVING 
    COUNT(`event_target`) = 6 
ORDER BY
    'totalsum' DESC

Upvotes: 2

jeroen
jeroen

Reputation: 91734

You are using the wrong kind of quotes for totalsum:

SELECT *, COUNT(`event_target`) 'totalsum' FROM `testdb` WHERE `account_id` = ? GROUP BY `username`, `event_title`, `event_target` ORDER BY 'totalsum' DESC

should be:

SELECT *, COUNT(`event_target`) `totalsum` FROM `testdb` WHERE `account_id` = ? GROUP BY `username`, `event_title`, `event_target` ORDER BY `totalsum` DESC

although here you could leave them out if you wanted to.

To filter the results by totalsum, you can do something like:

SELECT *, COUNT(`event_target`) `totalsum` FROM `testdb` WHERE `account_id` = ?
  GROUP BY `username`, `event_title`, `event_target` HAVING `totalsum`=6

Upvotes: 2

Related Questions