One of the queries I run on a database on my web site returns all the results which are now over 2200 rows and growing. I need to display say 50 at a time with a 'next page' link. How do I do this. This is the script.
<?php

// change location, user-id and password

if(!isset($query) || empty($query))
($query= "select id as ID, Owner, Country, Model, Year, Mileage from <table> order by Owner asc");
$query = stripslashes($query);
$db_link = mysql_connect('hostname', 'username', 'password')
or die("Could not connect to database. Please try again later.");
mysql_select_db('dbname') or
die("could not connect to database");
$result = mysql_query($query) or
die( mysql_error() );
$number_cols = mysql_num_fields($result);

echo "<table width=740 border=1 align=center cellspacing=0 cellpadding=5>\n";
echo "<tr align=center>\n";
echo "<font face=verdana>";
echo "<font size=-1>";
for ($i=0; $i<$number_cols; $i++)
{
echo "<th class=hmcth>" .mysql_fieldname($result, $i). "</th>\n";
}
echo "</tr>\n";

while ($row = mysql_fetch_row($result))
{
echo "<tr align=left>\n";

for ($i=0; $i<$number_cols; $i++)
{
echo "<td>";
echo "<font face=verdana>";
echo "<font size=-1>";
if (!isset($row[$i]))
{echo "n.a.";}
else
{echo $row[$i];}
echo "</td>\n";
}
echo "</tr>\n";
}
echo "</table>";
echo "</font>";
mysql_close($db_link);
?>

Bob

instead of using a for loop to show the rows, use a LIMIT inside the query

like

$query= "select id as ID, Owner, Country, Model, Year, Mileage from <table> order by Owner asc LIMIT $starting_number,$finishing_number");

Thanks. The script in my original post was done for me some time ago and my understanding of MySQL is still pretty basic. How would I implement this. You can see the current output HERE (http://www.volvoclub.org.uk/highmileage/country_search.php), I just need to break it up into smaller pages of about 50 rows each with Page 1,2,3 etc links .

Bob

<?php

class Pagination {

var $CurrentPage;
var $ItemCount;
var $ItemsPerPage;

var $TotalPages;

var $NextLink = true;
var $PrevLink = true;

var $LinkHref;
var $LinkSeperator;

var $Query;
var $LimitQuery;

var $RunQueryOne;
var $PageQuery;

function SetCurrentPage($page)
{
if ($page > 0)
{
$this->CurrentPage = (int) $page;
} else
{
$this->CurrentPage = (int) 1;
}
}

function SetLinkHref($href)
{
$this->LinkHref = $href;
}

function SetLinkSeperator($sep)
{
$this->LinkSeperator = $sep;
}

function SetQuery($query)
{
$this->Query = $query;
}

function SetItemsPerPage($items)
{
$this->ItemsPerPage = (int) $items;
}

function SetLimitQuery()
{
//(($this->CurrentPage-1)*$this->ItemsPerPage)
$this->LimitQuery = " LIMIT " . (($this->CurrentPage-1)*$this->ItemsPerPage) . ", " . $this->ItemsPerPage;
}

function GetTotalPages()
{
$this->TotalPages = (int) ceil($this->ItemCount/$this->ItemsPerPage);
}

function BuildQuery($stage)
{
if ($stage == 1)
{
$this->RunQueryOne = mysql_query($this->Query);
} else
{
$this->PageQuery = mysql_query($this->Query . $this->LimitQuery);
//$this->PageQuery = $this->Query . $this->LimitQuery;
}
}

function UpdateQueryString($page)
{
//$pattern = array('/page=[^&]*&?/', '/&$/');
//$replace = array('', '');
//$queryString = preg_replace($pattern, $replace, $_SERVER['QUERY_STRING']);

if (preg_match("/page=/", $_SERVER['QUERY_STRING']))
{
$queryString = preg_replace("/page=(\d+)/", "page=" . $page, $_SERVER['QUERY_STRING']);
} else
{
$queryString = $_SERVER['QUERY_STRING'] . "&page=" . $page;
}

return "?" . $queryString;
}

function GetTotalItems()
{
if ($this->RunQueryOne == null)
{
$this->BuildQuery(1);
}
$this->ItemCount = (int) mysql_num_rows($this->RunQueryOne);
}

function PageLinks()
{
if ($this->PrevLink != false)
{
if ($this->CurrentPage != '1')
{
echo "<a href=\"" . $this->LinkHref . $this->UpdateQueryString($this->CurrentPage-1) . "\">&laquo;Previous</a>&nbsp;&nbsp;";
} else
{
echo "&laquo;Previous&nbsp;&nbsp;";
}
}

for ($i = 1; $i<$this->TotalPages+1; $i++)
{
if ($this->CurrentPage == $i)
{
if ($i != $this->TotalPages)
{
echo "[" . $i . "]" . "&nbsp;" . $this->LinkSeperator . "&nbsp;";
} else
{
echo "[" . $i . "] &nbsp;";
}
} else
{
if ($i != $this->TotalPages)
{
echo "<a href=\"" . $this->LinkHref . $this->UpdateQueryString($i) . "\">" . $i . "</a>" . "&nbsp;" . $this->LinkSeperator . "&nbsp;";
} else
{
echo "<a href=\"" . $this->LinkHref . $this->UpdateQueryString($i) . "\">" . $i . "</a>" . "&nbsp;";
}
}
}

if ($this->NextLink != false)
{
if ($this->CurrentPage != $this->TotalPages)
{
echo "&nbsp;<a href=\"" . $this->LinkHref . $this->UpdateQueryString($this->CurrentPage+1) . "\">Next&raquo;</a>&nbsp;";
} else
{
echo "&nbsp;Next&raquo;&nbsp;";
}
}
}

function Paginate($query)
{
if (empty($this->LinkHref)) { $this->LinkHref = $_ENV['PHP_SELF']; }
if (empty($this->LinkSeperator)) { $this->LinkSeperator = "|"; }
if (empty($this->ItemsPerPage)) { $this->SetItemsPerPage('10'); }
$this->SetCurrentPage($_GET['page']);
$this->SetQuery($query);
$this->BuildQuery(1);
$this->GetTotalItems();
$this->GetTotalPages();
$this->SetLimitQuery();

$this->BuildQuery(2);
}
}
?>


This is what I use for pagination. If you cant work out how to use it, let me know and I'll write an example

Thanks. Sorry for the delay replying. I'd be gratefull if you could show by example how I could implement this based on my post #1.

Bob