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
>
>
>
