Pagination help

[leev3]

Sorry, I know this comes up a lot, I am using a pagination script I found on this site, I think by phpfreak(Eric), everything works fine except when i invoke a $querytest based on in put from a form.

 

 

$sql = mysql_query("SELECT * FROM lca where $querytest LIMIT $from, $max_results");

 

while($row = mysql_fetch_array($sql)){

    // Build your formatted results here.

    echo $row['local_rate_center']. "<br />";

   

}

 

// Figure out the total number of results in DB:

$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM lca where $querytest"),0);

 

// Figure out the total number of pages. Always round up using ceil()

$total_pages = ceil($total_results / $max_results);

 

 

echo $total_results;  this comes out to the correct row, but when I hit the next button, nothing?

 

what am i doing wrong?

[leev3]

It's almost like it lost the results of the query. 

[leev3]

here's the whole code:

 

// Get post info from lca_search_php form
$concat = " and  ";

if(($_POST['state'] != null)) {
        $querytest .= $con . " state =  '$state' ";
        $con = $concat;
}
elseif (($_POST['state'] == null))
{

}

if(($_POST['exchange'] != null)) {
        $querytest .= $con . " exchange like  '%{$_POST['exchange']}%' ";
        $con = $concat;
}
elseif (($_POST['exchnage'] == null))
{

}

if(($_POST['npa'] != null)) {
        $querytest .= $con . " npa =  '{$_POST['npa']}' ";
        $con = $concat;
}
elseif (($_POST['npa'] == null))
{

}


if(($_POST['nxx'] != null)) {
        $querytest .= $con . " nxx =  '{$_POST['nxx']}' ";
        $con = $concat;
}
elseif (($_POST['nxx'] == null))
{


}


// If current page number, use it
// if not, set one!

if(!isset($_GET['page'])){
    $page = 1;
} else {
    $page = $_GET['page'];
}

// Define the number of results per page
$max_results = 20;

// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results); 

// Perform MySQL query on only the current page number's results

$sql = mysql_query("SELECT * FROM lca where $querytest LIMIT $from, $max_results");

while($row = mysql_fetch_array($sql)){
    // Build your formatted results here.
    echo $row['local_rate_center']. "<br />";
    
}

// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM lca where $querytest"),0);

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);


echo $total_results;


// 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 <= $total_pages; $i++){
    if(($page) == $i){
        echo "$i ";
        } else {
            echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
    }
}

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

[AndyB]

If you echo'd your queries as a testing method, the problem would be readily apparent.

 

I'll give you a hint ... $con = $concat; simply resets the value of $con every time it's encountered. I suspect $con.= $concat would work a lot better.

 

$_POST['exchnage'] might be a problem as well, unless that's really the form field name as spelled.

 

Last but not least, please put your code here between CODE tags - much easier to read.

[leev3]

I tried :

 

$echo $sql;  which I thought was my query, but all I get is resource Id# 3, instead of an query string, which i would have expected something like " select from * lca where exchange like '\%exchange%\' ".

 

 

I tried your suggestion about the $con. = $concat, I got the white screen of doom.

 

 

It did at least have a back button for 9 pages worth, but never output the page numbers at the bottom, also I'm not really sure what you mean by code tages, just Code follows or somrthing? 

 

I am very new to all of this.

 

Thanks

[AndyB]

$sql = mysql_query("SELECT * FROM lca where $querytest LIMIT $from, $max_results");

 

In that code, $sql is the result of the query and it will be a 'resource'.  Instead, split that line into two - a querystring and a database query with error display like below. It makes debugging vastly easier. 

 

$query = "SELECT * FROM lca where $querytest LIMIT $from, $max_results"; //define query
$sql = mysql_query($query) or die("Error: ". mysql_error(). " with query ". $query); // show error

[leev3]

ha ha , cool.  here's my error when I hit page 2:

 

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 20, 20' at line 1 with query SELECT * FROM lca where LIMIT 20, 20,

 

Not really sure how it helps me yet, but at least I see what's causing the error.

[leev3]

I fibbed I see thew problem, it's losing the $querytest, hmmm

[leev3]

ok I'm stumped, why does my variable disappear on the second pass for the pagination?

 

[AndyB]

ok I'm stumped, why does my variable disappear on the second pass for the pagination?

 

 

Because none of the original search form selections are passed to the next page, i.e. the querystring cannot be reconstructed.

[leev3]

so , okay here's the big question, how do i pass it?  I tried assigning it to a different variable, but no dice, I have googled "php passing variables", and most of the ones I could understand got me from the 1st page to the second, but petered out there.  Any more clues?

[leev3]

I tried a session variable, did not work.  Arrghhhh, am I asking the impossible?

[leev3]

If I set the variable into a function, would it carry through?

 

Something like:

 

code:

function varcarry()

{

$querytest = $querytest

}

 

$query = "SELECT * FROM lca where ". varcarry() ." LIMIT $from, $max_results";