Sorting queries into pages

[internetguy]

I have a large product list, so I have broken down the products into classes, but there are still about a hundred or so. I want to only show 20 per page, and then have a link to go to the next 20 results. I am not sure how to do this efficiently, I was thinking multiply an integer by 20 and passing the number through a get statement, but I se problems that way. Please let me know what method is best, thanks.

 

[bastienk]

i use hidden fields and a submit or you can use links, it really doesn't matter...

can post my code when i get home later if you like


Bastien

Cat, the other other white meat

 

[internetguy]

oh thanks man that would be great. I hate making dozens of lines of code that could be summed up in 3, haha.

 

[ingresman]

We need a pattern/snippet repository

 

[internetguy]

Sorry but what do you mean by snippet or repository? A sample of my code?

 

[bastienk]

repository is a place on this site to dump code to for other to explore

//the vars
$NextBlock             = 0;   //next block offset
$Display_len           = 10;   //number of records per page



//get the data from the form
if ($_POST['NextBlock']){
  $NextBlock = $_POST['NextBlock'];
  //check if its a number and less that total
  if (!is_numeric($NextBlock)){
    $NextBlock = 0;
  }
}

//get the values of the paging system (the button values)
//If neither has a value then
if ((!$_POST['page']=="Next")&&(!$_POST['page']=="previous")){
   $NextBlock = 0;

//move forwards thru recordset results
}elseif($_POST['page']=="Next"){
   $NextBlock=+$Display_len;

//move backwards thru recordset results
}elseif($_POST['page']=="previous"){
   $NextBlock=-$Display_len;
}
//can't move back past first record
if ($NextBlock < 0 ){
   $NextBlock = 0;
}

the sql

$sql = "select a.ad_id as aid, a.ad_title as title, a.ad_text as text,
        u.user_id as uid, u.user_name as user, u.user_email as email, u.user_web as web
        from ads a, users u
        where ad_deleted = 0 and ad_duration >= now()
        and a.ad_user_id = u.user_id 
        order by ad_last_access_timestamp desc
        limit $NextBlock, $Display_len";

the elements on the page

//calc the page
    $totalpage = (int)($total / 10);
    if(($total % 10)!=0){
      $totalpage+=1;
    }
    //current page
    $currpage = ($NextBlock / 10)+1;

    //paging
    echo "<td align=\"center\"><b> $total Ads available (Page $currpage of $totalpage)</b><br /><br />
          <input type=\"hidden\" name=\"total\" value=\"$total\">
          <input type=\"hidden\" name=\"NextBlock\" value=\"$NextBlock\">";

    if ($total < $Display_len + $NextBlock + 1){ $NextStatus = " disabled ";}
    if ($NextBlock == 0){  $PrevStatus = " disabled ";}
    echo "<input type=\"submit\" value=\"previous\" name=\"page\" $PrevStatus >&nbsp;&nbsp;&nbsp;";
    echo "<input type=\"submit\" value=\"Next\" name=\"page\" $NextStatus>";
    echo "</td></tr><tr>";

Bastien

Cat, the other other white meat

 

[internetguy]

\\vars
@ $brand = $_GET['brand'];
@ $price = $_GET['price'];
@ $class = $_GET['class'];
\\which product
<?php
$db = mysql_connect('localhost','root');
mysql_select_db('ludwig');
if(empty($class) && empty($price) && empty($brand)){
$query = "SELECT * FROM products limit 0,20";
}

if(!empty($class) && empty($brand) && empty($price)){
$query = "SELECT * FROM products WHERE `class`='".$class."'
order by class desc limit 0,20";
echo "Sorted by Class";
}

if(!empty($brand)){
$query = "SELECT * FROM products where `class`='".$class."' order by brand asc limit 0,20";
echo "Sorted by Brand";
}

if(!empty($price)){
$query = "SELECT * FROM products where `class`='".$class."' order by price desc limit 0,20";
echo "Sorted by Price";
}
$result = mysql_query($query);
$num = mysql_num_rows($result);
if($num < 1){
echo "<p>There are no products listed under your criteria, please try another search</p>";
}

for ($i = 0; $i < $num; $i++){
$row = mysql_fetch_array($result);
?>
Below here are tables that display the info, but above it just shows how I have the products sorted by which class and so on. I want to be able to show the top 20 then go to the next 20. Like on this site where u see the first number of topics, then the next and so forth.

 

[bastienk]

if you want a page where it shows the links with the number of pages ie 1 2 here 4 5 6 kinda thing then thats a little different to build...
would write this a function and pass the vars to it....


Bastien

Cat, the other other white meat

 

[internetguy]

any idea how to do that???

 

[bastienk]

trying to rewrite your page now to include it


Bastien

Cat, the other other white meat

 

[bastienk]

<?
//vars

  $NextBlock             = 0;   //next block offset
  $Display_len           = 20;   //number of records per page

  if (isset($_GET['brand']){
    $brand = $_GET['brand'];
  }
  if (isset($_GET['price']){
    $price = $_GET['price'];
  }
  if (isset($_GET['class']){
    $class = $_GET['class'];
  }

//which product

$db = mysql_connect('localhost','root');
mysql_select_db('ludwig');

/*
    One neat trick here is to include a column that will always have data in the WHERE clause
    ie a 'item_available' field which needs to be set to yes or no and skip all the 'No' values
    then the WHERE clause automatically uses AND in the statement

    Easier to code that way. I will use your code to demo what I mean

*/

  //get the data from the form
if ($_GET['NextBlock']){
  $NextBlock = $_GET['NextBlock'];
  //check if its a number
  if (!is_numeric($NextBlock)){
    $NextBlock = 0;
  }
}

  $sql_select  = "SELECT * FROM products ";
  $sql_where   = "WHERE item_available = 'Yes' ";  //that could(should be a numeric value ( 1=yes 0=no) quicker to use)
  $sql_limit   = "LIMIT $NextBlock, $Display_len";


if(!empty($class)){
  $sql_where =. " AND `class`='$class' ";
  $SortFlag = "Class";
}

if(!empty($brand)){
  $sql_orderby = " order by brand asc ";
  $SortFlag = "Brand";
}

if(!empty($price)){
  $sql_orderby = " order by price desc ";
  $SortFlag = "Price";
}
if ($SortFlag == "Class"){
   $sql_orderby = " order by class desc";
}
$query = $sql_select.$sql_where.$sql_orderby.$sql_limit;
$result = mysql_query($query);
$num = mysql_num_rows($result);
if($num < 1){
echo "<p>There are no products listed under your criteria, please try another search</p>";
}
paging($Display_len,$num,$NextBlock); //function call


for ($i = 0; $i < $num; $i++){
$row = mysql_fetch_array($result);
//rest of your code to display the data


}
function paging($RecOnPage,$TotalRec,$Block)
{
    $totalpage = (int)($TotalRec / $RecOnPage);      //base total number of pages (rounded down)
    if(($total % $RecOnPage)!=0){                    //are there few left over records? take the modulus
      $totalpage+=1;                                 //if 0 then no 'extra' records
    }                                               //if more than one, then there are more records
    //current page
    $currpage = ($Block / $RecOnPage)+1;            //current page (ie not an active link)

    for ($i=1;$i<=$totalpage;$i++){
      if ($i == $currentpage){
        echo "&nbsp;&nbsp;<b>$i</b>&nbsp;&nbsp;"; //not the active link (ie the current page)
      }else{
        echo "<a href=\"".$_SERVER['PHP_SELF']."\"?NextBlock=".($i*$RecOnPage)."><b>$i</b>";    //active link
      }
    }
}
?>

Bastien

Cat, the other other white meat