CODEWITHSUNDEEP
mysqlcountchataggregatehaving
JamesBondInBlack
JamesBondInBlack

Reputation: 97

MYSQL HAVING WHERE not working

I have the following function

function availableChatRoom($topic_id){
$max_rooms = 2;
$max_users = 2;

$sqli = "SELECT COUNT(DISTINCT room_num) AS numRooms FROM forum_chat WHERE topic_id = '$topic_id'";
$num_rooms = queryColumnVal($sqli, "numRooms");

$sqlii = "SELECT room_num, COUNT(user_id) AS numUsers FROM forum_chat 
          WHERE topic_id = '$topic_id' AND (status = 'online' AND status = 'offline') AND chat_msg = ''
          GROUP BY room_num
          HAVING numUsers < $max_users ORDER BY msg_date";

$num_rooms_with_spaces = mysqlNumRows($sqlii, "room_num");
queryColumnVal($sqlii, "room_num");

if($num_rooms == 0){
    return 1;
}
elseif($num_rooms <= $max_rooms){

    if($num_rooms_with_spaces == 0){
        return $num_rooms + 1;
    }
    else{
        return queryColumnVal($sqlii, "room_num");
    }
}
else{
    return "none";
}

}

the idea is for the function to return a chat room number with space. -Every time a user joins the room, a line with no msg is inserted with status online -Every time a user leaves the room a line with no msg is inserted with status offline I am now trying to write a function which checks if there are any spare rooms for each topic The idea is to select the room_num where the number of users who have logged in but not logged out is less than the maximum chatter per room. Can someone help me with my $sqlii.

Thanks

Upvotes: 1

Views: 6428

Answers (3)

John Woo
John Woo

Reputation: 263803

The HAVING clause is evaluated before the SELECT - so the server doesn't yet know about that alias.

SELECT room_num, COUNT(user_id) AS numUsers 
FROM forum_chat 
WHERE topic_id = '$topic_id' AND 
      (status = 'online' OR status = 'offline') AND 
      chat_msg = ''
GROUP BY room_num
HAVING COUNT(user_id) < $max_users ORDER BY msg_date
  1. First the product of all tables in the from clause is formed.
  2. The where clause is then evaluated to eliminate rows that do not satisfy the search_condition.
  3. Next, the rows are grouped using the columns in the group by clause.
  4. Then, Groups that do not satisfy the search_condition in the having clause are eliminated.
  5. Next, the expressions in the select clause target list are evaluated.
  6. If the distinct keyword in present in the select clause, duplicate rows are now eliminated.
  7. The union is taken after each sub-select is evaluated. 8.Finally, the resulting rows are sorted according to the columns specified in the order by clause.

Upvotes: 4

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

There are several errors in your query:

  • (status = 'online' AND status = 'offline')

This asserts that your query wil return no now at all. Use: (status = 'online' OR status = 'offline') or the equivalent (status IN ('online', 'offline'))

  • COUNT(user_id)

This does not count the difference of online minus offline users. Correct the logic.

  • ORDER BY msg_date

In GROUP BY queries, it's not good to use non-aggregate column, at the HAVING, SELECT or ORDER BY clauses, despite that MySQL allows it. Unless that column depends on the grouping expression. Since msg_date obviously does not depend on room_num, you should replace that with an aggregate function:

ORDER BY MAX(msg_date) or ORDER BY MIN(msg_date)

$sqlii = "
   SELECT room_num, 
          COUNT(CASE WHEN status = 'online' THEN 1 END) 
            - COUNT(CASE WHEN status = 'offline' THEN 1 END) 
          AS numUsers 
   FROM forum_chat 
   WHERE topic_id = '$topic_id' 
     AND status IN ('online', 'offline')
     AND chat_msg = ''
   GROUP BY room_num
   HAVING COUNT(CASE WHEN status = 'online' THEN 1 END) 
          - COUNT(CASE WHEN status = 'offline' THEN 1 END) 
          < $max_users 
   ORDER BY MAX(msg_date)
     ";

Upvotes: 1

JHS
JHS

Reputation: 7871

You cannot use the alias of the column in HAVING in the same SELECT level. That is because in yout MySQL ONLY_FULL_GROUP_BY would be activated.

Try this - 

HAVING COUNT(user_id) < $max_users ORDER BY msg_date

Upvotes: 0

Related Questions