COUNT, INNER JOIN and LIMIT?

[Kristoff1875]

Hi, could anyone tell me why this isn't working?

SELECT o.*, p.*, COUNT(*) as num
	FROM Offers o
	INNER JOIN Partners p USING (PartnerID) LIMIT $start, $limit

The LIMIT is to make pagination, it's working when just calling info from one table, but when I INNER JOIN the tables, the second page is just displaying blank.

 

Any help appreciated. Thanks

[mac_gyver]

is your query that gets the total number of matching rows that goes into producing the number of pages also using the same JOIN?

[Kristoff1875]

Yep, would that be the problem?

$query = "SELECT o.*, p.*, COUNT(*) as num
FROM Offers o
INNER JOIN Partners p USING (PartnerID)";

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

/* Setup vars for query. */
$targetpage = "searchoffers.php"; //your file name  (the name of this file)
$limit = 5; //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 o.*, p.*, COUNT(*) as num
FROM Offers o
INNER JOIN Partners p USING (PartnerID) WHERE PartnerArea = '$offerarea' LIMIT $start, $limit";

Edited May 20, 2013 by Kristoff1875

[Kristoff1875]

Ok, have changed some things that I thought may be the problem... But still no joy:

$query = "SELECT o.*, p.*, COUNT(*) as num
	FROM Offers o
	INNER JOIN Partners p USING (PartnerID)
	WHERE PartnerArea = '$offerarea'";
	
	$total_pages = mysql_fetch_array(mysql_query($query));
	$total_pages = $total_pages[num];
	
	/* Setup vars for query. */
	$targetpage = "searchoffers.php";			//your file name  (the name of this file)
	$limit = 1; 								//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 o.*, p.*
	FROM Offers o
	INNER JOIN Partners p USING (PartnerID) WHERE PartnerArea = '$offerarea' LIMIT $start, $limit";
	$result = mysql_query($sql);

[Kristoff1875]

I've sussed it. Nothing to see here! I wasn't passing the $offerarea variable to page 2!

[Barand]

All you want from the first query is the count

$query = "SELECT COUNT(*) as num
	FROM Offers o
	INNER JOIN Partners p USING (PartnerID)
	WHERE PartnerArea = '$offerarea'";

[Kristoff1875]

Even better, thanks bud.