From the question itself, I see two problems
- The return value of the query will be, at most, one row.
SQL_CALC_FOUND_ROWS is uesless
- You are looking for the number of rows that piled up in the
You have to extrapolate the row count from the result of the
group_concat. Here is how
- If there is a return value from the
GROUP_CONCAT, set @found_rows = 1 + the number of commas in the return value.
- If no value is returned for the
GROUP_CONCAT, set @found_rows = 0
BTW there is a heads you need to be aware of: GROUP_CONCAT's max length is 1024 by default. You should extend it to 10M.
SET group_concat_max_len = 1024 * 1024 * 10;
FROM notification AS ca
LEFT JOIN alert_msg AS an ON an.id=ca.alert_msg_id
WHERE ca.mem_id='777 '
NOT IN(26,27) AND ca.from_id != '777'
SET @found_rows = IF(
LENGTH(@IDList) - LENGTH(REPLACE(@IDList,',','')) + 1
Give it a Try !!!