WHERE clause problem

STXman

Hi guys

I have a query to paginate the results from a select on a table with 200 rows. Query(A) works as expected producing all the rows on multiple pages. Query(😎 does not. It produces 10 rows on a single page. The subsequent pages are blank. I am expecting a minimum of 30 rows with the particular $country I test with.

It appears that the WHERE clause is the problem. Am I wrong? How can I properly use a WHERE clause in this query and get the script to print the rows of data in subsequent pages?

Query(A)

$sql_text = "SELECT *
FROM userdetails
ORDER BY ID DESC LIMIT ".$page.",10";

Query(😎

$sql_text = "SELECT *
FROM userdetails
WHERE country = '$country'
ORDER BY ID DESC LIMIT ".$page.",10";

czambran

Try this,

$sql_text = "SELECT * 
FROM userdetails 
WHERE country = '$country' 
ORDER BY ID DESC LIMIT $page,10";

STXman

Thanx but that didn’t work either. After some debugging it seems the problem stems from the fact that the value of $country is not retained when the user clicks the next button. Can someone be kind enough to show me how to retain the value in $country when the user clicks the next/previous buttons? My code is below:

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

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

echo "This is country: $country";

$sql_text = "SELECT *
FROM userdetails
WHERE country = '$country'
ORDER BY id DESC LIMIT $page,10";

//query one (Get 10 rows)
$myQuery = mysql_query($sql_text);
$myQueryTwo = mysql_query($sql_text);

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:black;width:500px;text-align:left;\">\n\n\n\n");
}
$page = (!isset($GET['page']))? '0' : $GET['page'];

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

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

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

Kudose

It's becuase in your URL you're not including the country, but in the script you're looking for $_GET['country'];

czambran

Don't quite get your pagination code, but the reason why country is not being retained is that you need to pass it as you do with page.
try this:

<? 

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


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

echo "This is country: $country"; 

$sql_text = "SELECT * 
FROM userdetails 
WHERE country = '$country' 
ORDER BY id DESC LIMIT $page,10"; 


//query one (Get 10 rows) 
$myQuery = mysql_query($sql_text); 
$myQueryTwo = mysql_query($sql_text); 

while ($row = mysql_fetch_assoc($myQuery)) { 
echo("<p>\n" 
. "<b>job Firstname:</b> " . $row["FIRSTNAME"] . "<br />\n" 
. "<b>job Lastname:</b> " . $row["LASTNAME"] . "<br />\n" 
. "<b>COUNTRY:</b> " . $row["COUNTRY"] . "<br />\n" 
. "<b>contact:</b>\n"); 
echo("<hr style=\"color:black;width:500px;text-align:left;\">\n\n\n\n"); 
} 
$page = (!isset($_GET['page']))? '0' : $_GET['page']; 


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

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

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

?>

STXman

Thanx a bunch. It worked!

STXman

Hi guys

Not sure if this worked as well as I initially thought.The problem is that when the next button is clicked only 1 row instead out of the 10 rows change value.

//connect to server and select database
$conn = mysql_connect("localhost", "root", "")

or die(mysql_error());

mysql_select_db("test",$conn) or die(mysql_error());

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

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

echo "This is country: $country";

$sql_text = "SELECT *
FROM userdetails

WHERE country = '$country'
ORDER BY id DESC LIMIT $page,10";

//query one (Get 10 rows)
$myQuery = mysql_query($sql_text);

$myQueryTwo = mysql_query($sql_text);

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:black;width:500px;text-align:left;\">\n\n\n\n");

}

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

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

if(($page-1) < $numRows && $page != 0) {

//display previous
echo '<a href="MLresultsPageFinal6.php?page='.($page-1).'&country='.$country.'">Previous</a> ';

}

//get Next link
if(($page+1) < $numRows){

echo '|<a href="MLresultsPageFinal6.php?page='.($page+1).'&country='.$country.'">Next</a>';

}