lost variables/paginating glitch on join search

turpentyne

I've looked at several tutorials. I've rebuilt this numerous times. I've copied scripts. I cannot figure this out.

What I'm trying to do: A user fills a form on one page of the 3 or 4 variables they want to search the database for. On submit, the variables are passed to a php page to do a database query on the "descriptors" table and cross-references the same ID on another table so that they will see some the relevant info from that table as well.

What's happening: The scripting that has gotten me closest has a glitch. A search is done for an item that has 6 records in the database. The code is set to show 2 records per page, and pagination links at the bottom. The page shows correct information and the correct number of pages at the bottom... at first. When one of the page links is clicked for more records, it seems as if the query variables are lost. The page shows every single record in the database at two per page, with all 20 page numbers at the bottom. Not what I want.

I am new to PHP and completely at a loss. I'm sure the problem is the lost POST variable. But can't find a way to solve this. I tried turning the POST info into variables, but then the problem is that I get one record only and no pagination numbers.

Here's a stripped down version of the form from the first page:

<FORM action="results.php" style="border: 1px dotted red; padding: 2px" method="post" name="formsearch">
<input type="text" name="select1" value="" id="select1" /><br>
             <input type="text" name="select2" value="" id="select2" /><br>
             <input type="text" name="select3" value="" id="select3" /><br>
             <input type="text" name="select4" value="" id="select4" /><br>
             <input type="hidden" name="submitted" value="TRUE">
             <input type="submit" value="Search" name="selected"/> 
             </form>

here's the code on the second page. I would put just the problem area code if I knew what the problem was:


<?php

require ('database.php');



// How many adjacent pages should be shown on each side?
$adjacents = 3;

/* 
   First get total number of rows in data table. 
   If you have a WHERE clause in your query, make sure you mirror it here.
*/
$query = "SELECT COUNT(*) as num
                FROM
                    descriptors
                JOIN
                    plantae ON (descriptors.plant_id = plantae.plant_name)
                WHERE
                    descriptors.leaf_shape LIKE '%$select1%'
                    AND descriptors.leaf_venation LIKE '%$select3%'
                    AND descriptors.leaf_margin LIKE '%$select4%'";


$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[num];

/* Setup vars for query. */
$targetpage = "leafsearch4.php"; 	//your file name  (the name of this file)
$limit = 2; 								//how many items to show per page
$page = $_GET['page'];
if($page) 
	$start = ($page - 1) * $limit; 			//first item to display on this page
else
	$start = 0;								//if no page var is given, set start to 0

/* Get data. */

    $sql = "SELECT
                    descriptors.*
                    ,plantae.*
                FROM
                    descriptors
                INNER JOIN
                    plantae ON (descriptors.plant_id = plantae.plant_name)
                WHERE
                    descriptors.leaf_shape LIKE '%$select1%'
                    AND descriptors.leaf_venation LIKE '%$select3%'
                    AND descriptors.leaf_margin LIKE '%$select4%'
                ORDER BY plantae.scientific_name ASC LIMIT $start, $limit";


$result = mysql_query($sql);

/* Setup page vars for display. */
if ($page == 0) $page = 1;					//if no page var is given, default to 1.
$prev = $page - 1;							//previous page is page - 1
$next = $page + 1;							//next page is page + 1
$lastpage = ceil($total_pages/$limit);		//lastpage is = total pages / items per page, rounded up.
$lpm1 = $lastpage - 1;						//last page minus 1

/* 
	Now we apply our rules and draw the pagination object. 
	We're actually saving the code to a variable in case we want to draw it more than once.
*/
$pagination = "";
if($lastpage > 1)
{	
	$pagination .= "<div class=\"pagination\">";
	//previous button
	if ($page > 1) 
		$pagination.= "<a href=\"$targetpage?page=$prev\">« previous</a>";
	else
		$pagination.= "<span class=\"disabled\">« previous</span>";	

	//pages	
	if ($lastpage < 7 + ($adjacents * 2))	//not enough pages to bother breaking it up
	{	
		for ($counter = 1; $counter <= $lastpage; $counter++)
		{
			if ($counter == $page)
				$pagination.= "<span class=\"current\">$counter</span>";
			else
				$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
		}
	}
	elseif($lastpage > 5 + ($adjacents * 2))	//enough pages to hide some
	{
		//close to beginning; only hide later pages row 85 or 86
		if($page < 1 + ($adjacents * 2))		
		{
			for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
			{
				if ($counter == $page)
					$pagination.= "<span class=\"current\">$counter</span>";
				else
					$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
			}
			$pagination.= "...";
			$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
			$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
		}
		//in middle; hide some front and some back
		elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
		{
			$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
			$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
			$pagination.= "...";
			for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
			{
				if ($counter == $page)
					$pagination.= "<span class=\"current\">$counter</span>";
				else
					$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
			}
			$pagination.= "...";
			$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
			$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";		
		}
		//close to end; only hide early pages
		else
		{
			$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
			$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
			$pagination.= "...";
			for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
			{
				if ($counter == $page)
					$pagination.= "<span class=\"current\">$counter</span>";
				else
					$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";					
			}
		}
	}

	//next button row 132
	if ($page < $counter - 1) 
		$pagination.= "<a href=\"$targetpage?page=$next\">next »</a>";
	else
		$pagination.= "<span class=\"disabled\">next »</span>";
	$pagination.= "</div>\n";		
}
?>

<?php

echo '<table align="center" border=1 cellspacing="0" cellpading-"5">
<tr>
<td align="left"><b></b></td>
<td align="left"><b></b></td>
<td align="left"><b>Scientific name</b></td>
<td align="left"><b>Common name</b></td>
<td align="left"><b>Leaf shape</b></td>
</tr>';
		while($row = mysql_fetch_array($result))
		{
echo '<tr>
<td align="left"> <a href="link.php">View plant</a> &nbsp; &nbsp;</td>
<td align="left"> nothing here &nbsp; &nbsp;</td>
<td align="left">' . $row['scientific_name'] . '</td>
<td align="left">test' . $row['common_name'] . '</td>
<td align="left">' . $row['leaf_shape'] . '</td>
</tr>';
}
echo '</table>';


?>
<?=$pagination?>

Krik

I didn't look at your code but you are likely assuming the php holds the query for all consecutive pages.

PHP is designed to run and output the results. Each page load is a unique and separate call. And the second, thrid, ect. pages will have no clue what php did on the first page or what it output to the browser. In short PHP has a memory about as long as it takes to load a given page and then it forgets everything this is why we need MySQL. If PHP could remember previous scripts it ran there would be no need for a database.

You have to run a new query each time the the page loads, and just show the results set that corresponds with the page they are on. To run the new query you have to pass the original search terms to each page so they can be passed to the query when the next page is clicked.

I'm sure the problem is the lost POST variable.

Same goes for $POST variables they only exist for the few seconds that it takes to process a new page load then they are delete. They are not ever available to the next page that loads. To makes them available to the next page, via post, you have to submit them again.

Now there is a way around this, but it is bit more complicated. You can use temporary tables. This is were you run the query, and then output the results to a temporary table and then just display the results from that table. And then at some point the temporary data is deleted.

turpentyne

I did test the query with a definite variable and found it worked. So, the lost _POST info is definitely the problem.

Right now I'm trying to figure out sessions to see if that solves the problem. I haven't had any luck with this yet. Would Sessions solve the problem?

If so, how do I rework the script? What I've tried so far isn't working (albeit I've only spend a half hour trying to do this)

come to think of it. My next step is to set it up so users can narrow their results. Maybe I should be doing all of this through a temporary table? I read somewhere that the table deletes the moment i close my query, though... or something like that.

Krik

Would Sessions solve the problem?

Yes they would. But I would say lets not reinvent the wheel. Go over to google and look at how they handle a search phrase and pagination. They just put them right into the URL, or GET. This is real handy as then you can construct a link with the values from the $_GET super global.

If you do use SESSION you may want to read up on [man]session_start[/man] and maybe a tutorial on session handling.

I read somewhere that the table deletes the moment i close my query, though... or something like that

Yes and no. It is true that the second PHP is sends the data to your browser a temporary table will be closed. However there are ways to keep it open but I am not familiar enough with this particular aspect to intelligibly expound much further.

However there is another technique that is not a true temporary table but has the same effect. If you create 2 permanent tables, one for the search phrase or words and one for search results. In the search phrase table place a time stamp and the users session id and create unique id for each record. Then make sure to use that unique id for all the related search results records. Then if the user does a new search delete the old search result records.

Now your thinking what if they leave the page or the close the browser window, the results will not get deleted. Well if they leave the search page but stay on your sight you can still delete the records you just have to have the delete query run in all pages. But now up think those are easy but what do you do if they close the window or leave your site. Here you would use AJAX (fancy term for javascript) with the onunload event to send an unseen request to the serve that would run a delete script.

Now I should note that any temporary table technique is really best for a database with hundreds of thousands of records. The search result that are held in the temporary table can be limited to something like 1000 of the most relevant records and, outside of the first search, future page requests will be much faster not having to search all the records. This can save tons of bandwidth also.

As you can see temporary table are more complicated and, like I said, unless you have a huge database I wouldn't use them. I would say go with passing the search phrase form page to page via the URL.