That gives me an array of the 10 records that are being displayed every
single time.
It's like even though the variables in my query are changing correctly from
0,10 to 10,20; the records being displayed are not updating.
The URL is reflecting the changes from page 1 to 2 correctly also.
On 9/19/07, T. Lensselink <dev@xxxxxxxx> wrote:
>
> On Wed, 19 Sep 2007 10:48:20 -0400, "Dan Shirah" <mrsquash2@xxxxxxxxx>
> wrote:
> > Actually, the query you mentioned will select records 11-20 because it
> > counts 10 records backwards starting with record 20.
> >
> > print_r($result) onyl returns "Resource id #3" and not the actual data.
> >
> >
> > On 9/19/07, T. Lensselink <dev@xxxxxxxx> 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?
> >>
> >>
>
> My bad. Print_r on the resource will do you no good :)
> Try this and see if the result is different on each page?
>
> while ($row = mssql_fetch_array($result)) {
> print_r($row);
> }
>
>
