Directory listings pagination

[bloodbender]

HELLOOOO Daniweb!

I'm stuck at an issue that I can't seem to get around with my standard pagination codes I've used in the past. I'm developing a business directory and as with most of those, the listings are priced according to hierarchy:
- sponsored (category sponsor, always on top)
- premier
- regular

There's going to be only one sponsored listing for each category (Accountants, Dentists, etc.), max of 10 "premier" and unlimited regular listings. Sponsored is straight forward enough. Currently, premier and regular listings show up underneath the sponsored listing which is great, however as more premier listings get added, I'd like the regular to be pushed down the list and eventually to the next page after a max of 11 listings fill up the first page (1 sponsor, 10 premier). All the listings are in the same table with a "categoryID" and "listingType".

Here's the code so far... any assistance is mucho mucho appreciated.

// Check for sponsored listing
$spon_query = "SELECT * FROM tbl_listings WHERE category_id = '".$get_category_info_rows['id']."' AND listing_type='spon'";
$spon_result = mysql_query($spon_query);
$spon_count = mysql_num_rows($spon_result);
$spon_rows = mysql_fetch_assoc($spon_result);
	if($spon_count <= 0) { // No sponsored listing
		echo "<div class=\"categorypagelistingspon\">
		<div class=\"categorypageadvertise\">SPONSOR THIS CATEGORY.  CALL (###) ### - ####</div>
		</div>";
	} else { // There is sponsored listing, display it
		echo "<div class=\"categorypagelistingspon\" onclick=\"window.location = '".$mainurl."Category/".$get_category_info_rows['category_slug']."/".$spon_rows['business_slug']."'\">
		<div class=\"listingimg\">
									
		</div>
		<div class=\"categorypagelistingtitle\">".$spon_rows['business_name']."</div>
		<div class=\"listingleft\">
		<span class=\"categorypagelistingaddr\">".$spon_rows['business_street_number']." ".$spon_rows['business_street']."<br />".$spon_rows['business_city'].", ".$spon_rows['business_province']." ".$spon_rows['business_postalcode']."</span>
									<br />
									<span class=\"categorypagelistingphone\">".$spon_rows['business_phone']."</span>
		</span>
		</div>
		<div class=\"listingright\">
		<span class=\"categorypagelistingcontact\">Email: </span>
		<span class=\"categorypagelistingaddr\">".$spon_rows['business_email']."</span>
		<br />
		<span class=\"categorypagelistingcontact\">Phone: </span>
		<span class=\"categorypagelistingaddr\">".$spon_rows['business_phone']."</span>
		</div>
	</div>";
	}

// Display all premium listings
$prem_query = "SELECT * FROM tbl_listings WHERE category_id = '".$get_category_info_rows['id']."' AND listing_type='prem' ORDER BY business_name ASC";
$prem_result = mysql_query($prem_query);
$prem_count = mysql_num_rows($prem_result);
while($premrows = mysql_fetch_assoc($prem_result)){
	echo "<div class=\"categorypagelistingsprem\" onclick=\"window.location = '".$mainurl."Category/".$get_category_info_rows['category_slug']."/".$premrows['business_slug']."'\">
	<div class=\"categorypagelistingtitle\">".$premrows['business_name']."</div>
	<div class=\"listingleft\">
	<span class=\"categorypagelistingaddr\">".$premrows['business_street_number']." ".$premrows['business_street']."<br />".$premrows['business_city'].", ".$premrows['business_province']." ".$premrows['business_postalcode']."</span>
	<br />
	<span class=\"categorypagelistingphone\">".$premrows['business_phone']."</span>
	</span>
	</div>
	<div class=\"listingright\">
	<span class=\"categorypagelistingcontact\">Email: </span>
	<span class=\"categorypagelistingaddr\">".$premrows['business_email']."</span>
	<br />
	<span class=\"categorypagelistingcontact\">Phone: </span>
	<span class=\"categorypagelistingaddr\">".$premrows['business_phone']."</span>
	</div>
	</div>";	
	}

// Display all regular listings
$regu_query = "SELECT * FROM tbl_listings WHERE category_id = '".$get_category_info_rows['id']."' AND listing_type='regu' ORDER BY business_name ASC";
$regu_result = mysql_query($regu_query);
$regu_count = mysql_num_rows($regu_result);
if($regu_count <= 0) {
	echo "";
} else {
	while($regu_rows = mysql_fetch_assoc($regu_result)){
	echo "<div class=\"categorypagelistingsregu\" onclick=\"window.location = '".$mainurl."Category/".$get_category_info_rows['category_slug']."/".$regu_rows['business_slug']."'\">
	<div class=\"listingimg\">
	</div>
	<div class=\"categorypagelistingtitle\">".$regu_rows['business_name']."</div>
	<div class=\"listingleft\">
        <span class=\"categorypagelistingaddr\">".$regu_rows['business_street_number']." ".$regu_rows['business_street']."<br />".$regu_rows['business_city'].", ".$regu_rows['business_province']." ".$regu_rows['business_postalcode']."</span>
	<br />
	<span class=\"categorypagelistingphone\">".$regu_rows['business_phone']."</span>
	</span>
	</div>
	<div class=\"listingright\">
	<span class=\"categorypagelistingcontact\">Email: </span>
	<span class=\"categorypagelistingaddr\">";
if(strlen($regu_rows['business_email']) >= 30){
	echo substr($regu_rows['business_email'],0,30)."...";
} else {
	echo $regu_rows['business_email'];	
}
	echo "</span>
	<br />
	<span class=\"categorypagelistingcontact\">Phone: </span>
	<span class=\"categorypagelistingaddr\">".$regu_rows['business_phone']."</span>
	</div>
	</div>";
}
}

[twiss]

I would do something like this (provided I understand what you want):

$page = $_GET['p']; // 1, 2, 3, ...
$start = ($page - 1) * 10;
$counter = 0;

In all those while loops, add:

while(.... && $counter < $start + 10) {
  if($counter++ < $start) continue;
  ....
}

You might also want to query the COUNT(*) first and do something genius with the other queries, but this is by far the easiest solution.