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
