Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 6 of 6
  1. #1
    Junior Member
    Join Date
    Jul 2007
    Location
    Orlando Florida
    Posts
    20
    Member #
    15537
    I couldn't find anything on this one, but it is so common. I must be looking with some wrong terms. So you have a database with 100 records in it. You want the user to view, let us say, 10 at a time. Of course that variable would be pulled in from a form. I have some code posted that works, but just wanted to see if there is a better way to do it, especially as it pertains to performance. I didn't add all of the sorting variable, but is there a better way to structure it, rather than hard coding the variable in if statements.

    PHP Code:
    <?php
    include 'sql.php';
    $page=$_GET['pid'];
    if(
    $page==""){
        
    $page=1;
    }

    $sort=$_GET['sort'];
    if(
    $sort==""){
        
    $sort="id";
    }

    $db=mysql_connect($host,$user,$pass);
    mysql_select_db($database,$db);

    $sql="SELECT `id` FROM `post`";
    $result=mysql_query($sql);

    $num_rows=mysql_num_rows($result);
    mysql_free_result($result);

    $sort_num=10;//<--- pull in from a form variable eg 10,20,30,...
    $num_pages=ceil($num_rows/$sort_num);
    $current_end=$page*$sort_num;
    $current_start=$current_end-$sort_num;

    $sql="SELECT * FROM `post` WHERE 1 ORDER BY `".$sort."` LIMIT ".$current_start.",".$sort_num;
    $result=mysql_query($sql,$db);
    $output_array=array();
    while(
    $val=mysql_fetch_array($result,MYSQL_ASSOC)){
        
    array_push($output_array,$val);
    }


    //print out meta and head html
    echo("
    <!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">
    <html xmlns=\"http://www.w3.org/1999/xhtml\">
    <head>
    <meta http-equiv=\"Content-Type\" content=\"text/html; charset=ISO-8859-1\" />
    <title>Split</title>
    </head>

    <body>\n"
    );
    //print out sort navigation
    for($i=1;$i<=$num_pages;$i++){
        echo(
    "\t<a href='?pid=".$i."&sort=".$sort."'>".$i."</a> | \n");
    }
    echo(
    "\n\t<br />\n");
    //print out selected page
    for($i=0;$i<sizeof($output_array);$i++){
        echo(
    "\n\t".$output_array[$i]['id'].". ".$output_array[$i]['name']."<br />");
    }
        
    echo(
    "\n\t<p>\n\t\t<a href='?pid=".$page."&sort=id'>sort by id</a> ::\n\t\t<a href='?pid=".$page."&sort=name'>sort by name</a>\n\t</p>");
        
    //close html
    echo("\n</body>\n</html>");
    ?>

  2.  

  3. #2
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,657
    Member #
    1234
    Liked
    139 times
    google "pagination"
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  4. #3
    Junior Member
    Join Date
    Jul 2007
    Location
    Orlando Florida
    Posts
    20
    Member #
    15537
    Quote Originally Posted by Wired
    google "pagination"
    found this article for anyone else interested. http://www.phpfreaks.com/tutorials/43/0.php . Thanks for the vocab. I think I did it pretty well, just use the count(*) in SQL rather than query the entire database and use mysql_num_rows on the result. Thanks again for your help.

  5. #4
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    The LIMIT clause is the only way to do it.

    If you're looking to improve performance, you can use subqueries to restrict how much data you're querying, but that can get overly complex.

    PS - you can eliminate the "WHERE 1" clause from your query - it's not doing anything.

    PPS - make sure all your sortable columns are indexed to optimize performance further.

  6. #5
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Quote Originally Posted by Prajna
    I think I did it pretty well, just use the count(*) in SQL rather than query the entire database and use mysql_num_rows on the result.
    Ah, yeah, missed that part... you definitely want to replace a query of all records with a count... that'll improve performance on that query by 99% ++

  7. #6
    Junior Member
    Join Date
    Jul 2007
    Location
    Orlando Florida
    Posts
    20
    Member #
    15537
    Quote Originally Posted by transio
    Ah, yeah, missed that part... you definitely want to replace a query of all records with a count... that'll improve performance on that query by 99% ++
    Yea, I'm using the limit as well in the SQL. But thanks for the WHERE 1. (silly rookie I am). I'll confess, I test my SQL in phpMyAdmin before I place them in, and it loves the WHERE 1. It is very redundent (not limiting on performace I wouldn't think), but non-the-less....WHERE true is always everything and would probably just show my SQL age to other developers. Thanks for the heads up.


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 08:01 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com