How to limit without SQL LIMIT

[doddsey_65]

im using the floowing code to pull all alerts from the database. as you can see i have 3 types of alertsd. Profile alerts, forum alerts and topic alerts. They are sorted and placed on the screen under their respective header. However i want to limit it so it only displays 4 of each type of alert. I cant use LIMIT in the query because that would limit all alerts meaning only 4 alerts in total would show up and i just need to limit each alert type. any ideas?

 

$alert_query = $link->query("SELECT 
                            a.a_aid, a.a_alert_type, a.a_time_alerted, 
                            a.a_fid, a.a_poster, a_alert_read,
                            a.a_tid, c.f_name as cat_name,
                            f.f_fid, f.f_name,
                            t.t_name,
                            u.u_avatar, u.u_avatar_cropped
                            FROM ".TBL_PREFIX."alerts as a
                            LEFT JOIN ".TBL_PREFIX."forums as f
                            ON (f.f_fid = a.a_fid)
                            LEFT JOIN ".TBL_PREFIX."topics as t
                            ON (t.t_tid = a.a_tid)
                            LEFT JOIN ".TBL_PREFIX."forums as c
                            ON (c.f_fid = f.p_id) 
                            LEFT JOIN ".TBL_PREFIX."users as u
                            ON (u.u_username = a.a_poster)
                            WHERE a.a_user_name = '$user_name'
                            ORDER BY a_time_alerted
                            ") or die(print_link_error());
        $alert_info = $alert_query->fetchAll();
        
        $pm_alert_list = '';
        $num_pm_alerts = 0;
        $num_forum_alerts = 0;
        $num_topic_alerts = 0;
        
        foreach($alert_info as $key => $val)
        {
            $alert_info[$key]['a_alert_read'] == 0 ? $color = '#f5dfaf' : $color = '#f4f4f4';
            
            // if alert is a profile message alert
            if($alert_info[$key]['a_alert_type'] == 1)
            {    
                $pm_alert_list .= '<dd style="background:'.$color.';" class="alert" id="alert-'.$alert_info[$key]['a_aid'].'"><p class="alert_intro">';
                $pm_alert_list .= '<input type="checkbox" class="pm_checkbox" id="pm_checkbox-'.$alert_info[$key]['a_aid'].'" />'.profile_link($alert_info[$key]['a_poster']).' posted on your wall</p>';
                $pm_alert_list .= '<p class="alert_time"> on '.asf_date($alert_info[$key]['a_time_alerted'],'full').'</p>';
                $pm_alert_list .= '</dd>';
                $num_pm_alerts++;
            }
            
            if($alert_info[$key]['a_alert_type'] == 2)
            {    
                $forum_alert_list .= '<dd style="background:'.$color.';" class="alert" id="alert-'.$alert_info[$key]['a_aid'].'"><p class="alert_intro">';
                $forum_alert_list .= '<input type="checkbox" class="pm_checkbox" id="pm_checkbox-'.$alert_info[$key]['a_aid'].'" /><strong><a href="'.$config['asf_root'].'category/'.create_url($alert_info[$key]['cat_name']).'/forum/'.create_url($alert_info[$key]['f_name']).'">'.$alert_info[$key]['f_name'].'</a></strong> has a new topic</p>';
                $forum_alert_list .= '<p class="alert_time"> '.asf_date($alert_info[$key]['a_time_alerted'],'full').'</p>';
                $forum_alert_list .= '</dd>';
                $num_forum_alerts++;
            }
            
            if($alert_info[$key]['a_alert_type'] == 3)
            {    
                $topic_alert_list .= '<dd style="background:'.$color.';" class="alert" id="alert-'.$alert_info[$key]['a_aid'].'"><p class="alert_intro">';
                $topic_alert_list .= '<input type="checkbox" class="pm_checkbox" id="pm_checkbox-'.$alert_info[$key]['a_aid'].'" /><strong><a href="'.$config['asf_root'].'category/'.create_url($alert_info[$key]['cat_name']).'/forum/'.create_url($alert_info[$key]['f_name']).'/topic/'.create_url($alert_info[$key]['t_name']).'">'.$alert_info[$key]['t_name'].'</a></strong> has a new post</p>';
                $topic_alert_list .= '<p class="alert_time"> '.asf_date($alert_info[$key]['a_time_alerted'],'full').'</p>';
                $topic_alert_list .= '</dd>';
                $num_topic_alerts++;
            }
        }

[Muddy_Funster]

Could you nest a counter into the return loop and use that to elect how many of each record is returned?  It's quick and dirty, but it saves rewriting the SQL.

[dcro2]

This might be what you're looking for: http://forums.mysql.com/read.php?20,128132,128134#msg-128134

Basically, use HAVING COUNT(column) <= 4 on your JOINs.