[SOLVED] Help with pagination of 2 tables (includes code)

[BlackenedSky]

Hi,

 

I'm attempting to paginate some mysql results but it is proving to be harder than it should be!

 

I have 2 tables - one of these tables contains an index type structure of reference numbers. From this list of reference numbers, I pull those which match certain WHERE criterea. Then I run a query on the 2nd table and pull data from it where the reference number matches, but again this has WHERE criterea relevant to values in the 2nd table.

 

The only problem is this...pagination works fine until it jumps into pulling data from the second table.

It is set to pull out 6 results in total for a page, so I pull out 6 results from the first table.

This is where the problem comes because then the search in the second table is only within these 6 results, and so can return anywhere from 0 to 6 results (it returns only results which pass the 1st query for table 1, then narrows them based on the type using data in the second table.

 

To better illustrate - you have a tube of smarties, you pull out 6 blue ones as you only want 6. Then you get rid of any that are chipped and give the ones left to your mate (pft, as if. e-numbers are for me!!!)

 

Obviously my pagination method is completely wrong!

 

So yeah, my pages don't always return 6 results if the search criterea is to specific, as it only searches within 6 results at a time because I want it paginated.

 

Anyone got any ideas on how I can solve this problem so that I always have 6 results being output on the page?

 

(Btw, if this is too confusing let me know and I'll try to explain better. Makes sense to me, but then I'm the one who's asking the question!)

 

Here's my code:

 

$limit = 'LIMIT ' . ($cPage - 1) * $perPage . ',' . $perPage;
		$pList = mysql_query("SELECT refNo FROM propertyList WHERE (type='$type' && areaID LIKE '$area' && price >= '$minprice'  && price <= '$maxprice') ORDER BY price,areaID ASC $limit");
		if (!$pList) {
			echo "No properties found meeting your criterea.";
			return;
		}

		$pageCount = mysql_query("SELECT COUNT(refNo) FROM propertyList WHERE (type='$type' && areaID LIKE '$area' && price >= '$minprice'  && price <= '$maxprice')");
		$pageCount = mysql_fetch_row($pageCount);
		$pageCount = ceil($pageCount[0]/$perPage);
		$pagination = pagination($pageCount,$perPage,$cPage);

		$isResult = false;
		$displayed = false;
		while ($pListRow = mysql_fetch_array($pList)) {
			if (!$displayed) { echo $pagination; $displayed = true; }
			if ($pool) {
				if ($garage) {
					$hList = mysql_query("SELECT refNo FROM houses WHERE (refNo='{$pListRow['refNo']}' && beds>='$beds' && baths >='$baths' && pool='1' && garage='1' && propsize>='$propsize' && plotsize>='$plotsize')");
					if ($hList = mysql_fetch_assoc($hList)) { printResult($hList['refNo'],false); $isResult=true; }
				} else {
					$hList = mysql_query("SELECT refNo FROM houses WHERE (refNo='{$pListRow['refNo']}' && beds>='$beds' && baths >='$baths' && pool='1' && propsize>='$propsize' && plotsize>='$plotsize')");
					if ($hList = mysql_fetch_assoc($hList)) { printResult($hList['refNo'],false); $isResult=true; }
				}
			} else {
				if ($garage) {
					$hList = mysql_query("SELECT refNo FROM houses WHERE (refNo='{$pListRow['refNo']}' && beds>='$beds' && baths >='$baths' && garage='1' && propsize>='$propsize' && plotsize>='$plotsize')");
					if ($hList = mysql_fetch_assoc($hList)) { printResult($hList['refNo'],false); $isResult=true; }
				} else {
					$hList = mysql_query("SELECT refNo FROM houses WHERE (refNo='{$pListRow['refNo']}' && beds>='$beds' && baths >='$baths' && propsize>='$propsize' && plotsize>='$plotsize')");
					if ($hList = mysql_fetch_assoc($hList)) { printResult($hList['refNo'],false); $isResult=true; }
				}
			}
		}
		if(!$isResult) echo "No properties found meeting your criterea.";
		else echo $pagination;

[BlackenedSky]

I managed to solve my own problem in the end, although it's far from pretty or efficient! But hey, it works at least

 

Basically, I just got rid of the limit on the 1st query, then from that put all the refNo's into one loooong  "OR" string, and put that into the second query. Downside is the string could contain up to 500 reference numbers so you can see what I mean by it not being efficient, and it will probably cause some errors no doubt once the couple hundred mark is reached, if not easily before!

 

Here's the code:

		$limit = 'LIMIT ' . ($cPage - 1) * $perPage . ',' . $perPage;
		$pList = mysql_query("SELECT refNo FROM propertyList WHERE (type='$type' && areaID LIKE '$area' && price >= '$minprice'  && price <= '$maxprice') ORDER BY price,areaID ASC");
		if (!$pList || mysql_num_rows($pList) < 1) {
			echo "No properties found meeting your criterea.";
			return;
		}

		$arr = array();
		while ($row = mysql_fetch_array($pList)) {
			$arr[sizeOf($arr)] = $row['refNo'];
		}
		$arr = "(refNo='" .implode($arr,"' OR refNo='"). "')";

		if ($pool) {
			if ($garage) { //pool and garage
				$pageCount = mysql_query("SELECT COUNT(refNo) FROM houses WHERE ($arr && beds>='$beds' && baths >='$baths' && pool='1' && garage='1' && propsize>='$propsize' && plotsize>='$plotsize')");

				$hList = mysql_query("SELECT refNo FROM houses WHERE ($arr && beds>='$beds' && baths >='$baths' && pool='1' && garage='1' && propsize>='$propsize' && plotsize>='$plotsize') $limit");
			} else { //just pool
				$pageCount = mysql_query("SELECT COUNT(refNo) FROM houses WHERE ($arr && beds>='$beds' && baths >='$baths' && pool='1' && propsize>='$propsize' && plotsize>='$plotsize')");

				$hList = mysql_query("SELECT refNo FROM houses WHERE ($arr && beds>='$beds' && baths >='$baths' && pool='1' && propsize>='$propsize' && plotsize>='$plotsize') $limit");
			}
		} else {
			if ($garage) { //just garage
				$pageCount = mysql_query("SELECT COUNT(refNo) FROM houses WHERE ($arr && beds>='$beds' && baths >='$baths' && garage='1' && propsize>='$propsize' && plotsize>='$plotsize')");

				$hList = mysql_query("SELECT refNo FROM houses WHERE ($arr && beds>='$beds' && baths >='$baths' && garage='1' && propsize>='$propsize' && plotsize>='$plotsize') $limit");
			} else { //neither
				$pageCount = mysql_query("SELECT COUNT(refNo) FROM houses WHERE ($arr && beds>='$beds' && baths >='$baths' && propsize>='$propsize' && plotsize>='$plotsize')");

				$hList = mysql_query("SELECT refNo FROM houses WHERE ($arr && beds>='$beds' && baths >='$baths' && propsize>='$propsize' && plotsize>='$plotsize') $limit");
			}
		}
		if (!$pageCount) {
			echo "No properties found meeting your criterea.";
			return;
		}
		$pageCount = mysql_fetch_row($pageCount);

		$pageCount = ceil($pageCount[0]/$perPage);
		$pagination = pagination($pageCount,$perPage,$cPage);

		$isResult = false;
		$displayed = false;
		while ($row = mysql_fetch_array($hList)) {
			if (!$displayed) { echo $pagination; $displayed = true; }

			printResult($row['refNo'],false);
			$isResult=true;
		}
		if(!$isResult) echo "No properties found meeting your criterea.";
		else echo $pagination;

		@mysql_free_result($hList);
		@mysql_free_result($pList);