Pagination is not displaying correct page numbers.

[pocobueno1388]

Sorry to post another topic on this script, but it is really giving me difficulties =/

I just paginated this page and made the max results per page 2. When there are more then 2 results that should be being displayed, it doesn't show another page, thus you can't click on page number 2 to see the rest of the results.

Here is the code:

[code]
<?php
include 'header.php';

if ($_POST['submit']) {
$username = trim(addslashes($_POST['username']));
$id = trim($_POST['id']);$query = "SELECT `playerID`, `username`, `online`, `upgrade` FROM `players` WHERE 1";

if($username) $query.=" AND `username` = '$username'";
if($id) $query.=" AND `playerID` = '$id'";


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


$max_results = 2;

$from = (($page * $max_results) - $max_results);

$query.="  LIMIT $from, $max_results";

$result = mysql_query($query);


print<<<HERE


<h2>Search Results:</h2><p>

<table width="75%" align="center" bgcolor="49614A">

<th>ID</th>
<th>Username</th>
<th>Online?</th>
<th>Account Type</th>

<tr align="center">

HERE;

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

if ($row['upgrade'] == 'yes'){
$account = Diamond;

} else {
$account = Basic;
}

echo "<td bgcolor='788D7A'>$row[playerID]</td>";

echo "<td bgcolor='788D7A'><a href=\"viewprofile.php?profileid=$row[playerID]\">$row[username]</a><p></td>";

echo "<td bgcolor='788D7A'>$row[online]</td>";

echo "<td bgcolor='788D7A'>$account</td>";

echo "<tr bgcolor='788D7A' align='center'>";


}
echo '</td></table>';




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

$total_results = mysql_num_rows($result);

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

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

// Build Previous Link
if($page > 1){
    $prev = ($page - 1);
    echo "<a href='searchplayers.php?action=search&page=$prev'><<Previous</a> ";
}

for($i = 1; $i <= $total_pages; $i++){
    if(($page) == $i){
        echo "$i ";
        } else {
            echo "<a href='searchplayers.php?action=search&page=$i'>$i</a> ";
    }
}

// Build Next Link
if($page < $total_pages){
    $next = ($page + 1);
    echo "<a href='searchplayers.php?action=search&page=$next'>Next>></a>";
}
echo "</center>";





exit;
}

print<<<HERE

<table align="center" bgcolor="49614A">
<td bgcolor="788D7A">
<h3>Search Players</h3>
<form action="searchplayers.php?action=search" method="POST">
Username:<br>
<input type="text" name="username"><p>
ID:<br>
<input type="text" name="id" size=8><p>
<input type="submit" name="submit" value="Search">

</form>
</td></table><p>
HERE;


?>

[/code]

I think it is this line that is throwing everything off:

[code]$total_results = mysql_num_rows($result);[/code]

The regular way would be this:

[code]
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM table"),0);
[/code]

But my query looks like this:

[code]
if($username) $query.=" AND `username` = '$username'";
if($id) $query.=" AND `playerID` = '$id'";

$query.="  LIMIT $from, $max_results";

$result = mysql_query($query);
[/code]

I am not sure how to change it to make it work. I had to do it a little different then what the regular way is because my query is a little different then usual because it is a search script and I can't just select certain things from the database because it depends on what the users searchs for.

Any help is appreciated =) Thanks.

[printf]

If you limit a query, then the total results will be the limit you set, even if there are more rows. So you can't expect to get the total rows from the same query. You have to make another query not using limit and "SELECT COUNT(*) AS total..." and use that for your next/prev linking!


me!

[pocobueno1388]

What query am I supposed to use? The query depends on what the user submits...so I'm not sure what to do. Maybe I just don't understand what you are saying...

[printf]

It a good idea to do your total query before doing your result query, this way you know if you have more results to show and if using LIMIT is even necessary!

Give me a few minutes and I'll write you an example, using your code!


me!

[pocobueno1388]

Awesome =) Thank you so much.

[pocobueno1388]

Does anyone else want to try to figure this out?

[printf]

Sorry I had to bring the kids for ice cream!

[code=php:0]<?php

include 'header.php';

$max_results = 2;

$links = '';

if ( ! empty ( $_REQUEST['action'] ) )
{
$q_a = "SELECT playerID, username, online, upgrade FROM players WHERE ";
$q_b = "SELECT COUNT AS total FROM players WHERE ";

$add = array ();

if ( ! empty ( $_REQUEST['username'] ) )
{
$un = trim ( $_REQUEST['username'] );

if ( ! empty ( $un ) )
{
$add[] = "username = '" . mysql_real_escape_string ( $un ) . "'";
}
}

if ( ! empty ( $_REQUEST['id'] ) )
{
$add[] = "playerID = " . intval ( $_POST['id'] );
}

if ( empty ( $add ) )
{
$r = mysql_query ( $q_b . implode ( ' AND ', $add ) );

$w = mysql_fetch_assoc ( $r );

if ( $w['total'] > 0 )
{
$max = ceil ( $w['total'] / $max_results );

/* only make links if we have page(s) of results */

if ( $max > 1 )
{
$page = 1;
$prev = 0;
$next = 1;

if ( ! empty ( $_REQUEST['page'] ) )
{
$pt = intval ( $_REQUEST['page'] );

if ( $pt > $max )
{
/* for the funny people */

$page = $max; // anything above the max page send them to the last page
$prev = 1;
$next = 0;
}
else if ( $pt < $page )
{
/* for the funny people */

$page = 1;    // anything below the min page send them to the first page
$prev = 0;
$next = 1;
}
else
{
$page = $pt;  // valid user submitted page

if ( $page == $max )
{
$prev = 1;
$next = 0;
}
else if ( $page == 1 )
{
$prev = 0;
$next = 1;
}
else
{
$prev = 1;
$next = 1;
}
}
}

$query = "&username=" . ( isset ( $un ) ? urlencode ( $un ) : '' ) . "&id=" . ( isset ( $id ) ? $id : '' );

if ( $prev == 1 )
{
    $links .= "<a href='searchplayers.php?action=search&page=" . ( $page - 1 ) . $query . "'> << </a> ";
}
else
{
/* for page formatting, nothing more */

    $links .= "<< ";
}

for ( $i = 1; $i <= $max; $i++)
{
if ( $page == $i )
{
$links = $i . " ";
}
else
{
    $links .= "<a href='searchplayers.php?action=search&page=" . $i . $query . "'> " . $i . " </a> ";
}
}

if ( $next == 1 )
{
    $links .= "<a href='searchplayers.php?action=search&page=" . ( $page + 1 ) . $query . "'> >> </a>";
}
else
{
/* for page formatting, nothing more */

    $links .= ">>";
}
}

$r = mysql_query ( $q_a . implode ( ' AND ', $add ) . " LIMIT " . ( $page == 1 ? 0 : ( $page * $max_results ) ) . ", " . $max_results );
print<<<HERE

<h2>Search Results:</h2><p>

<table width="75%" align="center" bgcolor="49614A">

<th>ID</th>
<th>Username</th>
<th>Online?</th>
<th>Account Type</th>



HERE;
while ( $item = mysql_fetch_assoc ( $r ) )
{
echo "<tr align='center'>";
echo "<td bgcolor='788D7A'>" . $item['playerID'] . "</td>";
echo "<td bgcolor='788D7A'><a href=\"viewprofile.php?profileid=" . $item['playerID'] . "\">" . $item['username'] . "</a><p></td>";
echo "<td bgcolor='788D7A'>" . $item['online'] . "</td>";
echo "<td bgcolor='788D7A'>" . ( $item['upgrade'] == 'yes' ? 'Diamond' : 'Basic' ) . "</td>";
echo "</tr>";
}

echo '</table>';

if ( ! empty ( $links ) )
{
echo "<center>Select a Page<br />" . $links . "</center>";
}

exit ();
} // end total results > 0 (could add a (else) here to throw a error [no results found])
} // end if some sort of valid search was passed (could add a (else) here to throw a error [invalid search request])
} // end if action

print<<<HERE

<table align="center" bgcolor="49614A">
<td bgcolor="788D7A">
<h3>Search Players</h3>
<form action="searchplayers.php?action=search" method="post">
Username:<br>
<input type="text" name="username"><p>
ID:<br>
<input type="text" name="id" size=8><p>
<input type="submit" name="submit" value="Search">

</form>
</td></table><p>
HERE;


?>[/code]


me!

[pocobueno1388]

Wow, thank you for spending your time doing that for me =D Although I am getting an error which I can't fix.

error:

[code]
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/colin/public_html/searchplayers.php on line 104
[/code]