Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Problem with Next/Previous record

Codes here !

Moderators: macek, egami, gesf

Problem with Next/Previous record

Postby wmdrumaizi » Tue May 20, 2003 9:06 pm

Hi there;

I found a code regarding the Next/Previous record. It works well except it preview record with 1 less. For example:

The total record is 11, record per page is 3 so it should be displayed in the following portion:

3 | 3 | 3 | 2|

But it didn't, what it is displayed is:

2 | 2| 2 | 1 | altough the total record stated is 11

The following is my php code
Code: Select all
<?
// Set Script Variables
$DB_Host="localhost";
$DB_Name="researchers";
$DB_User="";
$DB_Pass="";
$Per_Page= 3;

// Open MySQL Connection
$Connection=mysql_connect($DB_Host, $DB_User, $DB_Pass);

// Run The Query Without a Limit to get Total result
$SQL="SELECT nama, id, org, phone, email FROM maintable WHERE nama LIKE '%$nama%' AND org LIKE '%$org%' ORDER by nama ASC";
$SQL_Query=mysql_db_query($DB_Name, $SQL);
$Total=mysql_num_rows($SQL_Query);

// Append a LIMIT clause to the SQL statement
if (!$Result_Set)
   {
   $Result_Set=0;
   $SQL.=" LIMIT $Result_Set, $Per_Page";
   }
   else
   {
   $SQL.=" LIMIT $Result_Set, $Per_Page";
   }

// Run The Query With a Limit to get result
$SQL_Query = mysql_db_query($DB_Name, $SQL);
$SQL_Rows = mysql_num_rows($SQL_Query);


//Buat table, papar bil rekod
    echo "<table border=1 align=\"center\" height=\"22\" cellspacing=\"0\" width=\"842\" bordercolor=\"#cccccc\">";
      echo "<tr align=\"left\" bordercolor=\"#cccccc\" bgcolor=\"#cccccc\">";
    echo "<td><font face=\"verdana\" color=\"#000000\" style=\"bold\" width=\"802\" size=\"2\">$Total records found</font></td>";

    //  Create Next / Prev Links and $Result_Set Value
    if ($Total>0)
       {
          if ($Result_Set<=$Total && $Result_Set>0)
          {
          $Res1=$Result_Set-$Per_Page;
          echo "<td align=\"right\"><font face=\"verdana\" color=\"#000000\" width=\"10\" size=\"2\"><A HREF=\"result3.php?Result_Set=$Res1\">&&| Previous |</a></font></td>";
          }
    if ($Result_Set>=0 && $Result_Set<$Total)
      {
      $Res1=$Result_Set+$Per_Page;
          if ($Res1<$Total)
          {
          echo "<td align=\"right\"><font face=\"verdana\" color=\"#000000\" width=\"10\" size=\"2\"><A HREF=\"result3.php?Result_Set=$Res1\">|Next Page|&&</a></font></td>";
          }
      }
      }
    echo "</tr></table>";

// Display Results using a for loop
for ($a=0; $a < $SQL_Rows; $a++)
    {
    $SQL_Array=mysql_fetch_array($SQL_Query);

    //Buat table untuk papar result
    echo "<table border=1 height=\"42\" width=\"842\" align=\"center\" bordercolor=\"#000000\" cellspacing=\"0\">";
    while ($SQL_Array = mysql_fetch_array($SQL_Query))
       {
       echo "<tr><td bordercolor=\"#339999\"><font face=\"verdana\" color=\"#000000\" size=\"2\"><a href=\"detail.php?id=".$SQL_Array[id]."\">".$SQL_Array['nama']."</a></font></td>
                 <td bordercolor=\"#339999\"><font face=\"verdana\" color=\"#000000\" size=\"2\">$SQL_Array[org]</font></td>
                 <td bordercolor=\"#339999\"><font face=\"verdana\" color=\"#000000\" size=\"2\">$SQL_Array[phone]</font></td>
                 <td bordercolor=\"#339999\"><font face=\"verdana\" color=\"#000000\" size=\"2\"><a href=\"mailto:$SQL_Array[email] \">$SQL_Array[email]</a></font></td></tr>";
       }
       echo "</table>";
       
    }
// Close Database Connection
mysql_close($Connection);
?>


I hope someone could tell me what is wrong with this code
Thanks in advance
User avatar
wmdrumaizi
New php-forum User
New php-forum User
 
Posts: 40
Joined: Thu Jan 23, 2003 5:15 pm
Location: Malaysia

Postby Redcircle » Tue May 20, 2003 11:23 pm

I see some things that are confusing.. Where did you get this script. The naming convention is a little odd. Also using OLD php programming techniques.

anyways there is an extra $SQL_Array=mysql_fetch_array($SQL_Query); on line 60

that does nothing with the data so that is pulling off one of the records.
Code: Select all
<?
// Set Script Variables
$DB_Host="localhost";
$DB_Name="researchers";
$DB_User="";
$DB_Pass="";
$Per_Page= 3;

// Open MySQL Connection
$Connection=mysql_connect($DB_Host, $DB_User, $DB_Pass);

// Run The Query Without a Limit to get Total result
$SQL="SELECT nama, id, org, phone, email FROM maintable WHERE nama LIKE '%$nama%' AND org LIKE '%$org%' ORDER by nama ASC";
$SQL_Query=mysql_db_query($DB_Name, $SQL);
$Total=mysql_num_rows($SQL_Query);

// Append a LIMIT clause to the SQL statement
if (!$Result_Set)
{
   $Result_Set=0;
   $SQL.=" LIMIT $Result_Set, $Per_Page";
}
else
{
   $SQL.=" LIMIT $Result_Set, $Per_Page";
}

// Run The Query With a Limit to get result
$SQL_Query = mysql_db_query($DB_Name, $SQL);
$SQL_Rows = mysql_num_rows($SQL_Query);


//Buat table, papar bil rekod
echo "<table border=1 align=\"center\" height=\"22\" cellspacing=\"0\" width=\"842\" bordercolor=\"#cccccc\">";
echo "<tr align=\"left\" bordercolor=\"#cccccc\" bgcolor=\"#cccccc\">";
echo "<td><font face=\"verdana\" color=\"#000000\" style=\"bold\" width=\"802\" size=\"2\">$Total records found</font></td>";

//  Create Next / Prev Links and $Result_Set Value
if ($Total>0)
{
   if ($Result_Set<=$Total && $Result_Set>0)
   {
      $Res1=$Result_Set-$Per_Page;
      echo "<td align=\"right\"><font face=\"verdana\" color=\"#000000\" width=\"10\" size=\"2\"><A HREF=\"result3.php?Result_Set=$Res1\">&&| Previous |</a></font></td>";
   }
   if ($Result_Set>=0 && $Result_Set<$Total)
   {
      $Res1=$Result_Set+$Per_Page;
      if ($Res1<$Total)
      {
         echo "<td align=\"right\"><font face=\"verdana\" color=\"#000000\" width=\"10\" size=\"2\"><A HREF=\"result3.php?Result_Set=$Res1\">|Next Page|&&</a></font></td>";
      }
   }
}
echo "</tr></table>";

// Display Results using a for loop
for ($a=0; $a < $SQL_Rows; $a++)
{
//--->> this shouldn't be here!! $SQL_Array=mysql_fetch_array($SQL_Query);
   
   //Buat table untuk papar result
   echo "<table border=1 height=\"42\" width=\"842\" align=\"center\" bordercolor=\"#000000\" cellspacing=\"0\">";
   while ($SQL_Array = mysql_fetch_array($SQL_Query))
   {
      echo "<tr><td bordercolor=\"#339999\"><font face=\"verdana\" color=\"#000000\" size=\"2\"><a href=\"detail.php?id=".$SQL_Array[id]."\">".$SQL_Array['nama']."</a></font></td>
                 <td bordercolor=\"#339999\"><font face=\"verdana\" color=\"#000000\" size=\"2\">$SQL_Array[org]</font></td>
                 <td bordercolor=\"#339999\"><font face=\"verdana\" color=\"#000000\" size=\"2\">$SQL_Array[phone]</font></td>
                 <td bordercolor=\"#339999\"><font face=\"verdana\" color=\"#000000\" size=\"2\"><a href=\"mailto:$SQL_Array[email] \">$SQL_Array[email]</a></font></td></tr>";
   }
   echo "</table>";
   
}
// Close Database Connection
mysql_close($Connection);
?>
 


I commented out the line.. see if it works
User avatar
Redcircle
Moderator
Moderator
 
Posts: 830
Joined: Tue Jan 21, 2003 10:42 pm
Location: Michigan USA

Postby wmdrumaizi » Wed May 21, 2003 11:05 pm

Thanks man; it work.....
User avatar
wmdrumaizi
New php-forum User
New php-forum User
 
Posts: 40
Joined: Thu Jan 23, 2003 5:15 pm
Location: Malaysia

Postby Redcircle » Wed May 21, 2003 11:46 pm

np
User avatar
Redcircle
Moderator
Moderator
 
Posts: 830
Joined: Tue Jan 21, 2003 10:42 pm
Location: Michigan USA


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 3 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.