Navigating query results

STXman

My query generates 50 rows and I want to display 4 at a time. The user can then click on a next button moving the page to the next 4 results. At the bottom of the page I would like to generate something like the list of pages below. The user will also be able to navigate to any page on the list if they wish.

Page: 1, 2, 3, 4, 5….. Next >

How can I achieve this using the code below? Thanx in advance.

<?php

$mysql_access = mysql_connect("localhost","root","");

mysql_select_db("test", $mysql_access);

$gender = $REQUEST['gender'];

$country = $REQUEST['country'];

$query_Recordset1 = "SELECT * FROM USERDETAILS where gender = '$gender' AND country = '$country'";

$Recordset1 = mysql_query($query_Recordset1, $mysql_access) or die(mysql_error());

echo "<table>";
while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)) {

$picture_path = $row_Recordset1['PICTURE_PATH'];

echo "<tr><td colspan=2><img src=$picture_path></td></tr>";

echo "<tr><td>Firstname: {$row_Recordset1['FIRSTNAME']}</td>";

echo "<td>Lastname: {$row_Recordset1['LASTNAME']}</td></tr>";

echo "<tr><td>Country:{$row_Recordset1['COUNTRY']}</td>";

echo "<td>gender:{$row_Recordset1['GENDER']}</td></tr>";

echo "<tr><td colspan=2>{$row_Recordset1['ID']}</td></tr>";

}

echo "</table>";
?>

<?php
mysql_free_result($Recordset1);
?>

devinemke

this has been covered 100's of times here, do a search using keyword "paginantion".

STXman

Thanx for pointing that out. After performing the search I found a number of useful scripts. I tried the following, however, I receive the error:

Notice: Undefined index: country in c:\Apache\htdocs\mlresultspagefinal.php on line 13
You have specified an invalid page number

Any Idea why? Many Thanks.

//connect to server and select database
$conn = mysql_connect("localhost", "root", "")
or die(mysql_error());
mysql_select_db("test",$conn) or die(mysql_error());

//choose the amount of stats you want to display in one page
$per_page = 3;

/original code // retrieve data from database
$sql_text = "SELECT FROM jobs";/
$COUNTRY = $_REQUEST['country'];
//create and issue the query
$sql_text = "SELECT
FROM USERDETAILS
WHERE COUNTRY = '$COUNTRY'
ORDER BY GENDER";

if(!isset($GET['page'])){
$page = 1;
}else{
$page = $GET['page'];
}

//set up the variables for the prev and next links
$prev_page = $page - 1;
$next_page = $page + 1;

//get the data from the database
$query = mysql_query($sql_text);

/set up specified page, so we have set $per_page to 3 we need to figure out the starting page based on the amount of data recieved from the database. mysql_num_rows() will get the no. of rows in the result from our mysql_query/
$page_start = ($per_page * $page) - $per_page;
$num_rows = mysql_num_rows($query);

if ($num_rows <= $per_page){
$num_pages = 1;
}elseif (($num_rows % $per_page) == 0) {
$num_pages = ($num_rows / $per_page);
}else{
$num_pages = ($num_rows / $per_page) + 1;
}
$num_pages = (int) $num_pages;

if ($page > $num_pages || $page < 0) {

echo 'You have specified an invalid page number'

;}

$sql_text = $sql_text." LIMIT $page_start, $per_page";
$query = mysql_query($sql_text);

<html>
<head>
<title>All Users</title>
</head>

<body>

There are currently <? echo "$num_rows"; ?> files in this section

<?
// previous link. we need a if statement to determine if prev page is not equal to 0, then echo the url for the link
if ($prev_page != 0){
echo '<a href="MLresultsPageFinal.php?page='.$prev_page.'">prev</a> |';
}

//displays the next link
if ($page != $num_pages) {
echo '|<a href="MLresultsPageFinal.php?page='.$next_page.'">Next</a>';
}
?>

<?php // This calls the data to be displayed.
while ($info = mysql_fetch_assoc($query)) {
echo("\n"
. "job Firstname: " . $info["FIRSTNAME"] . " \n"
. "job Lastname: " . $info["LASTNAME"] . " \n"
. "COUNTRY: " . $info["COUNTRY"] . " \n"
. "contact:\n");
echo("<hr style=\"color:black;width:500px;text-align:left;\">\n\n\n\n");
}
?>

</body>
</html>

STXman

After re-working the script I have come up with this one, which initially seems to work fine. However, after several clicks on the next button it displays data that do not fulfil the selection criteria. How can I limit it to data that only fulfil the selection criteria? Thanx.

//connect to server and select database
$conn = mysql_connect("localhost", "root", "")
or die(mysql_error());
mysql_select_db("test",$conn) or die(mysql_error());

$gender = $REQUEST['gender'];

$country = $REQUEST['country'];

$page = (!isset($GET['page']))? '0' : $GET['page'];

//query one (Get 10 rows)
$myQuery = mysql_query("SELECT FROM userdetails WHERE country = '$country' AND gender ='$gender' ORDER BY id DESC LIMIT ".$page.",10");
//$myQuery = mysql_query("SELECT FROM userdetails WHERE $country = 'country'");

//query two (get total rows)
$myQueryTwo = mysql_query("SELECT * FROM userdetails WHERE country = '$country' AND gender ='$gender' ORDER BY id DESC LIMIT ".$page.",10");

//get result
//$row=mysql_fetch_array($myQuery);

// Display Row Information here

while ($row = mysql_fetch_assoc($myQuery)) {
echo("\n"
. "job Firstname: " . $row["FIRSTNAME"] . " \n"
. "job Lastname: " . $row["LASTNAME"] . " \n"
. "COUNTRY: " . $row["COUNTRY"] . " \n"
. "contact:\n");
echo("<hr style=\"color:grey;width:500px;text-align:left;\">\n\n\n\n");
}

//Pagination below
$numRows = mysql_num_rows($myQueryTwo);//total rows

if(($page-1) < $numRows && $page != 0) {
//display previous
echo '<a href="MLresultsPageFinal2.php?page='.($page-1).'">Previous</a> ';
}

//get Next link
if(($page+1) < $numRows){
echo '|<a href="MLresultsPageFinal2.php?page='.($page+1).'">Next</a>';
}
?>