limit rows

[gwolff2005]

Hi guys I have a problem. I limit the displayed rows by 10 (which works) and then a link saying "next" or "previous"

The link itself works, but the site always just displays teh first 10 data entries.

What do I do wrong?

Thank you!

 

<?php
$host="****"; // Host name
$username="****"; // Mysql username
$password="****"; // Mysql password
$db_name="****"; // Database name
$tbl_name="sp_users"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

//$sql="SELECT * FROM $tbl_name ORDER BY user_id";
$sql="SELECT * from sp_users,sp_schools where sp_users.user_id=sp_schools.school_id ORDER BY school_name LIMIT 0, 10";
$result=mysql_query($sql);
$num_rows=mysql_num_rows($result);
?>
<?PHP
//check if the starting row variable was passed in the URL or not
if (!isset($_GET['startrow']) or !is_numeric($_GET['startrow'])) {
  //we give the value of the starting row to 0 because nothing was found in URL
  $startrow = 0;
//otherwise we take the value from the URL
} else {
  $startrow = (int)$_GET['startrow'];
}
?>
<?PHP
//this part goes after the checking of the $_GET var
$fetch = mysql_query("SELECT * FROM sp_users,sp_schools where sp_users.user_id=sp_schools.school_id LIMIT $startrow, 10")or
die(mysql_error());
?>
<style type="text/css">
<!--
.style2 {font-weight: bold}
.style3 {
font-family: Arial, Helvetica, sans-serif;
color: #000000;
}
.style10 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10px; color: #000000; }
#Layer1 {
position:absolute;
left:803px;
top:36px;
width:65px;
height:28px;
z-index:1;
}
#Layer2 {
position:absolute;
left:707px;
top:19px;
width:143px;
height:39px;
z-index:1;
}
#Layer3 {
position:absolute;
left:247px;
top:463px;
width:175px;
height:53px;
z-index:2;
}
-->
</style>

<title>User overview</title>
</html>
<div id="Layer2">
  <form action="<?= $_SERVER['PHP_SELF'] ?>" method="post">
<input type="hidden" name="foo" value="<?= $foo ?>" />
<input type="submit" name="submit" value="Refresh" />
</form></div>
<div id="Layer3"><?PHP
//now this is the link..
echo '<a href="'.$_SERVER['PHP_SELF'].'?startrow='.($startrow+10).'">Next</a>';
?>
<?PHP
$prev = $startrow - 10;

//only print a "Previous" link if a "Next" was clicked
if ($prev >= 0)
    echo '<a href="'.$_SERVER['PHP_SELF'].'?startrow='.$prev.'">Previous</a>';
?></div>
<table width="779" border="0" align="left" cellpadding="0" cellspacing="1" bgcolor="#996600">
<tr>
<td width="777">
  
    <div align="left">
      <table width="779" border="1" cellspacing="0" cellpadding="3">
        <tr>
          <td colspan="6" align="center"><div align="center" class="style1 style3"><strong>SchoolPorta.com Users / Total: <?php echo $num_rows ?></strong></div></td>
          </tr>
        <tr>
          <td width="342" align="center"><span class="style2">school</span></td>
          <td width="62" align="center"><span class="style2">Name</span></td>
          <td width="104" align="center"><span class="style2">Lastname</span></td>
          <td width="130" align="center"><span class="style2">Email</span></td>
   		  <td width="64" align="center"><span class="style2">Update</span></td>
        </tr>
        <?php
while($rows=mysql_fetch_array($result)){
?>
        <tr>
          <td><span class="style10"><? echo $rows['school_name']; ?></span></td>
	  <td><span class="style10"><? echo $rows['user_first_name']; ?></span></td>
          <td><span class="style10"><? echo $rows['user_surname']; ?></span></td>
          <td><span class="style10"><a href="mailto:<?php echo $rows['user_login']; ?>"><?php echo $rows['user_login']; ?></a></span></td>
          <td align="center"><a href="update.php?id=<? echo $rows['user_id']; ?>" class="style10">update</a></td>
        </tr>
        <?php
}
?>
      </table>
    </div></td>
</tr>
</table>

<div align="left">
  <p>    </p>
  <p> </p>
  <p> </p>
  <p>
    <?php
mysql_close();
?>
  </p>
</div>

[bh]

Hi,

 

you always get 0..10 rows:

$sql="SELECT * from sp_users,sp_schools where sp_users.user_id=sp_schools.school_id ORDER BY school_name LIMIT 0, 10";

 

Send the actual offset (now 0) and length (now 10) to the next page (if someone click next or previous) eg in a hidden input. At next page you can get

your previous page's hidden inputs. After that you can calculate it (eg show the next 10 rows - set offset to 10)

[gwolff2005]

ok thanks... How do I do that?