why is my pagination code not working?

[michellephp]

Hello again everyone!

I simply cannot work it out, after going through tutorial after tutorial, it just aint working :( The problem is:

<EDIT> Ok, solved some of the problem. But now, ALL results are showing on the one page, even though it accurately says
"Showing: 1 to 10 of 16 Listings"

I know it has something to do with $limit (because later in the page I have: $sql .= $sort . $limit;)
but when I include:

$limit = ' LIMIT '.$startRow_p.', '.$maxRows_p;

it makes it say, "showing result 1 to 10 of 10 listings" and only shows 10 listings and no option of 'next page', regardless of how many listing I have.

<EDIT> HMMM, it seems as if the $limit is limiting the ACTUAL results that show up at all. I think that might be because of this:
$all_p = mysql_query($sql.$sort);
$totalRows_p = @mysql_num_rows($all_p);

and above that I have: $sql .= $sort . $limit;

I really really really would apprectae some help. I've been at this one little thing for about 6 hours. No matter what I cut out, or put back in, I have one of two results:
1). I get the correct 'showing 1-10 of 16' but all the results are on the first page. or,
2.) I get 'showing 1 - 10 of 10' with only 10 showing but without being able to see the rest of the results (eg it thinks there are only 10 there)



This is the code:

#### BUILD SEARCH SQL BASED ON SEARCH TYPE ####

#defauts

$maxRows_p = 10;
if(!isset($_GET['page'])){
$pageNum_p = 1;
} else {
$pageNum_p = $_GET['page'];
}
$startRow_p = (($pageNum_p * $maxRows_p) - $maxRows_p);
$sql = mysql_query("SELECT * FROM pages LIMIT $startRow_p, $maxRows_p");





## Start building sql for GET varables for advanced search

//Add Price From
if(isset($_REQUEST['pfrom']) && ($_REQUEST['pfrom'] != '-1'))
$search[] = ' price >= '.$_REQUEST['pfrom'];
//Add Max Price
if(isset($_REQUEST['pto']) && ($_REQUEST['pto'] != '-1'))
$search[] = ' price <= '.$_REQUEST['pto'];

ETC ETC (more search options here)

//implode to search string on ' and ';
$searchStr = @implode(' and ',$search);

$sql = 'select * FROM items WHERE (expires > NOW()) and active = "Yes" and ';
$sql .= $searchStr;

//Add column sorting
if($_REQUEST['sort'] != '')
$sort = ' order by price asc ';
else
$sort = $_REQUEST['sort'];


### DEBUG
if($debugP) echo 'Advanced Search Sql<hr>'.$sql;

$error['Results'] = 'No results found, please search again';




### Finished Building search sql and execting #####
$sql .= $sort . $limit;

//Perform search
$searchResults = $mysql->exSql($sql);

### BUILD OUTPUT ####

if (isset($_GET['totalRows_p'])) {
$totalRows_p = $_GET['totalRows_p'];
} else {
$all_p = mysql_query($sql.$sort);
$totalRows_p = @mysql_num_rows($all_p);
}
$totalPages_p = ceil($totalRows_p/$maxRows_p)-1;

// Build Page Number Hyperlinks
echo "<center>Select a Page<br />";

// Build Previous Link
if($page > 1){
$prev = ($page - 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<Previous</a> ";
}

for($i = 1; $i <= $totalPages_p; $i++){
if(($pageNum_p) == $i){
echo "$i ";
} else {
echo "<a href=\"".$_SERVER['PHP_SELF']."?pageNum_p=$i\">$i</a> ";
}
}

// Build Next Link
if($page < $totalPages_p){
$next = ($pageNum_p + 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?pageNum_p=$next\">Next>></a>";
}
echo "</center>";


?>

[michellephp]

hi again,

I think it definitely has something to do with:

$sql .= $sort . $limit;

and

//Add column sorting
if($_REQUEST['sort'] != '')
$sort = ' order by price asc ';
else
$sort = $_REQUEST['sort'];

and

$limit

Please help!!! It's absolutely driving me crazy!!!!!!!!!!!!!!!!!!!!!!!!

<EDIT> I forgot to mention, it's a search page that send the results to the page in which contains the code above.

Eg search.php --- > properties.php

Do I need to enter the limit stuff on the search page instead... maybe?

[michellephp]

OK, I have found some code, which sort of makes it work, however it is not pulling the search results. This is the new code - how do I adapt my exisiting code to use this new stuff?

$query_p = "SELECT * FROM items WHERE mid = ".$_SESSION['memberID'].".$sort"; [!--coloro:#33CC00--][span style=\"color:#33CC00\"][!--/coloro--]So query_p needs to be the results of the form, eg sql... right?[!--colorc--][/span][!--/colorc--]
//echo $query_p; //debug output
$query_limit_p = sprintf("%s LIMIT %d, %d", $query_p, $startRow_p, $maxRows_p);
$p = mysql_query($query_limit_p, $myconn) or die(mysql_error());

if (isset($_GET['totalRows_p'])) {
$totalRows_p = $_GET['totalRows_p'];
} else {
$all_p = mysql_query($query_p);
$totalRows_p = mysql_num_rows($all_p);
}
$totalPages_p = ceil($totalRows_p/$maxRows_p)-1;

$queryString_p = "";
if (!empty($_SERVER['QUERY_STRING'])) {
$params = explode("&", $_SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_p") == false &&
stristr($param, "totalRows_p") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_p = "&" . htmlentities(implode("&", $newParams));
}
}
$queryString_p = sprintf("&totalRows_p=%d%s", $totalRows_p, $queryString_p);

If you have any idea, I'd really appreciate ANY hints or ideas you have. Thank-you.

[!--coloro:#FF0000--][span style=\"color:#FF0000\"][!--/coloro--]By the way, if I take out WHERE mid = ".$_SESSION['memberID'].".$sort from the very first line (above), it says it has ALL properties, (ie showing 1 - 3 of 52 properties), but still shows the 16 that are in the search criteria only (on the first page, ie showing more than the limit of 3!!). But then if you click next, you get "showing 3 -6 of 52" but you see the same 16 properties as on the first page.[!--colorc--][/span][!--/colorc--]


I think the code just above is similar to:
[a href=\"http://www.phpfreaks.com/quickcode/Pagination/164.php\" target=\"_blank\"]http://www.phpfreaks.com/quickcode/Pagination/164.php[/a]

someone must have an idea?

[michellephp]

I'm sorry for bumping, but I really need some help with this. I am willing to pay (see freelancing section). Surely someone out there has done heaps of paginations and knows what I am doing wrong? Even if I have to start over, seriously I need your help and it would mean so much to me to fix this.

Thank-you,
Michelle.

[wickning1]

There were a lot of small problems, I cleaned it up, but I don't know the details of that database class you are using, so you may still need to modify this a bit.

[code]<?php
#### BUILD SEARCH SQL BASED ON SEARCH TYPE ####

#defaults
$maxRows_p = 10;

if(!isset($_GET['page'])){
    $pageNum_p = 1;
} else {
    $pageNum_p = $_GET['page'];
}
$startRow_p = ($pageNum_p - 1) * $maxRows_p;
$limit = " LIMIT $startRow_p, $maxRows_p";

## Start building sql for GET varables for advanced search

//Add Price From
if(isset($_REQUEST['pfrom']) && ($_REQUEST['pfrom'] != '-1'))
    $search[] = ' price >= '.$_REQUEST['pfrom'];
//Add Max Price
if(isset($_REQUEST['pto']) && ($_REQUEST['pto'] != '-1'))
    $search[] = ' price <= '.$_REQUEST['pto'];

//ETC ETC (more search options here)

//implode to search string on ' and ';
$searchStr = @implode(' and',$search);

$select = 'select *';
$basicinfo .= ' FROM items WHERE (expires > NOW()) and active = "Yes" and ';
//Add column sorting
if($_REQUEST['sort'] != '')
    $sort = ' order by price asc';
else
    $sort = $_REQUEST['sort'];
    
$sql = $select.$basicinfo.$searchStr.$sort.$limit;

### DEBUG
if($debugP) echo 'Advanced Search Sql<hr>'.$sql;

$error['Results'] = 'No results found, please search again';

//Perform search
$searchResults = $mysql->exSql($sql);

### BUILD OUTPUT ####

// get total number of rows
$countsel = 'select COUNT(*)';
$csql = $countsql.$basicinfo.$searchStr.$sort.$limit;
$all_p = mysql_query($csql);
$row = mysql_fetch_row($all_p);
$totalRows_p = $row[0];

$totalPages_p = ceil($totalRows_p/$maxRows_p);

// Build Page Number Hyperlinks
echo "<center>Select a Page<br />";

// Build Previous Link
if($pageNum_p > 1){
    $prev = ($pageNum_p - 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<Previous</a> ";
}

for($i = 1; $i <= $totalPages_p; $i++){
    if($pageNum_p == $i){
        echo "$i ";
    } else {
        echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
    }
}

// Build Next Link
if($pageNum_p < $totalPages_p){
    $next = ($pageNum_p + 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next>></a>";
}
echo "</center>";

?>[/code]