I have my query echoing out. Upon initial display it looks like this:
SELECT DISTINCT * FROM ( SELECT TOP 10 Value1, Value2 FROM ( SELECT TOP 10
Value1, Value2 FROM my_table WHERE my_table.column = 'P' ) as newtbl order
by PKEY desc ) as newtbl2 order by PKEY asc
And then when I click on the link to take it to page 2, it looks like this:
SELECT DISTINCT * FROM ( SELECT TOP 10 Value1, Value2 FROM ( SELECT TOP 20
Value1, Value2 FROM my_table WHERE my_table.column = 'P' ) as newtbl order
by PKEY desc ) as newtbl2 order by PKEY asc
But still the same problem of the displayed results being the same every
time.
On 9/19/07, Dan Shirah <mrsquash2@xxxxxxxxx> wrote:
>
> 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);
> > }
> >
> >
>
