Forums

Insane-Rocket

Hi all,
Its been a while since I have had to mess with PHP but I can't get pagination to work on a page of searched results. Can anybody help?

Here is my code, I have tried different things but it don't seem to work, so here is working code of the page showing all results on a page.

Thanks Damian

<?
mysql_connect("host", "user", "password");
mysql_select_db("database");

$search=$_POST["search"];

$result = mysql_query("SELECT * FROM Raids WHERE Date LIKE '%$search%' OR Target LIKE '%$search%' OR Plane LIKE '%$search%' OR Callsign LIKE '%$search%' OR Pilotsurname LIKE '%$search%''");

while($r=mysql_fetch_array($result))
{
$Date=$r["Date"];
$Plane=$r["Plane"];
$Callsign=$r["Callsign"];
$Target=$r["Target"];
$Pilotsurname=$r["Pilotsurname"];
$Pilotrank=$r["Pilotrank"];
$Pilotinitials=$r["Pilotinitials"];
$Comments=$r["Comments"];

echo "<table cellpadding=0 border=1>";
echo "<tr>";
echo "<th>";
echo "Date";
echo "</th>";
echo "<th>";
echo "Aircraft";
echo "</th>";
echo "<th>";
echo "Call Sign";
echo "</th>";
echo "<th>";
echo "Target";
echo "</th>";
echo "</tr>";
echo "<tr>";
echo "<td>$Date</td>";
echo "<td>$Plane</td>";
echo "<td>$Callsign</td>";
echo "<td>$Target</td>";
echo "<tr>";
echo "<th>";
echo "Pilot Surname";
echo "</th>";
echo "<th>";
echo "Pilot Rank";
echo "</th>";
echo "<th>";
echo "Pilot Initials";
echo "</th>";
echo "</tr>";
echo "<tr>";
Print "<td>$Pilotsurname</td>";
Print "<td>$Pilotrank</td>";
Print "<td>$Pilotinitials</td>";
echo "<tr>";
echo "<th>";
echo "<tr>";
echo "<th>";
echo "Notes";
echo "</th>";
echo "<th>";
echo "</tr>";
echo "<tr>";
Print "<td>$Comments</td>";
echo "<br>";
echo "</tr>";
echo "</table>";
}
?>

rcmatt4321

Ah, pagination, Personally I think its more fun to figure this out for yourself, so if you want to read the first part, and attempt it, go ahead, but, if you want to use the source, feel free to. This kinda came to me in the shower one day while I was thinking abut my state tech fair project. It was a blog that needed paginated posts...

You're going to want to decide how many results per page you want to display. For now, I'd go with 5.

The theory behind pagination is that if you are on page one, you want to have the first result displayed the page number times the number of results per page minus the results per page. For example.

If you are on page one, and you are displaying 10 results per page your first result would be the 1st in the database.

1 (page number) * 10 (posts per page) - 10 (posts per page) = 1 (first result on page)
2 (page number) * 10 (posts per page) - 10 = 10 (first result on page)

Thats the theory behind it (If my theory is stupid, someone please correct me :P ).

Some basic data you're going to need for this to work. How many results were returned, how many results per page you would like, and what page you are on.

To find out how many results were returned in your array you should use the PHP mysql_num_rows() function.
To find out what page you are on, you need to use the GET array, $_GET['page'];
To know how many results per page, simply set a variable, I recommend a config file with database and others settings inside of it.

Ok on with your script, remember, you can stop using PHP at any time, and write HTML. Its really really unnecessary (and slow) to use like 30 lines of echos.

config.php



<?php

	$_config['username'] = \"root\";
	$_config['password'] = \"root\";
	$_config['host'] = \"localhost\";
	$_config['database'] = \"pagination\";
	$_config['baseurl'] = \"localhost\";
	
	$_config['results_per_page'] = 5;

?>

index.php



<?php 

	//getting our configuration, this is a good way to store values that may be changed later, just good habit
	require_once('config.php');
	
	//connecting to our database
	mysql_connect($_config['host'], $_config['username'], $_config['password']);
	
	//selecting our database
	mysql_select_db(\"pagination\");
	
	//getting our search query string
	$search = $_GET['search'];
	
	//getting our page
	$page = $_GET['page'];
	
	//getting how many results to display per page
	$results_per_page = $_config['results_per_page'];
	
	//what result is our first? Used in the Limit
	$first_result = $page * $results_per_page - $results_per_page;
	
	$result = mysql_query(\"SELECT * FROM CountryLanguage WHERE Language LIKE \\"$search\\"\");
	
	//knowing the number of results will know how many pages we have
	$numresults = mysql_num_rows($result);
	
	//query our country language table
	$result = mysql_query(\"SELECT * FROM CountryLanguage WHERE Language LIKE \\"$search\\" ORDER by CountryCode DESC LIMIT $first_result, $results_per_page\");
	
?>

<html>

<body>

	<h1>Search</h1>
	
		<p>Countries by continent</p>
	
	<!-- we submit the form to its self, just a basic form here, nothing to see -->
	
	<form action=\"<?php echo $_SERVER[\"SCRIPT_NAME\"] ?>\" method=\"get\" >
	
		<p><input type=\"text\" name=\"search\"/></p>
		
		<p><input type=\"submit\"/></p>
		
		<input type=\"hidden\" name=\"page\" value=\"1\">
				
	</form>

	<h4>Countries that speak <?php echo $search; ?></h4>

	<!-- we set the starting number of the ol to the page times the results per page minus the results per page plus 1 -->
	<!-- this is simple when you think about it, if you're on page 1, and 5 per page, one times 5 is 5 minus 5 = 0 + 1-- >
	<!-- if we're on page 2 then 2 * 5 = 10 - 5 = 5 + 1 = 6 -->
	
	<!--  NOTE: this is for example only, this html attribute is depreciated -->

	<ol start = \"<?php echo ($page * $results_per_page - $results_per_page) +1; ?>\">
		
		<?php while($row = mysql_fetch_assoc($result)):

			//this example is a little more complicated, I'm grabbing coutry codes for countries that speak a language
			//then looking up those country codes in a table that has the name of the country for that code

			$countrycode = $row['CountryCode'];

			$countryresult = mysql_fetch_assoc(mysql_query(\"SELECT * FROM Country WHERE Code = \\"$countrycode\\"\"));
			
		?>

			<!-- echoing the country name into a li element -->

			<li><?php echo $countryresult[\"Name\"]; ?></li>

		<?php endwhile; ?>
		
	</ol>	

	<!-- here's the fun part :D -->
	
	<?php if($page != 1): ?>
		
		<!-- if the page is not the first, we can always go back a page, simple really-->
		<!-- you're going to have to change some values depending on what the name of your php file is -->
		
		<!-- \"$page-1\" is simpliy the page we're on, minus one -->
		
		<a href=\"index.php?search=<?php echo $search; ?>&page=<?php echo $page-1; ?>\">Previous Page</a>
		
	<?php endif;?>
	
	<br/>
	
	<?php if($page * $results_per_page < $numresults): ?>
		
		<!-- a little more complicated here, if the page we're on times the results per page is less than the number of results-->
		<!-- then we're going to display a next link, for example, if we're on page 1 -->
		<!-- 1 * 5 = 5 < 60 -->
		<!-- 12 * 5 = 60 < 60 we can't do <= because then we would have another next link on page 12  -->
		
		<!-- and again, \"$page+1\" if just the current page, plus one -->
				
		<a href=\"index.php?search=<?php echo $search; ?>&page=<?php echo $page+1; ?>\">Next Page</a>
		
	<?php endif;?>

</body>

</html>

The code looks really really really ugly so, you can download/view it here http://www.mattegan.net/code.txt

I hope that helps :D Btw, my PHP may not be the best, as in, it works, but I'm not sure if this is how most people would go about it. I've only been programming PHP for about a year, and there are plenty of people way better than me.

davesgonebananas

You should always escape user inputted strings that are being used in database queries. If you don't follow this advice malicious users could get unrestricted access to your data.

   //getting our search query string
   $search = mysql_real_escape_string($_GET['search']);

rcmatt4321

Woops, forgot to mention that. Thanks Dave :D

Also, the database I was using was the world.sql file you can get off of the mySQL site.

rcmatt4321

Please tell me I didn't go through the trouble of writing and commenting through all that code for nothing.... :?

treehouse : what would you like to learn today?

Forums

Pagination for searched results help