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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> wrote: > > > > On Wed, 19 Sep 2007 10:48:20 -0400, "Dan Shirah" <[EMAIL PROTECTED] > > > 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 <[EMAIL PROTECTED]> wrote: > > >> > > >> On Wed, 19 Sep 2007 10:23:58 -0400, "Dan Shirah" <[EMAIL PROTECTED] > > > > > >> 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 <[EMAIL PROTECTED]> wrote: > > >> >> > > >> >> 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 > > >> >> > > >> > > >> 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); > > } > > > > >