Jump to content
  • Advertisement
Sign in to follow this  
Sonnenblume

[web] Difficulty with PHP/MySql Sorting Script

This topic is 4236 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hi there I have written a sorting engine which displays non expired records, but im having difficulties getting it to work. It was working fine until I added pagination, and now it seems to have gone to pot. The script works by getting todays date, and based on the users selection from the drop down menu, calculates the number of pages, and records the users sort preference. The correct number of links to different pages are then generated, and the pages are populated based on the users sort preference, or so I thought... The first page of results is sorted correctly, but on the second and subsequent pages, this isnt always the case. This causes results to be appearing in the wrong order, or not at all in some cases. Ive included my source code, if anyone see's anything which is glaringly obvious, or feels like giving me a hand fixing this id very much appreciate it :)
<?php
	
	//gets todays date
	$date = gmdate(Ymd);
	
	//gets the connection settings
	require('connection.php');
	
	//gets the variables the user selected from the form.
	$select= $_POST['select'];
	$searchTerm=$_POST['searchTerm'];
	$searchType=$_POST['searchType'];


	//Switch Statement which correspsonds to the sort drop down menu.  Each case represents a field in the menu.  The query contained gets all valid records (i.e. non expired)
	//and $orderBy represents the order that the results with be sorted into.
		switch($select)
		{
			case "1":

				$query = "select title, advertId, projectNum, value, type, cpv, expireDate, details from adverts  where expireDate >= ".$date."";
				$orderBy = "area";
				break;
			
			case "2":

				$query = "select title, advertId, projectNum, value, type, cpv, expireDate, details from adverts  where expireDate >= ".$date."";
				$orderBy = "expireDate";
				break;
			
			case "3":

				$query = "select title, advertId, projectNum, value, type, cpv, expireDate, details from adverts  where expireDate >= ".$date."";
				$orderBy = "title";
				break;
				
			case "4":

				$query = "select title, advertId, projectNum, value, type, cpv, expireDate, details from adverts  where expireDate >= ".$date."";
				$orderBy = "type";
				break;

			case "5":

				$query = "select title, advertId, projectNum, value, type, cpv, expireDate, details from adverts  where expireDate >= ".$date."";
				$orderBy = "Value";
				break;
				

		}


		//If the user hasnt selected a sorting preference, just display all the records which havent expired
		if($select=='')
			{
				$query = "select title, advertId, projectNum, value, type, cpv, expireDate, details from adverts  where expireDate >= ".$date."";
			}
			
			$result = $db->query($query);
  			$total_entries = $result->num_rows;
				

		//Number of entries that each page will contain
		$entries_per_page = 4; 
				
		//Gets the current page number		
		if(isset($_GET['page_number'])) 
			{ 
				$page_number = $_GET['page_number']; 
			} 
		else
			{ 
				$page_number = 1; 
			}
			
		//Calculates the number of pages needed to display all the records
		$total_pages = ceil($total_entries / $entries_per_page); 
				
				
		//calculates the offset i.e. calculates whats the first record to be displayed on a certain page
		$offset = ($page_number - 1) * $entries_per_page; 
				
				
				
		//If no search terms or sort order has been entered, then display all the current valid records across the pages
		if($select=='' && $searchTerm=='')
			{
				$queryA = "select * from adverts where expireDate >= ".$date." limit $offset,$entries_per_page";
				$resultA = $db->query($queryA);
				$total_entries = $result->num_rows;
			}
								
		//If the user enteres a search term, display it across the pages
		if($searchTerm!='')
			{
				$queryA = "select title, advertId, projectNum, value, type, cpv, expireDate, details from adverts where match(".$searchType.") against ('".$searchTerm."%' in boolean mode) ";
				$resultA = $db->query($queryA);
				$total_entries = $result->num_rows;
			}

		//If the user enters a sort perference, display the records in that order across the pages
		if($select!='')
			{
				$queryA = "select * from adverts where expireDate >= ".$date." order by ".$orderBy." asc limit $offset,$entries_per_page";
				$resultA = $db->query($queryA);
				$total_entries = $result->num_rows;
			}

		//Display links to all the page
		echo'<p>Page: ';
		for($i = 1; $i <= $total_pages; $i++) 
			{ 
				if($i == $page_number) 
					{ 
						// This is the current page. Don't make it a link. 
						print "$i "; 
					} 
				else 
					{ 
						// This is not the current page. Make it a link. 
						echo'<a href="viewer.php?page_number='.$i.'">&nbsp;'.$i.'&nbsp;</a>'; 
					} 
				} 
		echo'</p>';
					
		//Calculates if there will be any pages that dont have the maximum number of recrods allowed			
		$remaining = $total_entries % $entries_per_page;
	
		//if thats the case, display the records across all the pages
	 	if($remaining==0)
	 		{
		 		if($page_number <= $total_pages)
		 			{			
						//this script displays the adverts based on the search results
						for ($i=1; $i <=$entries_per_page; $i++)
							{
								$row = $resultA->fetch_assoc();
								require('displaySearchResultsScript.php');
							}
					}
			}
		//else display all the recrods bar the last page, we will handle this on its own	
		else
			{	//Displays all records bar the first page
		 		if($page_number < $total_pages)
		 			{			
						//this script displays the adverts based on the search results
							for ($i=1; $i <=$entries_per_page; $i++)
								{
									$row = $resultA->fetch_assoc();
									require('displaySearchResultsScript.php');
								}
					}
				//Displays the very last page on its own as it has a unique number of records
				if($page_number == $total_pages)
		 			{			
						//this script displays the adverts based on the search results
						for ($i=1; $i <=$remaining; $i++)
							{
							$row = $resultA->fetch_assoc();
							require('displaySearchResultsScript.php');
							}
					}
				}
					
?>  



[Edited by - Sonnenblume on October 18, 2006 9:45:58 AM]

Share this post


Link to post
Share on other sites
Advertisement
For the love god, use the SOURCE tags...

Also, how about using the LIMIT param in the query?
Add a $PageSize = 20; constant somewhere, and then use it to calc the LIMIT part:
"QUERY HERE LIMIT $Page * $PageSize, $Page + 1 * $PageSize";

Toolmaker

Share this post


Link to post
Share on other sites
Ok, ive not got it working but I think ive figured out whats wrong with it.

First of all, say im showing 5 records per page. Its sorting my results into alphabetical order, and then on printing out records 1-5 in the correct order on the first page.

However, when the user clicks on the second page, it is forgetting that the results have all been sorted, and then taking records 5-10 out of the database, rather than the sorted results Ive produced.

Any suggestions on how to work around this?

Share this post


Link to post
Share on other sites
I know whats wrong. When I click on a link to view the next set of results, its forgetting what the 'orderBy' value is. Ill go away and find a way around that.

Share this post


Link to post
Share on other sites
As toolmaker said, use the LIMIT statement and let MySQL do the sorting. It's a thousand times better at it than you are. It was built to do that.

Small fix to toolmaker's snippet:

"QUERY HERE LIMIT $Page - 1 * $PageSize, $Page * $PageSize";


This way you'll actually get 1-$PageSize on the first page, instead of the zeroth page.

Share this post


Link to post
Share on other sites
One more small fix,

"QUERY HERE LIMIT ($Page - 1) * $PageSize, $Page * $PageSize";

Can't forget the parenthesis or else operator precedence takes over and evaluates 1 * $PageSize first.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!