Recordset paging not working

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
royalblue
Forum Newbie
Posts: 13
Joined: Sun May 16, 2004 8:26 am

Recordset paging not working

Post by royalblue »

I am using Dreamweaver mx 2004, php and MYSQL:

When I insert recordet Navigation bar in dreamweaver the 'Next' and 'Previous' Text do not seem to work with my recordset.
The following PHP Code exists for the 'Previous' Text (for recordset 'Test2')

<?php if ($pageNum_Test2 > 0) { // Show if not first page ?>
<a href="<?php printf("%s?pageNum_Test2=%d%s", $currentPage, max(0, $pageNum_Test2 - 1), $queryString_Test2); ?>">Previous</a>
<?php } // Show if not first page ?>

The following code exists for the 'Next' Text for the same recordset:

<?php if ($pageNum_Test2 < $totalPages_Test2) { // Show if not last page ?>
<a href="<?php printf("%s?pageNum_Test2=%d%s", $currentPage, min($totalPages_Test2, $pageNum_Test2 + 1), $queryString_Test2); ?>">Next</a>
<?php } // Show if not last page ?>

Please help !
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

Living proof that you should not use any of the PHP functions you can get for MX
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

rip it out and use straight PHP...both MS and FP try to 'help' too much and can't possibly match what every person wants to do...

Learn to write the code by itself....you'll learn more and become a better developer...

a simple text editor is all you really need.
royalblue
Forum Newbie
Posts: 13
Joined: Sun May 16, 2004 8:26 am

Post by royalblue »

Can anybody give me the code for a simple 'Next' and 'Previous' navigation text ? I've tried searching the web but cant find any suitable PHP Code for this. Whats wrong with the code shown above?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

your code above will only work if register_globals is on
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

if you can wait a couple of hours. i have code at home that does this...i'll post it when i get home
royalblue
Forum Newbie
Posts: 13
Joined: Sun May 16, 2004 8:26 am

Post by royalblue »

Lostboy:
that would be great - thanks. I'm very new to coding ( have only been using Design view in dreamweaver mx so far) so please be gentle !

Feyd:
Please explain what you mean by register_globals being ON. How do I turn this on in Dreamweaver ?
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

Its not a DW function, its a PHP function. REgister_globals is an old function that allowed the name of an input element

Code: Select all

<input type=text NAME="myElement">
to become the variable in a PHP script without any additional work...ie

$myElement = "someValue";

This is set in the PHP.ini file and was turned off for security purposes.
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

Code: Select all

<?php
//index.php views all the available ads sorted by last_date_accessed in lots of 10
session_start();

//initialize variables
var $NextBlock             =  0;   //next block offset
var $Prev                     =  0;   //previous offset
var $Display_len           = 10;   //number of records per page
var $total                      =  0;    //total number of records
var $sql                         = "";
var $sql_results              = "";
var $sql_ads                   = "";
var $filter                       =  0;   //filter for categories

//get the db conn file
require("dbconn.php");
require("ad_common.php");
myheader();

if ($_POST['total']){
  $total = $_POST['total'];
  if(!is_numeric($total)){
    $total=0;
  }
}
if ($_POST['NextBlock']){
  $NextBlock = $_POST['NextBlock'];
  //check if its a number and less that total
  if (!is_numeric($NextBlock)){
    $NextBlock = 0;
  }
}

//set filter
if ($_POST['filter']){
  $filter = $_POST['filter'];
  $total = 0;
}

//get the values of the paging system
//If neither has a value then
if ((!$_POST['page']=="Next")&&(!$_POST['page']=="previous")){
   $NextBlock = 0;

//move forwards thru recordset results
}elseif($_POST['page']=="Next"){
   $NextBlock+=$Display_len;

//move backwards thru recordset results
}elseif($_POST['page']=="previous"){
   $NextBlock-=$Display_len;
}
//can't move back past first record
if ($NextBlock < 0 ){
   $NextBlock = 0;
}
$date = date("Y-m-d");
//total current ads in system

$sql_ads = "select count(ad_id)as rec_count from ads where ad_deleted = 0 and ad_duration >= '$date' ";
if ($filter!=0){$sql_ads.= " and ad_cat = $filter ";}

//echo $sql_ads."<BR>";
$ttl_results = mysql_query($sql_ads,$conn) or die ("Can't complete query1 because ".mysql_error());
if ($ttl_results){
    while ($row=mysql_fetch_array($ttl_results)){
      $total = $row['rec_count'];
    } //end while
} //end if



$sql = "select a.ad_id as aid, a.ad_title as title, a.ad_text as text,
        u.user_id as uid, u.user_name as user, u.user_email as email, u.user_web as web
        from ads a, users u
        where ad_deleted = 0 and ad_end_date >= now()
        and a.ad_user_id = u.user_id ";
//add in the filter if requested
if ($filter!=""){$sql.= " and ad_cat = $filter ";}

$sql.= "order by ad_last_access_timestamp desc
        limit $NextBlock, $Display_len";

$result = mysql_query($sql,$conn) or die ("Can't complete query2 because ".mysql_error());

if ($result){
    echo "<html><head>";
    echo "<form action="index.php" method="post" name="results"><table width="100%"><tr><td>";
    echo "<table width="100%" border=0><tr>";

    //calc the page
    $totalpage = (int)($total / 10);
    if(($total % 10)!=0){
      $totalpage+=1;
    }
    //current page
    $currpage = ($NextBlock / 10)+1;

    //paging
    echo "<td align="center"><b> $total Ads available (Page $currpage of $totalpage)</b><br /><br />
          <input type="hidden" name="total" value="$total">
          <input type="hidden" name="NextBlock" value="$NextBlock">";

    if ($total < $Display_len + $NextBlock + 1){ $NextStatus = " disabled ";}
    if ($NextBlock == 0){  $PrevStatus = " disabled ";}
    echo "<input type="submit" value="previous" name="page" $PrevStatus >&nbsp;&nbsp;&nbsp;";
    echo "<input type="submit" value="Next" name="page" $NextStatus>";
    echo "</td></tr><tr>";

     filter();                 //insert the filter dropdown

    $x=$NextBlock+1;
    //data
    while ($rows=mysql_fetch_array($result)){
        $aid       = $rows['aid'];
        $uid       = $rows['uid'];
        $ad_title  = $rows['title'];
        $ad_text   = $rows['text'];
        $user      = $rows['user'];
        $email     = $rows['email'];
        $web       = $rows['web'];

        echo "</tr><tr><td>
                  <table><tr><td>$x) &nbsp;$ad_title</td></tr>
                         <tr><td>$ad_text</td></tr>
                         <tr><td>Contact: $user at <a href="mailer.php?u=$uid&ad=$aid&e=$email">$email</a>
                         Visit at <a href="$web">$web</a></td></tr>
                  </table>
              </td></tr><tr><td><hr></td></tr>";
    $x++;
    }//end while
    echo "</table></form>";

}else{
   echo "<h1> No Records Found</h1>";
}//end if

echo "</body></html>";


?>
royalblue
Forum Newbie
Posts: 13
Joined: Sun May 16, 2004 8:26 am

Post by royalblue »

Many thanks for the code Lostboy.

I am however having a little trouble with inserting it. As I have already done a lot of the rcordset creating (SQL) and filtering etc in Dreamweaver I'm keen to just have a piece of PHP Code that does the recordset paging. I therefore assume I would only require a cut down version of your code below? Whenever I try to start inserting from:

//get the values of the paging system

I run into all sorts of trouble. I'm not certain what to put into ['Page'] and get errors such as:
Notice: Undefined index: Page in C:\Xitami\webpages\Search\Results1.php

when I preview in dreamweaver. I actually tried sticking in 'Results1.php' but this was no use.

How do I create a cut down version of your code for a results page = Results1.php and a Recordset = Test2. I've already got my results table set up with filtering from the recordset and a repeat region facility etc. All I really want now is two simple Text fields placed below the table ('previous' and 'next') that will allow me to scroll thru the recordset).

Sorry if I'm appearing rather amateurish but I'm very new to php and webste creation in general. This problem is really giving me a headache !

As an observation would you say that its best to use ASP with dreamweaver rather than php? Dreamweaver doesn't seem to support php very well (have previously had to download extensions to do simple Timestamp formatting).
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

Code: Select all

//get the values of the paging system 
//If neither has a value then 
if ((!$_POST['page']=="Next")&&(!$_POST['page']=="previous")){ 
   $NextBlock = 0; 

//move forwards thru recordset results 
}elseif($_POST['page']=="Next"){ 
   $NextBlock+=$Display_len; 

//move backwards thru recordset results 
}elseif($_POST['page']=="previous"){ 
   $NextBlock-=$Display_len; 
} 
//can't move back past first record 
if ($NextBlock < 0 ){ 
   $NextBlock = 0; 
} 

//then you need to find how many records there are in total
//so run the same sql query without the limit clause to get the total number of records

//calc the page 
    $totalpage = (int)($total / 10); 
    if(($total % 10)!=0){ 
      $totalpage+=1; 
    } 
    //current page 
    $currpage = ($NextBlock / 10)+1; 

//these are the buttons that provide the paging movement

    //paging 
    echo "<td align="center"><b> $total Ads available (Page $currpage of $totalpage)</b><br /><br /> 
          <input type="hidden" name="total" value="$total"> 
          <input type="hidden" name="NextBlock" value="$NextBlock">"; 

    if ($total < $Display_len + $NextBlock + 1){ $NextStatus = " disabled ";} 
    if ($NextBlock == 0){  $PrevStatus = " disabled ";} 
    echo "<input type="submit" value="previous" name="page" $PrevStatus >   "; 
    echo "<input type="submit" value="Next" name="page" $NextStatus>"; 
    echo "</td></tr><tr>";
royalblue
Forum Newbie
Posts: 13
Joined: Sun May 16, 2004 8:26 am

Post by royalblue »

Thanks again for reply but this code seem to give me all sorts of trouble when I try to insert it underneath my table. Does anybody know any code that is a littl more simplistic that I could just simply insert underneath my Table ? Can the original code be created by Dreamweaver mx (shown in original post) be amended?
Post Reply