Paginate according to current month and year?

[lovephp]

How to acheive it friends? i want my current codes to show results accouding to current  month followed by year, the precious  month goes to the page two.

 

would really appreciate if someone could help me acheive it

 

 $tableName="records";        
    $targetpage = "sales.php";     
    $limit = 1000;
    $cur_month = date("Y-m");

         $query = "SELECT COUNT(*) as num FROM $tableName  WHERE Customer_Status = 'Sale' AND DATE_FORMAT(Date, '%Y-%m') = '$cur_month' ORDER BY id DESC";
    $total_pages = mysql_fetch_array(mysql_query($query));
    $total_pages = $total_pages[num];
    
    $stages = 1;
    $page = mysql_escape_string($_GET['page']);
    if($page){
        $start = ($page - 1) * $limit;
    }else{
        $start = 0;    
        }    
    
    // Get page data
    $query1 = "SELECT id, Date, User, Team, Transfer_Number, Agent_Name, Cell_Phone, Customer_Name, Customer_Status FROM $tableName WHERE Customer_Status = 'Sale' AND DATE_FORMAT(Date, '%Y-%m') = '$cur_month' ORDER BY id DESC LIMIT $start, $limit";
    $result = mysql_query($query1);
    
    // Initial page num setup
    if ($page == 0){$page = 1;}
    $prev = $page - 1;    
    $next = $page + 1;                            
    $lastpage = ceil($total_pages/$limit);        
    $LastPagem1 = $lastpage - 1;    
    
    $paginate = '';
    if($lastpage > 1)
    {    
    $paginate .= "<div class='paginate'>";
        // Previous
        if ($page > 1){
            $paginate.= "<a href='$targetpage?page=$prev'>previous</a>";
        }else{
            $paginate.= "<span class='disabled'>previous</span>";    }
                
        // Pages    
        if ($lastpage < 7 + ($stages * 2))    // Not enough pages to breaking it up
        {    
            for ($counter = 1; $counter <= $lastpage; $counter++)
            {
                if ($counter == $page){
                    $paginate.= "<span class='current'>$counter</span>";
                }else{
                    $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}                    
            }
        }
        elseif($lastpage > 5 + ($stages * 2))    // Enough pages to hide a few?
        {
            // Beginning only hide later pages
            if($page < 1 + ($stages * 2))        
            {
                for ($counter = 1; $counter < 4 + ($stages * 2); $counter++)
                {
                    if ($counter == $page){
                        $paginate.= "<span class='current'>$counter</span>";
                    }else{
                        $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}                    
                }
                $paginate.= "...";
                $paginate.= "<a href='$targetpage?page=$LastPagem1'>$LastPagem1</a>";
                $paginate.= "<a href='$targetpage?page=$lastpage'>$lastpage</a>";        
            }
            // Middle hide some front and some back
            elseif($lastpage - ($stages * 2) > $page && $page > ($stages * 2))
            {
                $paginate.= "<a href='$targetpage?page=1'>1</a>";
                $paginate.= "<a href='$targetpage?page=2'>2</a>";
                $paginate.= "...";
                for ($counter = $page - $stages; $counter <= $page + $stages; $counter++)
                {
                    if ($counter == $page){
                        $paginate.= "<span class='current'>$counter</span>";
                    }else{
                        $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}                    
                }
                $paginate.= "...";
                $paginate.= "<a href='$targetpage?page=$LastPagem1'>$LastPagem1</a>";
                $paginate.= "<a href='$targetpage?page=$lastpage'>$lastpage</a>";        
            }
            // End only hide early pages
            else
            {
                $paginate.= "<a href='$targetpage?page=1'>1</a>";
                $paginate.= "<a href='$targetpage?page=2'>2</a>";
                $paginate.= "...";
                for ($counter = $lastpage - (2 + ($stages * 2)); $counter <= $lastpage; $counter++)
                {
                    if ($counter == $page){
                        $paginate.= "<span class='current'>$counter</span>";
                    }else{
                        $paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}                    
                }
            }
        }
                    
                // Next
        if ($page < $counter - 1){
            $paginate.= "<a href='$targetpage?page=$next'>next</a>";
        }else{
            $paginate.= "<span class='disabled'>next</span>";
            }
            
        $paginate.= "</div>";        
        
}


echo "Total Records:".$total_pages;
echo '<br/><br/>';
 // pagination
 echo $paginate;

 }

Edited December 2, 2014 by lovephp

[Psycho]

I am not going to read and analyze all of your code and write it for you, but I will provide the basis for a solution. As I understand it, you want the page to load the current month/year by default with the ability to paginate by months. So, you can simply find any "normal" pagination script as the basis. Then each "page" is calculated as the current month - (page-1) months.

 

One challenge is in calculating the Total Pages. You could get the oldest data and calculate how many months it is from the current month. But, if there are any empty months, you could have pages with no records. if that is possible, and you don't want empty pages, then you would have to run a query to get the count of months with data.

 

Rough example:

 

 

Logic to determine the parameters to get the selected "page" display

//Pass normal page numbers, 1, 2, 3, ...
$page = intval($_GET['page']);
 
//Determine the month offset, from current month, based on page selected
$monthOffset = $page-1;
//Get a timestamp for the selected month
$pageDate = strtotime(" -{$monthOffset} months");
//Determine the month & year of the selected month
$year = date('Y', $pageDate);
$month = date('m', $pageDate);

Query to get the data

$query1 = "SELECT id, Date, User, Team, Transfer_Number, Agent_Name, Cell_Phone, Customer_Name, Customer_Status
           FROM $tableName
           WHERE Customer_Status = 'Sale'
             AND YEAR(Date) = $year
             AND MONTH(Date) = $month
           ORDER BY id DESC LIMIT $start, $limit";

Edited December 2, 2014 by Psycho

[Barand]

You could do "SELECT COUNT(DISTINCT DATE_FORMAT(date, '%Y-%m')) ..."

 

Or, perhaps, "SELECT DISTINCT DATE_FORMAT(date, '%Y-%m') as month ..." and instead of giving page numbers to click on, give the months

 

14-12   14-11   14-10   14-09 ... 14-01

 

Then instead of using page numbers and LIMIT clauses just query for the Y-m that is clicked

Edited December 2, 2014 by Barand