Re: Page Numbering

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I found the easiest solution is to use the 'Pager' package in pear
http://pear.php.net/package/Pager
Ben

On Wednesday 19 Sep 2007 15:45, T.Lensselink wrote:
> On Wed, 19 Sep 2007 10:23:58 -0400, "Dan Shirah" <mrsquash2@xxxxxxxxx>
> wrote:
> > Becase I am using MSSQL not MYSQL.  MSSQL does not have anything easy to
> > use
> > like LIMIT in MYSQL. So, to achieve the same functionality you have to
> use
> > the subqueries.
> > 
> > Having the largest number as the inner most subquery value tells the
> query
> > to retrieve the records that are equal to that number minus 10(my results
> > per page)
> > 
> > So, if the inner most query has has a value of 30, the outer query will
> > select records 21-30.
> > 
> > And, it does this just fine because if I echo out my variables I see the
> > numbers changing. But for whatever reason, the data being displayed is
> not
> > changing.
> > 
> > 
> > On 9/19/07, T. Lensselink <dev@xxxxxxxx> wrote:
> >>
> >> On Wed, 19 Sep 2007 10:05:40 -0400, "Dan Shirah" <mrsquash2@xxxxxxxxx>
> >> wrote:
> >> > Hello all,
> >> >
> >> > I am having a problem with trying to display a set amount of records
> >> from
> >> > my
> >> > result.
> >> > I have verified that the correct values for my variables are being
> >> passed
> >> > to
> >> > the query.
> >> > The calculation for the records that should be displayed per page is
> >> > correct.
> >> > The total number of records returned from my query is correct.
> >> > And the calculated number of total pages to be displayed is correct.
> >> >
> >> > So, initially it displays the first 10 results as it should, and has
> > the
> >> > pages numbers at the bottom.  The problem is, when I click on a
> >> different
> >> > page number the same 10 results are ALWAYS displayed.  Even though my
> >> > $page
> >> > variable IS being updated.
> >> >
> >> > Any ideas why my results are not reflecting the page I select?
> >> >
> >> >
> >> > <?php
> >> > if(!isset($_GET['page'])){
> >> >     $page = 1;
> >> >  } else {
> >> >     $page = $_GET['page'];
> >> >  }
> >> >  // Define the number of results per page
> >> >  $max_results = 10;
> >> >  // Figure out the limit for the query based
> >> >  // on the current page number.
> >> >  $from = (($page * $max_results) - $max_results);
> >> >  echo $from."FROM";
> >> >  $page_results = $max_results + $from;
> >> >  echo $page_results."PAGE RESULTS";
> >> >   // Query the table and load all of the records into an array.
> >> >    $sql = "SELECT DISTINCT * FROM (
> >> >     SELECT TOP $max_results Value1, Value2 FROM (
> >> >      SELECT TOP $page_results Value1,
> >> >      FROM my_table
> >> >      WHERE my_table.column = 'P'
> >> >     ) as newtbl order by credit_card_id desc
> >> >    ) as newtbl2 order by credit_card_id asc";
> >> >
> >> >     print_r ($sql);
> >> >   $result = mssql_query($sql) or die(mssql_error());
> >> >          //print_r ($result);
> >> >   $number_rows = mssql_num_rows($result);
> >> > ?>
> >> > <table width='780' border='1' align='center' cellpadding='2'
> >> > cellspacing='2'
> >> > bordercolor='#000000'>
> >> > <?php
> >> > if(!empty($result)) {
> >> >  while ($row = mssql_fetch_array($result)) {
> >> >   $id = $row['credit_card_id'];
> >> >   $dateTime = $row['date_request_received'];
> >> >   //print_r ($id_child);
> >> > ?>
> >> > <tr>
> >> > <td width='88' height='13' align='center' class='tblcell'><div
> >> > align='center'><?php echo "<a
> > href='javascript:editRecord($id)'>$id</a>"
> >> > ?></div></td>
> >> > <td width='224' height='13' align='center' class='tblcell'><div
> >> > align='center'><?php echo "$dateTime" ?></div></td>
> >> > <td width='156' height='13' align='center' class='tblcell'><div
> >> > align='center'><?php echo "To Be Processed" ?></div></td>
> >> > <td width='156' height='13' align='center' class='tblcell'><div
> >> > align='center'><?php echo "Last Processed By" ?></div></td>
> >> > </tr>
> >> > <?php
> >> >  }
> >> > }
> >> > ?>
> >> > </table>
> >> > <table align="center" width="780" cellpadding="2" cellspacing="2"
> >> > border="0">
> >> > <tr>
> >> > <td width='780' height='15' align='center' class='tblcell'><div
> >> > align='center'><strong>Results: </strong><?php echo "$number_rows";
> >> > ?></div></td>
> >> > </tr>
> >> > </table>
> >> > <?php
> >> > // Figure out the total number of results in DB:
> >> > $sql_total= "SELECT * FROM my_table WHERE my_table.column = 'P'";
> >> > $tot_result = mssql_query($sql_total) or die(mssql_error());
> >> > $total_results = mssql_num_rows($tot_result) or die(mssql_error());
> >> > // Figure out the total number of pages. Always round up using ceil()
> >> > $total_pages = ceil($total_results / $max_results);
> >> > echo $max_results."Results";
> >> > echo $total_results."Total";
> >> > echo $total_pages."pages";
> >> > // Build Page Number Hyperlinks
> >> > echo "<center>Select a Page<br />";
> >> > // Build Previous Link
> >> > if($page > 1){
> >> >     $prev = ($page - 1);
> >> >     echo "<a
> > href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<Previous</a>
> >> > ";
> >> > }
> >> >
> >> > for($i = 1; $i <= $total_pages; $i++){
> >> >     if(($page) == $i){
> >> >         echo "$i ";
> >> >         } else {
> >> >             echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a>
> > ";
> >> >     }
> >> > }
> >> > // Build Next Link
> >> > if($page < $total_pages){
> >> >     $next = ($page + 1);
> >> >     echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next>></a>";
> >> > }
> >> > echo "</center>";
> >> > ?>
> >>
> >> I think it returns the same 10 records because of TOP $max_results.
> >> This will always get the first 10 records. Probably better to use LIMIT.
> >>
> >> Why are there so much subqueries needed to get the result set?
> >> Why not something like this:
> >>
> >> SELECT DISTINCT * FROM my_table WHERE my_table.column = 'p' ORDER BY
> >> credit_card_id DESC LIMIT $page_results, $max_results
> >>
> 
> Dan,
> 
> Thanx for the explenation. I should have asked what DB you are using.
> 
> So if you wanna select rows from 10 to 20 with a limit of 10 the query will
> be
> something like this? 
> 
> SELECT DISTINCT * FROM (
>      SELECT TOP 10 Value1, Value2 FROM (
>       SELECT TOP 20 Value1,
>      FROM my_table
>      WHERE my_table.column = 'P'
>      ) as newtbl order by credit_card_id desc
>     ) as newtbl2 order by credit_card_id asc
> 
> If you watch at the output of print_r($result) you get different results
> for every page? 
> 
> -- 
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 
> 
> 

-- 
We Design Shropshire
http://www.sparkcomputing.co.uk

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux