Re: Page Numbering

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2007. 09. 19, szerda keltezéssel 11.08-kor Dan Shirah ezt írta:
> 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.

I know almost nothing about mssql but a quick googling for 'mssql select
bottom' gave me this:

http://forums.belution.com/en/sql/000/123/74.shtml

maybe you could use a query like that

greets
Zoltán Németh

> 
> 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);
> > > }
> > >
> > >
> >

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux