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" <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]> wrote:
> >>
> >> On Wed, 19 Sep 2007 10:05:40 -0400, "Dan Shirah" <[EMAIL PROTECTED]>
> >> 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
