PHP Pagination

[billy_111]

Hey,

I am trying to implement pagination on my PHP website to display images from a database and limit them to only show a certain amount of images per page..

Now i have been following this tutorial:-

http://net.tutsplus.com/tutorials/php/how-to-paginate-data-with-php/

I have the following code:-

            <?php

            include("paginator.php"); //Paginator class is within this file
        
            $pages = new Paginator;  
            $pages->items_total = $num_rows[0];  
            $pages->mid_range = 9;  
            $pages->paginate();  
            echo $pages->display_pages(); 
                           
            include("conn.php"); 
                                     
            $query = "SELECT image_name FROM images WHERE image_name != '' ORDER BY image_id ASC $pages->limit";
            $result =  mysql_query ($query);
            $row = mysql_fetch_array($result,MYSQL_ASSOC);
            
            echo "<div><a href='images/$row[image_name]' rel='lightbox' title='$row[caption]'><img src='images/$row[image_name]' alt='' width='80' height='80'/></a><span>$row[caption]</span></div>";
            
            mysql_close();    
            ?>

Now the code above has an include file "paginator.php" which includes the class given in the tutorial.

Now when it comes to displaying the fields from the database i am having trouble. I am receiving this error:-

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /domains/skindeepapparel.com/http/lou/gallery.php on line 90 

Can anyone help..

If not can you offer some other help in terms of pagination..

Regards

[ArunB]

After mysql_query, try mysql_error function to get the error occurred while executing last query.

[Jake Arkinstall]

What is output when you echo the query?

Is line 90 in that code you've posted? If line 90 is after the last line of the code you've posted, try removing mysql_close() - it's pointless to use that function unless you do alot of processing after the last query.

Try this, it should give you an error to work with:

$query = "SELECT image_name FROM images WHERE image_name != '' ORDER BY image_id ASC {$pages->limit}";
$result = mysql_query($query) or die("<h2>MySQL Error</h2><p><b>Query:</b>{$query}<br /><b>Error:</b> " . mysql_error() . "</p>"); 
$row = mysql_fetch_assoc($result); 

[ArunB]

Write a JavaScript function, which sends a request to your PHP script with start-index and max-limit and the PHP page will give the details of the images to be displayed. Then Use JavaScript and display images on your page.

[Jake Arkinstall]

That really isn't a good idea, unless it's for debugging reasons only.

JavaScript is often disabled, and should only be used as an enhancement, not core functionality, unless your site depends on JavaScript as its functionality.

[billy_111]

Hey,

I am going with post #3 so i can see the error.

I have tried this code:-

             $query = "SELECT image_name FROM images WHERE image_name != '' ORDER BY image_id ASC {$pages->limit}";
            $result = mysql_query($query) or die("<h2>MySQL Error</h2><p><b>Query:</b>{$query}<br /><b>Error:</b> " . mysql_error() . "</p>"); 
            $row = mysql_fetch_assoc($result); 

This is the error i receive:-

Query:SELECT image_name FROM images WHERE image_name != '' ORDER BY image_id ASC LIMIT -25,25
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-25,25' at line 1 

If you want i can post the full contents of the "pagination.php" file, you can see this in my previous post in the link provided..

[Jake Arkinstall]

Yeah, it appears that the problem is in pagination.php. Can you post it?

What is contained in $num_rows?

[billy_111]

Yep, its below, now its alot of code i was following a tutorial from this link:-

http://net.tutsplus.com/tutorials/ph...data-with-php/

Code is shown below:-

<?php        
 class Paginator{  
             var $items_per_page;  
             var $items_total;  
             var $current_page;  
             var $num_pages;  
             var $mid_range;  
             var $low;  
             var $high;  
             var $limit;  
             var $return;  
             var $default_ipp = 25;  
           
             function Paginator()  
             {  
                 $this->current_page = 1;  
                 $this->mid_range = 7;  
                 $this->items_per_page = (!empty($_GET['ipp'])) ? $_GET['ipp']:$this->default_ipp;  
             }  
           
             function paginate()  
             {  
                 if($_GET['ipp'] == 'All')  
                 {  
                     $this->num_pages = ceil($this->items_total/$this->default_ipp);  
                     $this->items_per_page = $this->default_ipp;  
                 }  
                 else  
                 {  
                     if(!is_numeric($this->items_per_page) OR $this->items_per_page <= 0) $this->items_per_page = $this->default_ipp;  
                     $this->num_pages = ceil($this->items_total/$this->items_per_page);  
                 }  
                 $this->current_page = (int) $_GET['page']; // must be numeric > 0  
                 if($this->current_page < 1 Or !is_numeric($this->current_page)) $this->current_page = 1;  
                 if($this->current_page > $this->num_pages) $this->current_page = $this->num_pages;  
                 $prev_page = $this->current_page-1;  
                 $next_page = $this->current_page+1;  
           
                 if($this->num_pages > 10)  
                 {  
                     $this->return = ($this->current_page != 1 And $this->items_total >= 10) ? "<a class=\"paginate\" href=\"$_SERVER[PHP_SELF]?page=$prev_page&ipp=$this->items_per_page\">� Previous</a> ":"<span class=\"inactive\" href=\"#\">� Previous</span> ";  
           
                     $this->start_range = $this->current_page - floor($this->mid_range/2);  
                     $this->end_range = $this->current_page + floor($this->mid_range/2);  
           
                     if($this->start_range <= 0)  
                     {  
                         $this->end_range += abs($this->start_range)+1;  
                         $this->start_range = 1;  
                     }  
                     if($this->end_range > $this->num_pages)  
                     {  
                         $this->start_range -= $this->end_range-$this->num_pages;  
                         $this->end_range = $this->num_pages;  
                     }  
                     $this->range = range($this->start_range,$this->end_range);  
           
                     for($i=1;$i<=$this->num_pages;$i++)  
                     {  
                         if($this->range[0] > 2 And $i == $this->range[0]) $this->return .= " ... ";  
                         // loop through all pages. if first, last, or in range, display  
                         if($i==1 Or $i==$this->num_pages Or in_array($i,$this->range))  
                         {  
                             $this->return .= ($i == $this->current_page And $_GET['page'] != 'All') ? "<a title=\"Go to page $i of $this->num_pages\" class=\"current\" href=\"#\">$i</a> ":"<a class=\"paginate\" title=\"Go to page $i of $this->num_pages\" href=\"$_SERVER[PHP_SELF]?page=$i&ipp=$this->items_per_page\">$i</a> ";  
                         }  
                         if($this->range[$this->mid_range-1] < $this->num_pages-1 And $i == $this->range[$this->mid_range-1]) $this->return .= " ... ";  
                     }  
                     $this->return .= (($this->current_page != $this->num_pages And $this->items_total >= 10) And ($_GET['page'] != 'All')) ? "<a class=\"paginate\" href=\"$_SERVER[PHP_SELF]?page=$next_page&ipp=$this->items_per_page\">Next �</a>\n":"<span class=\"inactive\" href=\"#\">� Next</span>\n";  
                     $this->return .= ($_GET['page'] == 'All') ? "<a class=\"current\" style=\"margin-left:10px\" href=\"#\">All</a> \n":"<a class=\"paginate\" style=\"margin-left:10px\" href=\"$_SERVER[PHP_SELF]?page=1&ipp=All\">All</a> \n";  
                 }  
                 else  
                 {  
                     for($i=1;$i<=$this->num_pages;$i++)  
                     {  
                         $this->return .= ($i == $this->current_page) ? "<a class=\"current\" href=\"#\">$i</a> ":"<a class=\"paginate\" href=\"$_SERVER[PHP_SELF]?page=$i&ipp=$this->items_per_page\">$i</a> ";  
                     }  
                     $this->return .= "<a class=\"paginate\" href=\"$_SERVER[PHP_SELF]?page=1&ipp=All\">All</a> \n";  
                 }  
                 $this->low = ($this->current_page-1) * $this->items_per_page;  
                 $this->high = ($_GET['ipp'] == 'All') ? $this->items_total:($this->current_page * $this->items_per_page)-1;  
                 $this->limit = ($_GET['ipp'] == 'All') ? "":" LIMIT $this->low,$this->items_per_page";  
             }  
           
             function display_items_per_page()  
             {  
                 $items = '';  
                 $ipp_array = array(10,25,50,100,'All');  
                 foreach($ipp_array as $ipp_opt)    $items .= ($ipp_opt == $this->items_per_page) ? "<option selected value=\"$ipp_opt\">$ipp_opt</option>\n":"<option value=\"$ipp_opt\">$ipp_opt</option>\n";  
                 return "<span class=\"paginate\">Items per page:</span><select class=\"paginate\" onchange=\"window.location='$_SERVER[PHP_SELF]?page=1&ipp='+this[this.selectedIndex].value;return false\">$items</select>\n";  
             }  
           
             function display_jump_menu()  
             {  
                 for($i=1;$i<=$this->num_pages;$i++)  
                 {  
                     $option .= ($i==$this->current_page) ? "<option value=\"$i\" selected>$i</option>\n":"<option value=\"$i\">$i</option>\n";  
                 }  
                 return "<span class=\"paginate\">Page:</span><select class=\"paginate\" onchange=\"window.location='$_SERVER[PHP_SELF]?page='+this[this.selectedIndex].value+'&ipp=$this->items_per_page';return false\">$option</select>\n";  
             }  
           
             function display_pages()  
             {  
                 return $this->return;  
             }  
        }  
?>

[Jake Arkinstall]

Somewhere along the line, it appears that $current_page is 0.

So, my guess is that num_pages is 0, therefore $num_rows[0] seems to be 0. Can you post the output of var_dump($num_rows); ?

[billy_111]

Hey,

I dont quite know how to do that.. ?

I dont think its the pagination file becuase i took my SQL out and the page does not show any errors..

Hoowever to make the code work i need SQL to display what i want, therefore the error must be in my SQL code..?

[Jake Arkinstall]

Yes, the limit starts at -25 - it must be >= 0.

But that's because of the pagination file, because the SQL uses a value from the pagination file.

So, where you have:

            <?php 

            include("paginator.php"); //Paginator class is within this file 
         
            $pages = new Paginator;   
            $pages->items_total = $num_rows[0];   
            $pages->mid_range = 9;   
            $pages->paginate();

I'm guessing the problem is with $num_rows[0]. What is that value?

var_dump($num_rows); 

will tell you everything that's inside $num_rows.

[billy_111]

Hey,

I tried the var_dump and it didnt show anything, so i changed the code to this:-

            <?php

            include("paginator.php"); //Paginator class is within this file
        
            $pages = new Paginator;  
            $pages->items_total = $num_rows[0];  
            $pages->mid_range = 9;  
            $pages->paginate();  
            
            echo $pages->display_pages(); 
                          
            include("conn.php"); 
                                     
            $query = "SELECT image_name FROM images WHERE image_name != '' ORDER BY image_id ASC {$pages->limit}";
            $result = mysql_query($query); 
            $row = mysql_fetch_assoc($result); 

            echo "<div><a href='images/$row[image_name]' rel='lightbox' title='$row[caption]'><img src='images/$row[image_name]' alt='' width='80' height='80'/></a><span>$row[caption]</span></div>";

            var_dump($num_rows); 

            mysql_close();    
            ?>

And i got a value of "NULL"..

What does this mean..

Regards
Thanks for you help so far

[Jake Arkinstall]

So, if you're assigning the total amount of rows to $num_rows[0], and $num_rows is null...

This is your problem then. Where does $num_rows come from?

[billy_111]

Hmmm..

Well i was just following the tutorial so what i have done is commented out the num_rows line:-

//$pages->items_total = $num_rows[0]; 

But i still recieve the same error when activating the SQL error script which is:-

Query:SELECT image_name FROM images WHERE image_name != '' ORDER BY pic_id ASC LIMIT -25,25
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-25,25' at line 1 

I dont understand why it does not work..

[billy_111]

If i change to SQL to this:-

$query = "SELECT image_name FROM images WHERE image_name != '' ORDER BY pic_id"; 

It shows only one image.. But no pagination.. I only get an "All" link, you can see here..

http://www.skindeepapparel.com/lou/gallery.php

Regards

[Jake Arkinstall]

As I have said before, it's because the items_total value is 0. You need to set that to the total amount of rows in the query.

e.g.

include("conn.php");
$Query = "SELECT COUNT(id) FROM images WHERE image_name != ''";
$Result = MySQL_Query($Query);
$NumRows = MySQL_Result($Query, 0);
include("paginator.php"); //Paginator class is within this file 
$Pages = new Paginator;   
$Pages->items_total = $NumRows;
$Pages->mid_range = 9;
$Pages->paginate();
echo $Pages->display_pages();
$Query = "SELECT image_name FROM images WHERE image_name != '' ORDER BY image_id ASC {$pages->limit}"; 
$Result = mysql_query ($query); 
While($Row = MySQL_Fetch_Assoc($result)){
    PrintF('<div><a href="%1$s" rel="lightbox" title="%2$s"><img src="images/%1$s" alt="" width="80" height="80"/></a><span>%2$s</span></div>', $Row['image_name'], $row['caption']);
} 

[billy_111]

Ok, the only problem with that is that the total amount of rows will contantly change as the administrator will be uploading new images on a daily basis..

If i try with the code you have suggested, i get this error:-

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /domains/skindeepapparel.com/http/lou/gallery.php on line 82
All 

I have 16 rows in my database table currently, so how can i check for test purposes that the pagination actually works..

[Jake Arkinstall]

Sorry, change the first parameter of MySQL_Result to $Result, not $Query.

If it still doesn't work, change

COUNT(id)

to

COUNT(image_name)

[billy_111]

Right, were getting somewhere now..

Now i can see ALL the images which exist in my database table. However i still have one error on the page.

The error is:-

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /domains/skindeepapparel.com/http/lou/gallery.php on line 84 

Now, i have the following code:-

        <?php                 
        include("conn.php");
        
        $Query = "SELECT COUNT(image_name) FROM images WHERE image_name != ''";
        $Result = MySQL_Query($Query);
        $NumRows = MySQL_Result($Query, 0);
        
        include("paginator.php"); //Paginator class is within this file
         
        $Pages = new Paginator;   
        $Pages->items_total = $NumRows;
        $Pages->mid_range = 9;
        $Pages->paginate();
        
        echo $Pages->display_pages();
        
        $Query = "SELECT image_name FROM images WHERE image_name != '' ORDER BY pic_id ASC {$pages->limit}"; 
        $Result = mysql_query ($query); 
        
        while($Row = MySQL_Fetch_Assoc($Result))
        {
        PrintF('<div><a href="%1$s" rel="lightbox" title="%2$s"><img src="images/%1$s" alt="" width="80" height="80"/></a><span>%2$s</span></div>', $Row['image_name'], $Row['caption']);
        }   
        ?>

Should the page numbers come up or do i need to have more than a certain number of images on the page for this? See the link to my website in my previous posts..

Were nearly there..

[Jake Arkinstall]

Look at my above post.

[billy_111]

Hey,

I think its 90% there. I changed to paginator.php file to only show 5 images per page:-

var $default_ipp = 5; 

Now when first viewing the page it should only show the 5 images, but it is still showing ALL the images, and the pagination does not seem to be taking me anywhere..

[Jake Arkinstall]

Can you post the code you've got so far?

[billy_111]

Yeah sure, sorry about this.

        <?php                 
        include("conn.php");
        
        $Query = "SELECT COUNT(image_name) FROM images WHERE image_name != ''";
        $Result = MySQL_Query($Query);
        $NumRows = MySQL_Result($Result, 0);
        
        include("paginator.php"); //Paginator class is within this file
         
        $Pages = new Paginator;   
        $Pages->items_total = $NumRows;
        $Pages->mid_range = 9;
        $Pages->paginate();
                
        $Query = "SELECT image_name FROM images WHERE image_name != '' ORDER BY pic_id ASC {$pages->limit}"; 
        $Result = mysql_query ($query);    
            
        while($Row = MySQL_Fetch_Assoc($Result))
        {
            if($_SESSION['USERNAME'] != FALSE) 
            {
            echo "<div><a href='images/$Row[image_name]' rel='lightbox' title='$Row[caption]'><img src='images/$Row[image_name]' alt='' width='80' height='80'/></a><span>$Row[caption]</span><br/><a href='delete_image.php?pic_id=$Row[pic_id]' style='color:red'/>delete</a></div>";
            }
            else
            {
            echo "<div><a href='images/$Row[image_name]' rel='lightbox' title='$Row[caption]'><img src='images/$Row[image_name]' alt='' width='80' height='80'/></a><span>$Row[caption]</span></div>";
            }
        }   
        
        echo "<div style='margin-left:700px;font-size:12px;border:1px solid #fff;background-color:#000;padding:5px;'>" . $Pages->display_pages() . "</div>";
        ?>

The paginator.php file is the same as before but this line is changed:-

var $default_ipp = 5; 

Originally this line was "25"..

Can you see where i am going wrong?

Regards

[Jake Arkinstall]

In the query you have:

{$pages->limit} 

Change that to

{$Pages->limit} 

[billy_111]

YAAAYY!!!

Thank you so much. My code now is as shown:-

        <?php                 
        include("conn.php");
        
        $Query = "SELECT COUNT(image_name) FROM images WHERE image_name != ''";
        $Result = MySQL_Query($Query);
        $NumRows = MySQL_Result($Result, 0);
        
        include("paginator.php"); //Paginator class is within this file
         
        $Pages = new Paginator;   
        $Pages->items_total = $NumRows;
        $Pages->mid_range = 9;
        $Pages->paginate();
                
        $Query = "SELECT image_name, caption FROM images WHERE image_name != '' ORDER BY pic_id ASC {$Pages->limit}"; 
        $Result = mysql_query ($Query);    
            
        while($Row = MySQL_Fetch_Assoc($Result))
        {
            if($_SESSION['USERNAME'] != FALSE) 
            {
            echo "<div><a href='images/$Row[image_name]' rel='lightbox' title='$Row[caption]'><img src='images/$Row[image_name]' alt='' width='80' height='80'/></a><span>$Row[caption]</span><br/><a href='delete_image.php?pic_id=$Row[pic_id]' style='color:red'/>delete</a></div>";
            }
            else
            {
            echo "<div><a href='images/$Row[image_name]' rel='lightbox' title='$Row[caption]'><img src='images/$Row[image_name]' alt='' width='80' height='80'/></a><span>$Row[caption]</span></div>";
            }
        }   
        
        echo "<div style='margin-left:700px;font-size:12px;border:1px solid #fff;background-color:#000;padding:5px;'>" . $Pages->display_pages() . "</div>";
        ?>

Again thanks for all your hrlp, this was my first ever pagination.

Regards
Billy