Paginition

[davieboy]

i have got a code and it displays the data, only 2 results as it should

if i click on one of the numbers at the bottom (results pages) it jsut gives the same two results

http://places4sale.net/results.php?area=Macclesfield&page=1

[The_Chancer]

Using MySQL ? Are you passing the right info the the LIMIT clause ?

Bit of code might be of help 🙂

[davieboy]

its off a tutorial cos totally new to the pagything

<?php 

// Database Connection 
include 'config.php'; 
$db = mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db ($db_name) or die ("Cannot connect to database");

// If current page number, use it 
// if not, set one! 

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); 

// Perform MySQL query on only the current page number's results 

$area = $_GET['area'];
$query = "SELECT * FROM clients WHERE town = '$area' ORDER BY id";
$sql = mysql_query($query)or die (mysql_error());
while($r=mysql_fetch_array($sql))
{
$id=$r["id"];
$town=$r["town"];
$area=$r["area"];
$price=$r["price"];
$style=$r["style"];
$rooms=$r["rooms"];
$agent=$r["agent"];
$address=$r["address"];
$town=$r["town"];
$pcode=$r["pcode"];
$description=$r["description"];
$agent=$r["agent"];
// Now lets display all the contect for each file
echo "<table width=\"100%\"  border=\"0\" cellpadding=\"0\" cellspacing=\"0\">
  <tr>
    <td width=\"12%\" height=\"104\"  bgcolor=\"#CCCCCC\"><a href=\"fullinfopage.php?id=$id\"><img src=\"/images/$id.jpg\" width=\"135\" height=\"101\" border=\"0\"></a></td>
    <td width=\"88%\" valign=\"top\"><table width=\"100%\"  border=\"0\" cellpadding=\"0\" cellspacing=\"0\">
      <tr>
        <td height=\"21\" bgcolor=\"#CCCCCC\"><font face=verdana size=1>$rooms bedrooms</td>
        <td  bgcolor=\"#CCCCCC\"><font face=verdana size=1>Price: $price</td>
        <td  bgcolor=\"#CCCCCC\"><font face=verdana size=1>Style: $style</td>
      </tr>
    </table>    <table width=\"100%\" height=\"71\"  border=\"0\" cellpadding=\"0\" cellspacing=\"0\">
      <tr>
        <td valign=\"top\"><table width=\"40%\"  border=\"0\" cellpadding=\"0\" cellspacing=\"0\">
          <tr>
            <td><font face=verdana size=1>$address, $town </td>
          </tr>
        </table>          <table width=\"100%\" height=\"30\"  border=\"0\" cellpadding=\"0\" cellspacing=\"0\">
          <tr>
            <td valign=\"top\"><font face=verdana size=1>$description<br>
			<a href=\"fullinfopage.php?id=$id\"><font face=verdana size=1>Veiw Full Details</a></td>
          </tr>
        </table></td>
      </tr>
    </table></td>
  </tr>
</table>
<table width=\"100%\"  border=\"0\" cellpadding=\"0\" cellspacing=\"0\">
  <tr>
    <td  bgcolor=\"#CCCCCC\"><font face=verdana size=1>For sale by, $agent</td>
  </tr>
</table><br></font>";
}

// Figure out the total number of results in DB: 
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM pages"),0); 

// Figure out the total number of pages. Always round up using ceil() 
$total_pages = ceil($total_results / $max_results); 

// Build Page Number Hyperlinks 
echo "<center><font face=verdana size=1>Select a Page<br />"; 
$areas = $_GET['area'];
// Build Previous Link 
if($page > 1){ 
    $prev = ($page - 1); 
    echo "<a href=\"".$_SERVER['PHP_SELF']."?area=$areas&page=$prev\"><font face=verdana size=1><<Previous</a>&nbsp;"; 
} 

for($i = 1; $i <= $total_pages; $i++){ 
    if(($page) == $i){ 
        echo "$i&nbsp;"; 
        } else { 
            echo "<a href=\"".$_SERVER['PHP_SELF']."?area=$areas&page=$i\"><font face=verdana size=1>$i</a>&nbsp;"; 
    } 
} 

// Build Next Link 
if($page < $total_pages){ 
    $next = ($page + 1); 
    echo "<a href=\"".$_SERVER['PHP_SELF']."?area=$areas&page=$next\"><font face=verdana size=1>Next>></a>"; 
} 
echo "</center>"; 
?> 

[The_Chancer]

Change:

$query = "SELECT * FROM clients WHERE town = '$area' ORDER BY id"; 

to

$query = "SELECT * FROM clients WHERE town = '$area' ORDER BY id LIMIT $from,$max_results"; 

Look at the select syntax at www.mysql.com/select and read up on the LIMIT clause..

Bear in mind that LIMIT 5,10 will give results 6-15 in the row, as, like with arrays, they count from 0....

[davieboy]

are you talking there meaning so it just shows how many results pages there are, not the total number?
if not how do i do that?

[The_Chancer]

The code you originally had produces the number of pages (which I assume is correct...) and then when you click on a page number it passes this through. It then works out the $from variable (which isn't used anywhere else...) and should be used in the limit clause to give the start point for the results.

The $max_results shows how many to show per page - and gives 10, so you want to start $from and give $max_results.

At the moment, you are getting all the rows in the table that are fit the SQL statement.

Does that make it any clearer ?

[davieboy]

i understand, but there are 4 results in the db, but it gives like 10 pages

[The_Chancer]

This line

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM pages"),0); 

Sets the number of rows in the table, and the number of pages is then worked out.

I take it that in the pages table - you have about 100 rows then (100/10 = 10pages)

Change this line, so it correlates with the sql statement you made earlier.. which makes it

$total_results = mysql_result(mysql_query("SELECT * FROM clients WHERE town = '$area' ORDER BY id"),0);

So if there are only 4 rows, it should only give you one page...