MySQL | Dynamic query - online users

Question

I'm looking for the way to build dynamic query and return online users. Please check my code below. I can't find where is mistake? Return's me: Unknown column 'is_online' in 'where clause'...

    if (isset($options['online']) && $options['online'] == 1) {
       $where[]  = ' AND is_online = 1';
    } 

    $sql = ' SELECT 
       u1.*,
       u2.*,
       COUNT(s.userid) AS is_online
    FROM `#__users`     
    AS 
        u1 
    LEFT JOIN 
        `#__ds_users`   
    AS 
        u2 
    ON 
        u1.id       = u2.id 
    LEFT JOIN 
        `#__session`    
    AS 
        s 
    ON 
        u1.id       = s.userid  
    WHERE 
        1=1
    '.implode('', $where).'
    GROUP BY 
    u1.id';

you might want to checkout my solution here http://stackoverflow.com/questions/25726512/how-to-insert-dynamic-multidimensional-array-in-database-with-mysqli — SuperDJ, Sep 12 '14 at 13:28
I'm guessing you can't use an alias in the WHERE condition, you should be able to use `COUNT(s.userid) = 1` there, or wrap the whole thing in a subquery. — stakolee, Sep 12 '14 at 13:30

score 1 · Accepted Answer · answered Sep 12 '14 at 13:33

You want to change that portion of the clause to a having clause:

having is_online = 1

This variable uses an aggregation function, so you cannot put the value in the where clause. However, you could instead have:

where s.userid is not null

Or, more simply, eliminate the condition and change the left join to inner join.

MySQL | Dynamic query - online users

1 Answers1