search with pagination

Labrat · October 30, 2011

Hi experts, need some assistance please. I have a search code that works great and posts the information just the way it should but the problem is when I try to go to the next page it loads an empty page even though there is more to show. The next page link seems to be correct it just is not working. Here is my code, will someone please look at it and give some advice? Thank you in advance.

elseif ((select($page) == 'city')){

echo '<div class="city_search">

<h1>Search by State</h1>

<form method="post" action="index.php?content=locations&section=city_search">
State: <select name="id">';
echo '<option value="" selected="selected" disabled="disabled">Select a State</option>';

$result = mysql_query("SELECT * FROM state") 
or die(mysql_error());

		  while($row = mysql_fetch_array( $result )) 
  
{
	   echo '<option value="'.$row['id'].'">'.$row['state'].'</option>';
	}
echo '</select><input type="submit" name="search" value="Search"></form>';
echo '</div>';
}



elseif ((select($page) == 'city_search')){

//	$id = $_POST['id'];

//    $state_id = $_POST['state_id'];

if(isset($_POST['search'])){

    $id = $_POST['id'];

    $state_id = $_POST['state_id'];

echo $state_id;

echo '<center>';

$per_page = 20;

$pages_query = mysql_query("SELECT COUNT(id) AS tot FROM city WHERE state_id='$id'");
$pages = ceil(mysql_result($pages_query, 0) / $per_page);

$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$start = ($page -1) * $per_page;

$query = mysql_query("select * from city WHERE state_id='$id' LIMIT $start, $per_page");

echo '<div class="pag">
<a href="index.php?content=locations&section=city_add">Add City</a><br><br>';
$totalres = mysql_result(mysql_query("SELECT COUNT(id) AS tot FROM city where state_id='$id'"),0);
echo "Number of Cities Found:<font color=\"red\"><b> ".$totalres. "</b></font><br><br>";

if ($pages >= 1 && $page <=$pages) {
for ($x=1; $x<= $pages; $x++) {
echo ($x == $page) ? '<strong><a href="index.php?content=locations&section=city_search&state_id='.$id.'&page='.$x.'">'.$x.'</a></strong> | ' : '<a href="index.php?content=locations&section=city_search&state_id='.$id.'&page='.$x.'">'.$x.'</a> | ';
;
}
}
echo '</div>';

echo '<div class="city_cont">
<div class="city_cols">
<div class="list_id">ID</div>
<div class="list_name">Country</div>
<div class="list_name">State</div>
<div class="list_name">City</div>
<div class="list_edit">Edit</div>
<div class="list_del">Delete</div>
</div>';

while ($row = mysql_fetch_array($query)) {

$id = $row['id'];
$country_id = $row['country_id'];
$state_id = $row['state_id'];
$city = $row['city'];

echo '<div class="city_cols">
<div class="list_id">' .$row['id'] .'</div>';

echo '<div class="name">';
$sql1 = mysql_query("SELECT * from country WHERE id='$country_id'");
$row = mysql_fetch_array( $sql1 );
echo $row['country'];

echo '</div>';

echo '<div class="name">';
$sql2 = mysql_query("SELECT * from state WHERE id='$state_id'");
$row = mysql_fetch_array( $sql2 );
echo $row['state'];

echo '</div>';

echo '
<div class="name">' .$city . '</div>
<div class="edit"><a href="index.php?content=locations&section=city_edit&id='.$id.'"><img src="images/edit.png" border="0"></a></div>
<div class="delete"><a href="index.php?content=locations&section=city_delete&id='.$id.'"><img src="images/delete.png" height="16" width="16" border="0"></a></div></div>';
}

print_r($_POST);
if ($pages >= 1 && $page <=$pages) {
for ($x=1; $x<= $pages; $x++) {
echo ($x == $page) ? '<strong><a href="index.php?content=locations&section=city_search&state_id='.$state_id.'&page='.$x.'">'.$x.'</a></strong> | ' : '<a href="index.php?content=locations&section=city_search&state_id='.$state_id.'&page='.$x.'">'.$x.'</a> | ';
;
}
}

echo '</div></center>';
}
}

Psycho · October 30, 2011

Your problem is that when the user selects the search criteria it is sent via POST data, but then when the user selects another page you are losing that data. It looks like you have two values passed in the POST data 'id' and 'state_id'. And, it appears you area appending the 'id' to the pagination links, but not the 'state_id' value.

but, even so, there is nothing in your code to use the 'id' passed in the URL. So, you need to make sure you maintain the search criteria when you use the pagination links. You can either put both variables on the query string or you can store them in SESSION/COOKIE data. There are benefits and drawbacks to the different methods. But, whichever method you use you will need to make sure you actually use the values. So, first check if the values were passed via POST - if so, use them. If not, then check if they are available in the alternative method GET/SESSION/COOKIE - and use them if they are set there.

Labrat · October 30, 2011

Thank you for your response. I`m not quite following you, are you saying the pagination links should read like this?

index.php?content=locations&section=city_search&state_id='.$state_id.'&id='.$id.'&page=2

where the state_id and the id are the same? the state_id comes from the "city" table where the id is from the "state" table. Both the state_id and the id are the same number, thats what both tables have in common. Both the state_id and the id are passing in the pagination link.

Psycho · October 30, 2011

No, that is not what I am saying. I did not read your code line for line trying to decipher every little detail. But, you say the 'id' and 'state_id' are the same value, so why do you have these two lines?

    $id = $_POST['id'];

    $state_id = $_POST['state_id'];

Again, I didn't look at all of your code in detail, but it looks as if you never use that variable $state_id from THAT defined variable. You do overwrite the variable $state_id, but you never use it with the defined value from $_POST['state_id'] so it seems that is completely useless.

So, if 'id' is the only value you need, then you have a workable solution to add the value to the pagination links. But, you still have the problem that you never use the value! You only attempt to use $_POST['id'] (from the form submission) and you never use $_GET['id'].

So you simply need to use some logic along the line of

1.) If user passed id in POST data use that

2. Else If user passed id in GET data use that

3. Else don't use id

Example code

if(isset($_POST['id'])
{
    $id = intval($_POST['id']);
}
elseif(isset($_GET['id'])
{
    $id = intval($_GET['id']);
}
else
{
    $id = false;
}

Labrat · October 30, 2011

The state_id is from the "city" table and that value is the same as the id in the "state" table. In other words if Georgia has the id number of 8 in the state table then the state_id is 8 from the city table. In the results query i have it where state_id(from city table)=$id(form submitted)

State Table

ID State

8 Georgia

City Table

ID State_id City

1 8 whatever

The results are posted from the City table, thats why the

 $state_id = $_POST['state_id'];

Psycho · October 30, 2011

You are making no sense. Why do you set the value of $state_id using this

 $state_id = $_POST['state_id'];

. . . when you NEVER use it?!

Later in you code you overwrite the value of $state_id withint he while() loop, so the original value never gets used.

But, that is beside the point of what you are trying to accomplish. As I stated previously, you are uing the value of $_POST['id'] to filter your query results, then you add that value to the query string for the pagination links using

id='.$id.'

But, just as above you never use those value. You need to be using $_GET['id'] to get the value in the query string. And, no where in your code do I see anything referencing that

Labrat · October 30, 2011

I just don`t get it, the pagination is passing the correct id number for that state and yet the next page is blank, do not see where i need the $_GET['id'] when its passing the right id to begin with. I took out the state_id reference, I was mainly just trying that to see if it would work but it doesn`t make any difference with or without it.

Psycho · October 30, 2011

Yes, you do appear to be passing the right value. But, you need to reference the value using $_GET['id'] - you do not have that anywhere in your code. I already gave you a suggestion on how you can use the POST value first if it is set then optionally the GET value if it is set.

The reason you are getting a blank page is because you are still tryign to use $_POST['id']. Sine that value is not set on the pages loaded via the pagination links your query has a where clause of

state_id=''

So, you are getting no results because you have no records where the ID is an empty string.

Labrat · October 30, 2011

Here is my new code with your suggestion, still comes up as a blank page. i tried it both with and without the $id = $_POST['id']; as seen on the 3rd line

elseif ((select($page) == 'city_search')){

if(isset($_POST['search'])){

    $id = $_POST['id'];

if(isset($_POST['id']))
{
    $id = intval($_POST['id']);
}
elseif(isset($_GET['id']))
{
    $id = intval($_GET['id']);
}
else
{
    $id = false;
}

echo '<center>';

$per_page = 20;

$pages_query = mysql_query("SELECT COUNT(id) AS tot FROM city WHERE state_id='$id'");
$pages = ceil(mysql_result($pages_query, 0) / $per_page);

$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$start = ($page -1) * $per_page;

$query = mysql_query("select * from city WHERE state_id='$id' LIMIT $start, $per_page");

echo '<div class="pag">
<a href="index.php?content=locations&section=city_add">Add City</a><br><br>';
$totalres = mysql_result(mysql_query("SELECT COUNT(id) AS tot FROM city where state_id='$id'"),0);
echo "Number of Cities Found:<font color=\"red\"><b> ".$totalres. "</b></font><br><br>";

if ($pages >= 1 && $page <=$pages) {
for ($x=1; $x<= $pages; $x++) {
echo ($x == $page) ? '<strong><a href="index.php?content=locations&section=city_search&id='.$id.'&page='.$x.'">'.$x.'</a></strong> | ' : '<a href="index.php?content=locations&section=city_search&id='.$id.'&page='.$x.'">'.$x.'</a> | ';
;
}
}
echo '</div>';

echo '<div class="city_cont">
<div class="city_cols">
<div class="list_id">ID</div>
<div class="list_name">Country</div>
<div class="list_name">State</div>
<div class="list_name">City</div>
<div class="list_edit">Edit</div>
<div class="list_del">Delete</div>
</div>';

while ($row = mysql_fetch_array($query)) {

$id = $row['id'];
$country_id = $row['country_id'];
$state_id = $row['state_id'];
$city = $row['city'];

echo '<div class="city_cols">
<div class="list_id">' .$row['id'] .'</div>';

echo '<div class="name">';
$sql1 = mysql_query("SELECT * from country WHERE id='$country_id'");
$row = mysql_fetch_array( $sql1 );
echo $row['country'];

echo '</div>';

echo '<div class="name">';
$sql2 = mysql_query("SELECT * from state WHERE id='$state_id'");
$row = mysql_fetch_array( $sql2 );
echo $row['state'];

echo '</div>';

echo '
<div class="name">' .$city . '</div>
<div class="edit"><a href="index.php?content=locations&section=city_edit&id='.$id.'"><img src="images/edit.png" border="0"></a></div>
<div class="delete"><a href="index.php?content=locations&section=city_delete&id='.$id.'"><img src="images/delete.png" height="16" width="16" border="0"></a></div></div>';
}
if(isset($_POST['id']))
{
    $id = intval($_POST['id']);
}
elseif(isset($_GET['id']))
{
    $id = intval($_GET['id']);
}
else
{
    $id = false;
}
if ($pages >= 1 && $page <=$pages) {
for ($x=1; $x<= $pages; $x++) {
echo ($x == $page) ? '<strong><a href="index.php?content=locations&section=city_search&id='.$id.'&page='.$x.'">'.$x.'</a></strong> | ' : '<a href="index.php?content=locations&section=city_search&id='.$id.'&page='.$x.'">'.$x.'</a> | ';
;
}
}

echo '</div></center>';
}
}

Psycho · October 30, 2011

Well, then I would assume that the elseif() condition is not resulting in true

elseif ((select($page) == 'city')){

How are you defining $page.

Although, based on the code I can see I think you really need to start over on the page logic.

Labrat · October 30, 2011

wait a min, think i see the problem now, the $page is being called through a switch statement, but i`m seeing now that the pagination also uses the $page variable. let me see what i can do about that and i`ll let you know. thank you for the help so far.

Labrat · October 30, 2011

well that didnt work, i thought the page var might be the problem so changed it and nope it still does the same thing.

Sign In

search with pagination

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information