[Resolved] Paged Recordsets

[furious5]

Can anyone help me out, I'm currently struggling with a paged recordset. I am getting the correct number of totalrows returned, but when I actually go to page through the records I find there is a duplication of records.

I've sat here all day trying to work out the problem but can't see the problem, maybe a fresh set of eyes will be able to work it out.

Here is a portion of the code, I can post all the code if neccesarry but I think this should suffice:

mysql_select_db($database_conVillas, $conVillas);

// Maximum SQL Count
  $test = mysql_query($sql) or die(mysql_error());
	$totalRows = mysql_num_rows($test);
	while($row = mysql_fetch_assoc($test)){

   $rows[$row[ID]] = $row; // Any duplication _should_ overwrite the previous instance 
}

// SQL LIMIT
if (isset($HTTP_GET_VARS['pageNum'])) {
  $pageNum = $HTTP_GET_VARS['pageNum'];
}
$startRow = $pageNum * $maxRows;

$result_limit = sprintf("%s LIMIT %d, %d", $sql, $startRow, $maxRows);
$result = mysql_query($result_limit, $conVillas) or die(mysql_error());

//$totalPages = ceil($totalRows/$maxRows)-1;

if (isset($HTTP_GET_VARS['totalRows'])) {
  $totalRows = $HTTP_GET_VARS['totalRows'];
} else {
  $test = mysql_query($sql) or die(mysql_error());
	$totalRows = mysql_num_rows($test);
	while($row = mysql_fetch_assoc($test)){

	$rows[$row[ID]] = $row; // Any duplication _should_ overwrite the previous instance 

}
}

$totalRows = count($rows);
$totalPages = ceil($totalRows/$maxRows)-1;

?>

Showing 
record <?php echo ($startRow + 1) ?> to <?php echo min($startRow + $maxRows, $totalRows) ?> 
of <?php echo $totalRows ?> 


<?php

if ($totalRows == 0){
echo 'Sorry your search did not return any results. Please return to the search page <a href=search.php>here</a>';
} else {

while($row = mysql_fetch_assoc($result)){

	echo "<p>";
	echo $row[ID]. "<br>";
	echo $row[Name]. "<br>";
	echo $row[Short_Description];

}

}

[AstroTeg]

What does the SQL look like for $result_limit?

[furious5]

The SQL looks like this:

$sql = "SELECT DISTINCT * FROM villas, pricing1 WHERE villas.resort = 'pollenca' AND villas.beds LIKE '%%' AND pricing1.price != '0' AND pricing1.availability = '1' AND pricing1.week >= 1073088000 AND pricing1.week <= 1074297600 AND villas.id = pricing1.id ";

[AstroTeg]

And you don't get any duplicates with that query? (you try it in phpMyAdmin or some SQL tool?)

[furious5]

Thats the problem I do get duplicates!! I'm trying to eliminate the duplicate records.

At the moment I have the results returning the correct totalrows as per this bit of code running the maximum SQL count, but when I then go to display the records I am still provided with 18 records which is 6 (the maximum total) x 3 weeks.

I'm totally stuck with this code at the moment.

[AstroTeg]

If you're running just the SQL and you're still getting dups, then you need to just tweak the SQL.

I'm thinking your problem might be here:

"DISTINCT * "

If you're going to do distinct, you have to select a subset of the fields. The "*" and "DISTINCT" can conflict with each other. Otherwise, you may need to resort to using the GROUP BY clause.

[furious5]

Perfect!

I hadn't thought about using the GROUP BY clause.

I've been sat staring at this code, trying to work out different ways to eliminate the records and by rights I should have realised that if the SQL was returning duplicate records the SQL needed tweaking!

AstroTeg you are a star!!!!