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
