Furious5
June 3rd, 2003, 05:42 PM
I hope someone can help me I'm really struggling with some code to return paged results in PHP.
I am trying to return 1 page per result, at the moment I have got the code to return the results and show each result on an individual page.
My problem occurs when I try to view the next page. If I click the link, it will refresh result1.php and therefore should show the next result. Instead it seems to run the query again and shows the same result as page one.
Any ideas? Here is the code to have a look at:
<?
/* you could put these following variables in a seperate file and include them on the page. That's how I normally do it, I only changed it to look like this for the example */
$server = "localhost";
$username = "user";
$password = "password";
$database = "jobclever";
$table = "job";
// Include Functions
include("functions.php");
// Connect To MySQL Server
@mysql_connect($server, $username, $password) or die("Couldn't Connect to Database");
// Select Database
@mysql_select_db($database) or die("Couldn't Select Database");
// set number of results to display per page
$pagelimit = "1";
// run query (change yourtable to the name of your table)
$strSQL = mysql_query("SELECT * FROM $table");
// count number of matches
$totalrows = mysql_num_rows($strSQL);
// determine how many pages there will be by using ceil() and dividing total rows by pagelimit
$pagenums = ceil ($totalrows/$pagelimit);
// if no value for page, page = 1
if ($page==''){
$page='1';
}
// create a start value
$start = ($page-1) * $pagelimit;
// blank matches found
echo "<b>" . $totalrows . " matches found</b><br>\n";
// Showing Results 1 to 1 (or if you're page limit were 5) 1 to 5, etc.
$starting_no = $start + 1;
if ($totalrows - $start < $pagelimit) {
$end_count = $totalrows;
} elseif ($totalrows - $start >= $pagelimit) {
$end_count = $start + $pagelimit;
}
echo "Results $starting_no to $end_count shown.<br>\n";
// create dynamic next, previous, and page links
/* lets say you're set to show 5 results per page and your script comes out with 7 results.
this will allow your script to say next2 if you're on the first page and previous5 if you're on the second page. */
if ($totalrows - $end_count > $pagelimit) {
$var2 = $pagelimit;
} elseif ($totalrows - $end_count <= $pagelimit) {
$var2 = $totalrows - $end_count;
}
$space = " ";
// previous link (make sure to change yourpage.php to the name of your page)
if ($page>1) {
echo "« <a href='result1.php?page=".($page-1)."' class=main>Previous" . $space . $pagelimit . "</a>" . $space . $space . "";
}
// dynamic page number links (make sure to change yourpage.php to the name of your page)
for ($i=1; $i<=$pagenums; $i++) {
if ($i!=$page) {
echo " <a href='result1.php?page=$i' class=main>$i</a>";
}
else {
echo " <b class='red'>$i</b>";
}
}
// next link (make sure to change yourpage.php to the name of your page)
if ($page<$pagenums) {
echo "" . $space . $space . $space . $space . " <a href='result1.php?page=".($page+1)."' class=main>Next " . $var2 . "</a> »";
}
/* output your data wherever you'd like.
BUT
in order for this all to work, before outputting your data, you have to run the query over using MySQL's LIMIT. This will limit how many results are actually displayed on the page. */
$strSQL = mysql_query("SELECT * FROM $table LIMIT $start,$pagelimit");
// LIMIT 0,10 will start at 0 and display 10 results
// LIMIT 10,5 will start at 10 and display 5 results
/* now you can do whatever you'd like with this query. it will only output one page at a time. change the $pagelimit variable to whatever to output more than 1 result per page. */
$sql = "SELECT jobid, date, title, description, location, salary, industry FROM $table LIMIT $start,$pagelimit";
$sql_result = mysql_query($sql) or die('SQL Error');
$num_rows = mysql_num_rows($sql_result);
while ($row = mysql_fetch_array($sql_result)){
$jobid = $row["jobid"];
$date= $row["date"];
$title = $row["title"];
$desc = $row["description"];
$location = $row["location"];
$salary = $row["salary"];
$industry = $row["industry"];
print "<table><tr><td>Job ID: $jobid</td></tr>";
print "<tr><td>Date:";
echo fixDate($date);
Print "</td></tr>";
print "<tr><td>Title: $title</td></tr>";
print "<tr><td>Description: $desc</td></tr>";
print "<tr><td>Location: $location</td></tr>";
print "<tr><td>Salary: $salary</td></tr>";
print "<tr><td>Industry: $industry</td></tr></table>";
print "To apply please click <a href=result_details.php?jobid=$jobid>here</a>";
print "<p>";
}
?>
I am trying to return 1 page per result, at the moment I have got the code to return the results and show each result on an individual page.
My problem occurs when I try to view the next page. If I click the link, it will refresh result1.php and therefore should show the next result. Instead it seems to run the query again and shows the same result as page one.
Any ideas? Here is the code to have a look at:
<?
/* you could put these following variables in a seperate file and include them on the page. That's how I normally do it, I only changed it to look like this for the example */
$server = "localhost";
$username = "user";
$password = "password";
$database = "jobclever";
$table = "job";
// Include Functions
include("functions.php");
// Connect To MySQL Server
@mysql_connect($server, $username, $password) or die("Couldn't Connect to Database");
// Select Database
@mysql_select_db($database) or die("Couldn't Select Database");
// set number of results to display per page
$pagelimit = "1";
// run query (change yourtable to the name of your table)
$strSQL = mysql_query("SELECT * FROM $table");
// count number of matches
$totalrows = mysql_num_rows($strSQL);
// determine how many pages there will be by using ceil() and dividing total rows by pagelimit
$pagenums = ceil ($totalrows/$pagelimit);
// if no value for page, page = 1
if ($page==''){
$page='1';
}
// create a start value
$start = ($page-1) * $pagelimit;
// blank matches found
echo "<b>" . $totalrows . " matches found</b><br>\n";
// Showing Results 1 to 1 (or if you're page limit were 5) 1 to 5, etc.
$starting_no = $start + 1;
if ($totalrows - $start < $pagelimit) {
$end_count = $totalrows;
} elseif ($totalrows - $start >= $pagelimit) {
$end_count = $start + $pagelimit;
}
echo "Results $starting_no to $end_count shown.<br>\n";
// create dynamic next, previous, and page links
/* lets say you're set to show 5 results per page and your script comes out with 7 results.
this will allow your script to say next2 if you're on the first page and previous5 if you're on the second page. */
if ($totalrows - $end_count > $pagelimit) {
$var2 = $pagelimit;
} elseif ($totalrows - $end_count <= $pagelimit) {
$var2 = $totalrows - $end_count;
}
$space = " ";
// previous link (make sure to change yourpage.php to the name of your page)
if ($page>1) {
echo "« <a href='result1.php?page=".($page-1)."' class=main>Previous" . $space . $pagelimit . "</a>" . $space . $space . "";
}
// dynamic page number links (make sure to change yourpage.php to the name of your page)
for ($i=1; $i<=$pagenums; $i++) {
if ($i!=$page) {
echo " <a href='result1.php?page=$i' class=main>$i</a>";
}
else {
echo " <b class='red'>$i</b>";
}
}
// next link (make sure to change yourpage.php to the name of your page)
if ($page<$pagenums) {
echo "" . $space . $space . $space . $space . " <a href='result1.php?page=".($page+1)."' class=main>Next " . $var2 . "</a> »";
}
/* output your data wherever you'd like.
BUT
in order for this all to work, before outputting your data, you have to run the query over using MySQL's LIMIT. This will limit how many results are actually displayed on the page. */
$strSQL = mysql_query("SELECT * FROM $table LIMIT $start,$pagelimit");
// LIMIT 0,10 will start at 0 and display 10 results
// LIMIT 10,5 will start at 10 and display 5 results
/* now you can do whatever you'd like with this query. it will only output one page at a time. change the $pagelimit variable to whatever to output more than 1 result per page. */
$sql = "SELECT jobid, date, title, description, location, salary, industry FROM $table LIMIT $start,$pagelimit";
$sql_result = mysql_query($sql) or die('SQL Error');
$num_rows = mysql_num_rows($sql_result);
while ($row = mysql_fetch_array($sql_result)){
$jobid = $row["jobid"];
$date= $row["date"];
$title = $row["title"];
$desc = $row["description"];
$location = $row["location"];
$salary = $row["salary"];
$industry = $row["industry"];
print "<table><tr><td>Job ID: $jobid</td></tr>";
print "<tr><td>Date:";
echo fixDate($date);
Print "</td></tr>";
print "<tr><td>Title: $title</td></tr>";
print "<tr><td>Description: $desc</td></tr>";
print "<tr><td>Location: $location</td></tr>";
print "<tr><td>Salary: $salary</td></tr>";
print "<tr><td>Industry: $industry</td></tr></table>";
print "To apply please click <a href=result_details.php?jobid=$jobid>here</a>";
print "<p>";
}
?>