user889349 user889349 - 6 months ago 19
PHP Question

MySQL | Dynamic query - online users

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';

Answer

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.