Pagination and Sortable Table using only PHP/SQL

**Shibbs** · Sep 24, 2012 00:26

Hey,

so I have tried for about 5 hours to figure this out but to no avail.

My table can currently sort the data from my databae but I cannot use pagination. I have created a pagination code and it works perfectly but combining the two has proven impossible.

Here are both codes:

Pagination code:

Code:

<html>
</html>
<?php
//Connecting to Banner DB
include('config.php') ;

//Preparing variables
if(!isset( $_GET['p']) ){$_GET['p'] =0;} 
$per_page=  5;
$sql= "SELECT url FROM `banner_clicks`" ;
$sql2= "SELECT url FROM `banner_clicks` ORDER BY url ASC LIMIT ".$_GET['p'].",". $per_page;
$query= mysql_query ($sql2) ;
$rows= mysql_num_rows ( mysql_query ($sql)) ;
$pages= ceil($rows / $per_page);

while ($fetch=mysql_fetch_assoc($query) ){
echo'<p>'. $fetch['url'] . '</p>';
}

for ( $i=0; $i<$pages; $i++){
echo ' <a href="pagination.php?p=' . ($i * $per_page) . '">'.($i +1). '</a>';

}

And here is the Sort Code(connection to DB is included but not in the part of the co:

Code:

<?php

    if ( isset ( $_GET['order_var'] ) )
    {
            $order_var = $_GET['order_var'];
           
            switch ( $order_var )
            {
                    case 'fn_asc':
                            $order_var = "url ASC";
                            break;
                    case 'fn_dsc':
                            $order_var = "url DESC";
                            break;
                    case 'ln_asc':
                            $order_var = "provby ASC";
                            break;
                    case 'ln_dsc':
                            $order_var = "provby DESC";
                            break;
                    case 'id_asc':
                            $order_var = "Description ASC";
                            break;
                    case 'id_dsc':
                            $order_var = "Description DESC";
                            break;
            }
    }
    else
    {
            $order_var = "provby ASC";  //default setting
    }
          
  

    $query = "SELECT * FROM `banner_clicks` ORDER BY $order_var" or die('error getting table');
    print $query;
    $result = mysqli_query($dbcon, $query) or die('error getting table');
	
     //here start the links
     
    $id_var = "id_asc";
    $fn_var = "fn_asc";
    $ln_var = "ln_asc";
     
    if ( $order_var == "Description ASC" )
    {
            $id_var = "id_dsc";
    }
    else if ( $order_var == "Description DESC" )
    {
            $id_var = "id_asc";
    }
     
    if ( $order_var == "url ASC" )
    {
            $fn_var = "fn_dsc";
    }
    else if ( $order_var == "url DESC" )
    {
            $fn_var = "fn_asc";
    }
     
    if ( $order_var == "provby ASC" )
    {
            $ln_var = "ln_dsc";
    }
    else if ( $order_var == "provby DESC" )
    {
            $ln_var = "ln_asc";
    }
     
     //table starts
	
    print '
    <table>
    <tr>
    <td><a href="zine.php?order_var=' . $id_var . '">ID</a></td>
    <td><a href="zine.php?order_var=' . $fn_var . '">Links</a></td>
    <td><a href="zine.php?order_var=' . $ln_var . '">Provided By</a></td>
    </tr>';
    while ( $row = mysqli_fetch_assoc ( $result ) ) //clicks are included in the ahref tag and are matched with the url
    {
            print '
            <tr>
            <td bgcolor="gray"><a href=click.php?id='.$row['id'].'">' . $row['url'] . '</td> 
            <td>' . $row['provby'] . '</td>
            <td>' . $row['Description'] . '</td>
            </tr>';
    }
      
    print '</table>';
     
    ?>

As you can see the variable order_var is used to order the table. I tried to change the following line

Code:

$query = "SELECT * FROM `banner_clicks` ORDER BY $order_var" or die('error getting table');

to

Code:

$query = "SELECT * FROM `banner_clicks` ORDER BY $order_var LIMIT $pages".$_GET['p'].",". $per_page;" or die('error getting table');

and adding the necessary lines my table could be ordered and was paginated but displayed no data....

any help is greatly appreciated, going to bed now will answer as soon as I get up!

**StarLion** · Sep 24, 2012 07:10

Code:

 echo ' <a href="pagination.php?p=' . ($i * $per_page) . '">'.($i +1). '</a>';

=>

Code:

 echo ' <a href="pagination.php?p=' . ($i * $per_page) . '&order_var='.$order_var.'">'.($i +1). '</a>';

And...

Code:

$sql2= "SELECT url FROM `banner_clicks` ORDER BY url ASC LIMIT ".$_GET['p'].",". $per_page;

=>

Code:

$sql2= "SELECT url FROM `banner_clicks` ORDER BY ".$order_var." LIMIT ".$_GET['p'].",". $per_page;

The page structure should be:
Connect to DB
Do Order_Var set logic
Do Pagination Logic
Use $sql2 result from paginiation to output current page's results.

You dont need any of the 'else if's, because the variables already hold those values by default.

**Shibbs** · Sep 24, 2012 12:57

Thanks for the reply!

So I did what you told me to and get the following error message form the die command 'error getting table'

Here is how I set up the pagination and sql2

Code:

if(!isset( $_GET['p']) ){$_GET['p'] =0;} 
   $per_page=  5;
   $sql= "SELECT url FROM `banner_clicks`" ;
   $rows= mysql_num_rows ( mysql_query ($sql)) ;
   $pages= ceil($rows / $per_page);    
    $sql2= "SELECT url FROM `banner_clicks` ORDER BY ".$order_var." LIMIT ".$_GET['p'].",". $per_page or die ('sql2 not working');
	$query= mysql_query ($sql2) ; 
    print $query;
    $result = mysqli_query($dbcon, $query) or die('error getting table');

I am new to php/sql so this setup might look ridiculous.

Thank you so much for your help.
Shibbs

**Shibbs** · Sep 24, 2012 13:22

I also tried displaying the data in the following way. Now what happens is that I have a table with pagination and I can order it. However it only shows the Urls
and the click.php function is not working either. (The for loop on the bottom is working perfectly though).

Also one thing I noticed is if I change my_sql_fetch_assoc to my_sqli_fetch_assoc it is not working.

Code:

   if(!isset( $_GET['p']) ){$_GET['p'] =0;} 
   $per_page=  5;
   $sql= "SELECT url FROM `banner_clicks`" ;
   $rows= mysql_num_rows ( mysql_query ($sql)) ;
   $pages= ceil($rows / $per_page);    
    $sql2= "SELECT url FROM `banner_clicks` ORDER BY ".$order_var." LIMIT ".$_GET['p'].",". $per_page or die ('sql2 not working');
	$query= mysql_query ($sql2) ; 
    
    
	
     //here start the links
     
    $id_var = "id_asc";
    $fn_var = "fn_asc";
    $ln_var = "ln_asc";
     
  
     
     //table starts
	
    print '
    <table>
    <tr>
    <td><a href="zine.php?order_var=' . $id_var . '">ID</a></td>
    <td><a href="zine.php?order_var=' . $fn_var . '">Links</a></td>
    <td><a href="zine.php?order_var=' . $ln_var . '">Provided By</a></td>
    </tr>';
    while ( $row = mysql_fetch_assoc ( $query ) ) //clicks are included in the ahref tag and are matched with the url
    {
            print '
            <tr>
            <td bgcolor="gray"><a href=click.php?id='.$row['id'].'">' . $row['url'] . '</td> 
            <td>' . $row['provby'] . '</td>
            <td>' . $row['Description'] . '</td>
            </tr>';
    }
      
    print '</table>';
   for ( $i=0; $i<$pages; $i++){
   echo ' <a href="zine.php?p=' . ($i * $per_page) . '&order_var='.$order_var.'">'.($i +1). '</a>';
}

I will be online for quite a while so I can respond quickly!
Thanks
Shibbs

**Shibbs** · Sep 24, 2012 13:41

Solved it!

Thank you so much StarLion you really helped me a lot.

Btw I love your signature

**StarLion** · Sep 25, 2012 05:26

Glad you got there in the end.

User Tag List

Thread: Pagination and Sortable Table using only PHP/SQL

Thread Tools

Display

Pagination and Sortable Table using only PHP/SQL

Bookmarks

Bookmarks

Posting Permissions