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?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
