SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2012
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Pagination and Sortable Table using only PHP/SQL

    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!

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2012
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Enthusiast
    Join Date
    Sep 2012
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2012
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Solved it!

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

    Btw I love your signature

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    Glad you got there in the end.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •