pocobueno1388 Posted September 24, 2006 Share Posted September 24, 2006 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]<?phpinclude '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 Hyperlinksecho "<center>Select a Page<br />";// Build Previous Linkif($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 Linkif($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. Quote Link to comment Share on other sites More sharing options...
printf Posted September 24, 2006 Share Posted September 24, 2006 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! Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted September 24, 2006 Author Share Posted September 24, 2006 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... Quote Link to comment Share on other sites More sharing options...
printf Posted September 24, 2006 Share Posted September 24, 2006 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! Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted September 24, 2006 Author Share Posted September 24, 2006 Awesome =) Thank you so much. Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted September 24, 2006 Author Share Posted September 24, 2006 Does anyone else want to try to figure this out? Quote Link to comment Share on other sites More sharing options...
printf Posted September 25, 2006 Share Posted September 25, 2006 Sorry I had to bring the kids for ice cream![code=php:0]<?phpinclude '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 actionprint<<<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! Quote Link to comment Share on other sites More sharing options...
pocobueno1388 Posted September 27, 2006 Author Share Posted September 27, 2006 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] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.