mysql - Group By From My Query Interferes With Pagination

Question

Good day. Please how can I re-write this query below in order to eliminate GROUP BY as it interferes with pagination and not sacrifice performance when the database eventually grows big? Here is the full code

$query = "report INNER JOIN ratings INNER JOIN seg WHERE report.bUser = 
seg.bUser AND report.sUser = ratings.sUser GROUP BY ratings.sUser";


function pagination($query, $per_page = 10, $page = 1, $url = '?'){ 
   include_once ('DBManager.php'); 
   $conn = DBManager::getConnection();
           
        $query = "SELECT COUNT(report.id) as `num` FROM {$query}";
        foreach($conn->query($query) as $row);
        $total = $row['num'];
        $adjacents = "2"; 
        
    //Ronald's code begins
    $url = '?';
        foreach($_GET as $k => $v) {
        if($k != 'page') {
        $url .= "{$k}={$v}&";
          }
        }
        //Ronald's code ends

        $page = ($page == 0 ? 1 : $page);  
        $start = ($page - 1) * $per_page;                               
        
        $prev = $page - 1;                          
        $next = $page + 1;
        $lastpage = ceil($total/$per_page);
        $lpm1 = $lastpage - 1;
        
        $pagination = "";
        if($lastpage > 1)
        {   
            $pagination .= "<ul class='pagination'>";
                $pagination .= "<li class='details'>Page $page of $lastpage</li>";
            if ($lastpage < 7 + ($adjacents * 2))
            {   
                for ($counter = 1; $counter <= $lastpage; $counter++)
                {
                    if ($counter == $page)
                        $pagination.= "<li><a class='current'>$counter</a></li>";
                    else
                        $pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";                    
                }
            }
            elseif($lastpage > 5 + ($adjacents * 2))
            {
                if($page < 1 + ($adjacents * 2))        
                {
                    for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
                    {
                        if ($counter == $page)
                            $pagination.= "<li><a class='current'>$counter</a></li>";
                        else
                            $pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";                    
                    }
                    $pagination.= "<li class='dot'>...</li>";
                    $pagination.= "<li><a href='{$url}page=$lpm1'>$lpm1</a></li>";
                    $pagination.= "<li><a href='{$url}page=$lastpage'>$lastpage</a></li>";      
                }
                elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
                {
                    $pagination.= "<li><a href='{$url}page=1'>1</a></li>";
                    $pagination.= "<li><a href='{$url}page=2'>2</a></li>";
                    $pagination.= "<li class='dot'>...</li>";
                    for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
                    {
                        if ($counter == $page)
                            $pagination.= "<li><a class='current'>$counter</a></li>";
                        else
                            $pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";                    
                    }
                    $pagination.= "<li class='dot'>..</li>";
                    $pagination.= "<li><a href='{$url}page=$lpm1'>$lpm1</a></li>";
                    $pagination.= "<li><a href='{$url}page=$lastpage'>$lastpage</a></li>";      
                }
                else
                {
                    $pagination.= "<li><a href='{$url}page=1'>1</a></li>";
                    $pagination.= "<li><a href='{$url}page=2'>2</a></li>";
                    $pagination.= "<li class='dot'>..</li>";
                    for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
                    {
                        if ($counter == $page)
                            $pagination.= "<li><a class='current'>$counter</a></li>";
                        else
                            $pagination.= "<li><a href='{$url}page=$counter'>$counter</a></li>";                    
                    }
                }
            }
            
            if ($page < $counter - 1){ 
                $pagination.= "<li><a href='{$url}page=$next'>Next</a></li>";
                $pagination.= "<li><a href='{$url}page=$lastpage'>Last</a></li>";
            }else{
                $pagination.= "<li><a class='current'>Next</a></li>";
                $pagination.= "<li><a class='current'>Last</a></li>";
            }
            $pagination.= "</ul>
";        
        }
    
    
        return $pagination;
    }

Currently, the pagination doesn't get displayed on the page using this function but get displayed on pages where I didn't use GROUP BY. In another instance on another page, I have rows result doubled. If this can be sorted, I can fix the other few areas. I hope my question is clear now. Thank you all for your input.

question from:https://stackoverflow.com/questions/65931376/group-by-from-my-query-interferes-with-pagination

Answer 1

Waitting for answers