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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
