phpwolf Posted February 25, 2010 Share Posted February 25, 2010 Hello I looked up some other pagination posts here on phpfreaks, and it might be the $_SESSION['mysqlquery'] = "SELECT COUNT(*) FROM mysqldbtable"; statement that is not constructed correctly, but im not sure. Once again, im trying to get the script to only make pagelinks for the rows in the 2nd mysql_query. Here is the search and pagination code again: <?php session_start(); $sess = session_id(); var_dump($sess); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <head> </head> <body> <p> <form method="post" action="http://localhost/search.php" name="searchengine"> Search: <input name="search" size="20" maxlength="80"> </input> <p> Display Rows On Page: <select name="rowsperpage"> <option value="10" selected="">10</option> <option value="20">20</option> <option value="30">30</option> <option value="40">40</option> <option value="50">50</option> <option value="100">100</option> </select> </p> <div id="selectsortorder"> Sort Order: <select name="sortorder"> <option value="ID ASC">A - Z</option> <option value="ID DESC">Z - A</option> </select> </div> <button TYPE="submit" NAME="submitform">SEARCH! </button> </form> <?php $conn = mysql_connect('localhost','root','') or trigger_error("SQL", E_USER_ERROR); $db = mysql_select_db('mysqldb',$conn) or trigger_error("SQL", E_USER_ERROR); if (isset($_POST['submitform'])) // If SEARCH! button is pressed, then... { unset($_SESSION); // unset the previous sessionvariables. $rowsperpage= @$_POST['rowsperpage'];// load new $rowsperpage only when SEARCH! Button is pressed. $sortorder = @$_POST['sortorder']; // load new $sortorderperpage only when SEARCH! Button is pressed. } $_SESSION['rowsperpage'] = $rowsperpage; $_SESSION['sortorder'] = $sortorder; if(!isset($_SESSION['searchvar'])) { $searchvar = $_POST['search']; } elseif(isset($_SESSION['searchvar'])) { $searchvar = $_SESSION['searchvar']; } $_SESSION['searchvar'] = $searchvar; var_dump($_SESSION['searchvar']); // find out how many rows are in the table $_SESSION['mysqlquery'] = "SELECT COUNT(*) FROM mysqldbtable"; $result = mysql_query($_SESSION['mysqlquery'], $conn) or trigger_error("SQL", E_USER_ERROR); $r = mysql_fetch_row($result); $numrows = $r[0]; // number of rows to show per page // find out total pages $totalpages = ceil($numrows / $_SESSION['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) * $_SESSION['rowsperpage']; $_SESSION['offset'] = $offset; $_SESSION['mysqlquery'] = "SELECT ID, CLIENTNAMES FROM mysqldbtable WHERE `CLIENTNAMES` REGEXP '$_SESSION[searchvar]' ORDER BY $_SESSION[sortorder] LIMIT $_SESSION[offset], $_SESSION[rowsperpage]"; $_SESSION['mysqlquery']; $_SESSION ['result111'] = mysql_query($_SESSION['mysqlquery'], $conn) or trigger_error("SQL", E_USER_ERROR); // while there are rows to be fetched... while ($list = mysql_fetch_assoc($_SESSION ['result111'])) { // echo data echo $list['CLIENTNAMES'] . "<br />"; } // end while // range of num links to show $range = 5; // 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 mysql_close(); ?> </body> Any help will be greatly appreciated Thank you Quote Link to comment Share on other sites More sharing options...
nilansanjaya Posted February 25, 2010 Share Posted February 25, 2010 sorry...ddn get your question.what you mean 2nd mysql query ? and btw its gr8 if u can post the codes inside php codes tag so its easy to read Quote Link to comment Share on other sites More sharing options...
phpwolf Posted February 25, 2010 Author Share Posted February 25, 2010 Hi nilansanjaya, 2nd mysql_query is this one: $_SESSION['mysqlquery'] = "SELECT ID, CLIENTNAMES FROM mysqldbtable WHERE `CLIENTNAMES` REGEXP '$_SESSION[searchvar]' ORDER BY $_SESSION[sortorder] LIMIT $_SESSION[offset], $_SESSION[rowsperpage]"; The problem is that the script makes pagelinks for all rows in the databasetable, and not as it should, which is only make links for the results in the $_SESSION['mysqlquery'] above. Quote Link to comment Share on other sites More sharing options...
Wolphie Posted February 25, 2010 Share Posted February 25, 2010 Please, when posting code remember to use the code BB Code. Quote Link to comment Share on other sites More sharing options...
phpwolf Posted February 25, 2010 Author Share Posted February 25, 2010 Hey guys here's the code again, color formatted. Im not getting anywhere with this script, I've been working on it for a few weeks already, but can't solve it. I've changed the line $_SESSION['mysqlquery'] = "SELECT COUNT(*) FROM mysqldbtable"; a few times, but this doesn't correct the problem, Is there any other way to make the script paginate correctly??? Any help is greatly appreciated Thank you <?php session_start(); $sess = session_id(); var_dump($sess); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <head> </head> <body> <p> <form method="post" action="http://localhost/search.php" name="searchengine"> Search: <input name="search" size="20" maxlength="80"> </input> <p> Display Rows On Page: <select name="rowsperpage"> <option value="10" selected="">10</option> <option value="20">20</option> <option value="30">30</option> <option value="40">40</option> <option value="50">50</option> <option value="100">100</option> </select> </p> <div id="selectsortorder"> Sort Order: <select name="sortorder"> <option value="ID ASC">A - Z</option> <option value="ID DESC">Z - A</option> </select> </div> <button TYPE="submit" NAME="submitform">SEARCH! </button> </form> <?php $conn = mysql_connect('localhost','root','') or trigger_error("SQL", E_USER_ERROR); $db = mysql_select_db('mysqldb',$conn) or trigger_error("SQL", E_USER_ERROR); if (isset($_POST['submitform'])) // If SEARCH! button is pressed, then... { unset($_SESSION); // unset the previous sessionvariables. $rowsperpage= @$_POST['rowsperpage'];// load new $rowsperpage only when SEARCH! Button is pressed. $sortorder = @$_POST['sortorder']; // load new $sortorderperpage only when SEARCH! Button is pressed. } $_SESSION['rowsperpage'] = $rowsperpage; $_SESSION['sortorder'] = $sortorder; if(!isset($_SESSION['searchvar'])) { $searchvar = $_POST['search']; } elseif(isset($_SESSION['searchvar'])) { $searchvar = $_SESSION['searchvar']; } $_SESSION['searchvar'] = $searchvar; var_dump($_SESSION['searchvar']); // find out how many rows are in the table $_SESSION['mysqlquery'] = "SELECT COUNT(*) FROM mysqldbtable"; $result = mysql_query($_SESSION['mysqlquery'], $conn) or trigger_error("SQL", E_USER_ERROR); $r = mysql_fetch_row($result); $numrows = $r[0]; // number of rows to show per page // find out total pages $totalpages = ceil($numrows / $_SESSION['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) * $_SESSION['rowsperpage']; $_SESSION['offset'] = $offset; $_SESSION['mysqlquery'] = "SELECT ID, CLIENTNAMES FROM mysqldbtable WHERE `CLIENTNAMES` REGEXP '$_SESSION[searchvar]' ORDER BY $_SESSION[sortorder] LIMIT $_SESSION[offset], $_SESSION[rowsperpage]"; $_SESSION['mysqlquery']; $_SESSION ['result111'] = mysql_query($_SESSION['mysqlquery'], $conn) or trigger_error("SQL", E_USER_ERROR); // while there are rows to be fetched... while ($list = mysql_fetch_assoc($_SESSION ['result111'])) { // echo data echo $list['CLIENTNAMES'] . "<br />"; } // end while // range of num links to show $range = 5; // 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 mysql_close(); ?> </body> Quote Link to comment Share on other sites More sharing options...
phpwolf Posted February 25, 2010 Author Share Posted February 25, 2010 Solved It! After viewing the MYSQL part of the forum, i've found this to work: Change line: $_SESSION['mysqlquery'] = "SELECT COUNT(*) FROM mysqldbtable"; to: $_SESSION['mysqlquery'] = "SELECT COUNT ( CLIENTNAMES ) FROM mysqldbtable WHERE `CLIENTNAMES` REGEXP '$_SESSION[searchvar]'"; This isn't really ideal coz im trying to implement a multi-field search script and this complicates multi search pagination. 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.