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
