kingnutter Posted August 10, 2009 Share Posted August 10, 2009 Hi everyone, I've been following the fantastic Pagination tutorial from this website, which makes everything very clear. Sadly when trying to customise for my own project I'm getting a bit mixed up. The results I wish to paginate are from a query of JOINed tables, and I'm not sure if I'm putting my COUNT in the right place. Perhaps I should have something like COUNT ($result). I would be grateful if someone has the time to have a quick scan and provide any pointers, also for potential future hiccups as I haven't proceeded past the errors as yet. Many thanks. KN <form name="search" method="post" action="search_results.php"> Search for: <input type="text" name="find" /> in <Select NAME="field"> <Option VALUE="track_artist">Track Artist</option> <Option VALUE="track_title">Track Title</option> <Option VALUE="all">All</option> </Select> <input type="hidden" name="searching" value="yes" /> <input type="submit" name="search" value="Search" /> </form> <?php // includes include('conf.php'); include('functions.php'); // Grab POST data sent from form $field = $_POST['field'] ; $find = $_POST['find'] ; $searching = $_POST['searching'] ; //This is only displayed if they have submitted the form if ($searching =="yes") { echo "<h2>Results</h2><p>"; //If they did not enter a search term we give them an error if ($find == "") { echo "<p>You forgot to enter a search term"; exit; } // Otherwise we connect to our Database // open database connection $connection = mysql_connect($host, $user, $pass) or die ('unable to connect!'); //select database mysql_select_db($db) or die ('unable to select database!'); $find = strtoupper($find); $find = strip_tags($find); $find = trim ($find); if ($field=='all') { $query = "SELECT COUNT t.moj_id, t.track_artist, t.track_title, m.moj_title FROM tracks t JOIN mojocd m ON t.moj_id = m.moj_id WHERE upper(t.track_artist) LIKE '%$find%' OR upper(t.track_title) LIKE '%$find%' LIMIT $offset, $rowsperpage"; } else //Or we search for our single search term, in the field the user specified { $query = "SELECT COUNT track_artist, track_title FROM tracks WHERE upper($field) LIKE'%$find%' LIMIT $offset, $rowsperpage"; } $result = mysql_query($query); $r = mysql_fetch_row($result); $numrows = $r[0]; // number of rows to show per page $rowsperpage = 10; // find out total pages $totalpages = ceil($numrows / $rowsperpage); // get the current page or set a default if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) { // cast var as int $currentpage = (int) $_GET['currentpage']; } else { // default page num $currentpage = 1; } // end if // if current page is greater than total pages... if ($currentpage > $totalpages) { // set current page to last page $currentpage = $totalpages; } // end if // if current page is less than first page... if ($currentpage < 1) { // set current page to first page $currentpage = 1; } // end if // the offset of the list, based on current page $offset = ($currentpage - 1) * $rowsperpage; if ($field=='all') { $query = "SELECT t.moj_id, t.track_artist, t.track_title, m.moj_title FROM tracks t JOIN mojocd m ON t.moj_id = m.moj_id WHERE upper(t.track_artist) LIKE '%$find%' OR upper(t.track_title) LIKE '%$find%' LIMIT $offset, $rowsperpage"; } else //Or we search for our single search term, in the field the user specified { $query = "SELECT track_artist, track_title FROM tracks WHERE upper($field) LIKE'%$find%' LIMIT $offset, $rowsperpage"; } $result = mysql_query($query); // while there are rows to be fetched... ?><table><?php while($list = mysql_fetch_assoc( $result )) { $counter++; $background_color = ( $counter % 2 == 0 ) ? ('white') : ('#E0E0E0'); echo '<tr><td style="background-color:'.$background_color.'">' ?> <b><?php echo $list['track_title']; ?></b> by <b><?php echo $list['track_artist']; ?></b> on <b><?php echo $list['moj_title']; ?></b></td></td> <br> <?php } ?> </table> <?php //This counts the number or results - and if there wasn't any it gives them a little message explaining that $anymatches=mysql_num_rows($result); if ($anymatches == 0) { echo "Sorry, but we can not find an entry to match your query<br><br>"; } //And we remind them what they searched for echo "<b>Searched For:</b> " .$find; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/169631-pagination-frustration/ Share on other sites More sharing options...
alexdemers Posted August 10, 2009 Share Posted August 10, 2009 COUNT(*) or COUNT(field_name) Not just COUNT by itself. Quote Link to comment https://forums.phpfreaks.com/topic/169631-pagination-frustration/#findComment-894933 Share on other sites More sharing options...
kingnutter Posted August 10, 2009 Author Share Posted August 10, 2009 Thanks. But I'm still confused about the placement. Should it be part of the sql query... $query = "COUNT (moj_id) WHERE SELECT t.moj_id, t.track_artist, t.track_title, m.moj_title FROM tracks t JOIN mojocd m ON t.moj_id = m.moj_id WHERE upper(t.track_artist) LIKE '%$find%' OR upper(t.track_title) LIKE '%$find%' LIMIT $offset, $rowsperpage"; ... which just doesn't look right or in this part of the code: $result = mysql_query($query); $r = mysql_fetch_row($result); $numrows = $r[0]; ...which doesn't seem right because is COUNT not an sql query only? Quote Link to comment https://forums.phpfreaks.com/topic/169631-pagination-frustration/#findComment-895020 Share on other sites More sharing options...
alexdemers Posted August 11, 2009 Share Posted August 11, 2009 MySQL :: MySQL 5.1 Reference Manual :: 3.3.4.8 Counting Rows SELECT COUNT(*) FROM `table` Quote Link to comment https://forums.phpfreaks.com/topic/169631-pagination-frustration/#findComment-895579 Share on other sites More sharing options...
kingnutter Posted August 13, 2009 Author Share Posted August 13, 2009 I sorted this out with mysql_num_rows in the end and it works quite well. For the first page of results anyhow. Then the links to further search pages lead to nothing. I think it's because I should be rePOSTing my search terms. Or perhaps it's something to do with SESSIONS. Can anyone give me some pointers before I go off down the wrong path? <form name="search" method="post" action="search_results.php"> Search for: <input type="text" name="find" /> in <Select NAME="field"> <Option VALUE="track_artist">Track Artist</option> <Option VALUE="track_title">Track Title</option> <Option VALUE="all">All</option> </Select> <input type="hidden" name="searching" value="yes" /> <input type="submit" name="search" value="Search" /> </form> <?php // includes include('conf.php'); include('functions.php'); $field = $_POST['field'] ; $find = $_POST['find'] ; $searching = $_POST['searching'] ; //This is only displayed if they have submitted the form if ($searching =="yes") { echo "<h2>Results</h2><p>"; //If they did not enter a search term we give them an error if ($find == "") { echo "<p>You forgot to enter a search term"; exit; } // Otherwise we connect to our Database // open database connection $connection = mysql_connect($host, $user, $pass) or die ('unable to connect!'); //select database mysql_select_db($db) or die ('unable to select database!'); $find = strtoupper($find); $find = strip_tags($find); $find = trim ($find); // Query database to find out how many rows in total result before pagination. N.B Could experiment with COUNT here. if ($field=='all') { $query = "SELECT t.moj_id, t.track_artist, t.track_title, m.moj_title FROM tracks t JOIN mojocd m ON t.moj_id = m.moj_id WHERE upper(t.track_artist) LIKE '%$find%' OR upper(t.track_title) LIKE '%$find%'"; $result = mysql_query($query, $connection); } else //Or we search for our single search term, in the field the user specified { $query = "SELECT track_artist, track_title FROM tracks WHERE upper($field) LIKE'%$find%'"; $result = mysql_query($query); } $result = mysql_query($query, $connection); $numrows = mysql_num_rows($result); // number of rows to show per page $rowsperpage = 10; // find out total pages $totalpages = ceil($numrows / $rowsperpage); // get the current page or set a default if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) { // cast var as int $currentpage = (int) $_GET['currentpage']; } else { // default page num $currentpage = 1; } // end if // if current page is greater than total pages... if ($currentpage > $totalpages) { // set current page to last page $currentpage = $totalpages; } // end if // if current page is less than first page... if ($currentpage < 1) { // set current page to first page $currentpage = 1; } // end if // the offset of the list, based on current page $offset = ($currentpage - 1) * $rowsperpage; if ($field=='all') { $query = "SELECT t.moj_id, t.track_artist, t.track_title, m.moj_title FROM tracks t JOIN mojocd m ON t.moj_id = m.moj_id WHERE upper(t.track_artist) LIKE '%$find%' OR upper(t.track_title) LIKE '%$find%' LIMIT $offset, $rowsperpage"; } else //Or we search for our single search term, in the field the user specified { $query = "SELECT track_artist, track_title FROM tracks WHERE upper($field) LIKE'%$find%' LIMIT $offset, $rowsperpage"; } $result = mysql_query($query); // while there are rows to be fetched... ?><table><?php while($list = mysql_fetch_assoc( $result )) { $counter++; $background_color = ( $counter % 2 == 0 ) ? ('white') : ('#E0E0E0'); echo '<tr><td style="background-color:'.$background_color.'">' ?> <b><?php echo $list['track_title']; ?></b> by <b><?php echo $list['track_artist']; ?></b> on <b><?php echo $list['moj_title']; ?></b></td></tr> <br> <?php } ?> </table> <?php //This counts the number or results - and if there wasn't any it gives them a little message explaining that $anymatches=mysql_num_rows($result); if ($anymatches == 0) { echo "Sorry, but we can not find an entry to match your query<br><br>"; } //And we remind them what they searched for echo "<b>Searched For:</b> " .$find; } /****** build the pagination links ******/ // range of num links to show $range = 3; // if not on page 1, don't show back links if ($currentpage > 1) { // show << link to go back to page 1 echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> "; // get previous page num $prevpage = $currentpage - 1; // show < link to go back to 1 page echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> "; } // end if // loop to show links to range of pages around current page for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) { // if it's a valid page number... if (($x > 0) && ($x <= $totalpages)) { // if we're on current page... if ($x == $currentpage) { // 'highlight' it but don't make a link echo " [<b>$x</b>] "; // if not current page... } else { // make it a link echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> "; } // end else } // end if } // end for // if not on last page, show forward and last page links if ($currentpage != $totalpages) { // get next page $nextpage = $currentpage + 1; // echo forward link for next page echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> "; // echo forward link for lastpage echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> "; } // end if /****** end build pagination links ******/ ?> Quote Link to comment https://forums.phpfreaks.com/topic/169631-pagination-frustration/#findComment-897560 Share on other sites More sharing options...
kingnutter Posted August 14, 2009 Author Share Posted August 14, 2009 It looks like both SESSIONs or re-POSTs could would. Which is the more scalable solution, say when search results reach into the hundreds? Quote Link to comment https://forums.phpfreaks.com/topic/169631-pagination-frustration/#findComment-898224 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.