gnawz Posted October 1, 2008 Share Posted October 1, 2008 This is one of my functions.. I need to paginate to show a certain number of records per page then have options previous and/or next <? function allsales_Today() { //$sql = 'SELECT * FROM cstocksales WHERE SaleDate = current_date'; $sql = 'SELECT * FROM cstocksales WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= SaleDate'; $result = dbQuery($sql); if (dbNumRows($result) == 0) //if none { echo '<p><font color=#CC0000><h4>No sales for today were found in the records...</h4></font></p>'; echo 'Go back <a href="index.php">Here</a> for another search.'; } else { echo "<h5>Today's sales sales:<p></h5>"; echo '<table width="100%" border="1" bordercolor="#000000">'; echo'<tr>'; echo'<th scope="col" bgcolor="#66FFFF">Sale No</th>'; echo'<th scope="col" bgcolor="#66FFFF">Category</th>'; echo'<th scope="col" bgcolor="#66FFFF">Brand</th>'; echo'<th scope="col" bgcolor="#66FFFF">Quantity </th>'; echo'<th scope="col" bgcolor="#66FFFF">Paid By: </th>'; echo'<th scope="col" bgcolor="#66FFFF">Amount </th>'; echo'<th scope="col" bgcolor="#66FFFF">Date of Sale </th>'; echo'</tr>'; while($row = dbFetchArray($result)) // while($row = mysql_fetch_array($result)) { echo'<tr>'; echo'<td align="center">'; $SaleID = $row["SaleID"]; echo $SaleID. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Category = $row["Category"]; echo $Category. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Brand = $row["Brand"]; echo $Brand. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Quantity = $row["Quantity"]; echo $Quantity. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $PaidBy = $row["PaidBy"]; echo $PaidBy. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Cost = $row["Cost"]; echo $Cost. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $SaleDate = $row["SaleDate"]; echo $SaleDate. "<br>"; echo'</td align="center">'; echo '</tr>'; } echo'</table>'; echo ' <a href="index.php">Go back</a> to sales panel.'; echo "<br>"; echo "<strong>Total Sales:</strong>"; echo "<hr>"; $sql_total = 'SELECT PaidBy, SUM(Cost) FROM cstocksales WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= SaleDate GROUP BY PaidBy'; $result = dbQuery($sql_total); while($row = dbFetchArray($result)) { echo "Total sales by ". $row['PaidBy']. "<font color=#CC0000> = Kshs". $row['SUM(Cost)']; echo "</font>"; echo " "; echo"|"; echo " "; } $sql_total_final = 'SELECT SUM(Cost) FROM cstocksales WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= SaleDate'; $result = dbQuery($sql_total_final); while($row = dbFetchArray($result)) { echo "<strong>All sales = <font color=#CC0000> Kshs" .$row['SUM(Cost)'] ; echo "</font></strong>" ; } } } ?> Quote Link to comment Share on other sites More sharing options...
trq Posted October 1, 2008 Share Posted October 1, 2008 Have you tried googling a tutorial? There are literally thousands out there. Quote Link to comment Share on other sites More sharing options...
dropfaith Posted October 1, 2008 Share Posted October 1, 2008 http://www.phpfreaks.com/tutorial/basic-pagination Quote Link to comment Share on other sites More sharing options...
Zhadus Posted October 1, 2008 Share Posted October 1, 2008 I agree that a tutorial would be the best result. If you're looking just for the process of going about it, it's fairly simple. You want to store the number of records you want to show in a variable that you can pass with the URL. If the amount of records is 50 for instance, and you want 10 per page, you could have the page show 10 records. The starting record would default to record 0 and would display up to record 9 with a next button. The next button would pass the variable "record" with a value of 1+ your last current record of 9. And if the starting record is greater than 0, create a previous button with a variable "record" with a value of your starting record, -10. <?php if (isset($_GET['record'])) { $start = $_GET['record']; } else { $start = 0; } //SQL statement to draw records ordered by ID, starting with ID = $start //Number of TOTAL records stored in variable $total for ($x = 0; $x < ($start + 10); $x++) { // Print Record } if ($start != 0) { // Print previous button with a link containing ?record=($start-10) } if ($total > ($start + 9)) { // Print next button with a link containing ?record=($start+10) } ?> Hope that makes sense. Quote Link to comment Share on other sites More sharing options...
gnawz Posted October 1, 2008 Author Share Posted October 1, 2008 Thanks alot people.. I have a pagination function I am using in a file called functions.php as below <? //Pagination functions function getPagingQuery($sql, $itemPerPage = 10) { if (isset($_GET['page']) && (int)$_GET['page'] > 0) { $page = (int)$_GET['page']; } else { $page = 1; } // start fetching from this row number $offset = ($page - 1) * $itemPerPage; return $sql . " LIMIT $offset, $itemPerPage"; } /* Get the links to navigate between one result page to another. */ function getPagingLink($sql, $itemPerPage = 10, $strGet = '') { $result = dbQuery($sql); $pagingLink = ''; $totalResults = dbNumRows($result); $totalPages = ceil($totalResults / $itemPerPage); // how many link pages to show $numLinks = 10; // create the paging links only if theres more than one page of results if ($totalPages > 1) { $self = 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'] ; if (isset($_GET['page']) && (int)$_GET['page'] > 0) { $pageNumber = (int)$_GET['page']; } else { $pageNumber = 1; } // print 'previous' link only if its not // on page one if ($pageNumber > 1) { $page = $pageNumber - 1; if ($page > 1) { $prev = " <a href=\"$self?page=$page&$strGet/\">[Prev]</a> "; } else { $prev = " <a href=\"$self?$strGet\">[Prev]</a> "; } $first = " <a href=\"$self?$strGet\">[First]</a> "; } else { $prev = ''; // on page one, don't show 'previous' link $first = ''; // nor 'first page' link } // print 'next' link only if its not // on the last page if ($pageNumber < $totalPages) { $page = $pageNumber + 1; $next = " <a href=\"$self?page=$page&$strGet\">[Next]</a> "; $last = " <a href=\"$self?page=$totalPages&$strGet\">[Last]</a> "; } else { $next = ''; // if on the last page, don't show 'next' link $last = ''; // nor 'last page' link } $start = $pageNumber - ($pageNumber % $numLinks) + 1; $end = $start + $numLinks - 1; $end = min($totalPages, $end); $pagingLink = array(); for($page = $start; $page <= $end; $page++) { if ($page == $pageNumber) { $pagingLink[] = " $page "; // no need to create a link to current page } else { if ($page == 1) { $pagingLink[] = " <a href=\"$self?$strGet\">$page</a> "; } else { $pagingLink[] = " <a href=\"$self?page=$page&$strGet\">$page</a> "; } } } $pagingLink = implode(' | ', $pagingLink); // return the page navigation link $pagingLink = $first . $prev . $pagingLink . $next . $last; } return $pagingLink; } ?> I include that functions.php in every page because it contains all the functions I need However, when I add the pagination to my DB functions containing SQLs, it doesn't work. Code below: <? function allsales_Today() { $rowsPerPage = 5; //$sql = 'SELECT * FROM cstocksales WHERE SaleDate = current_date'; $sql = 'SELECT * FROM cstocksales WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= SaleDate'; //$result = dbQuery($sql); $result = dbQuery(getPagingQuery($sql, $rowsPerPage)); $pagingLink = getPagingLink($sql, $rowsPerPage); if (dbNumRows($result) == 0) //if none { echo '<p><font color=#CC0000><h4>No sales for today were found in the records...</h4></font></p>'; echo 'Go back <a href="index.php">Here</a> for another search.'; } else { echo "<h5>Today's sales sales:<p></h5>"; echo '<table width="100%" border="1" bordercolor="#000000">'; echo'<tr>'; echo'<th scope="col" bgcolor="#66FFFF">Sale No</th>'; echo'<th scope="col" bgcolor="#66FFFF">Category</th>'; echo'<th scope="col" bgcolor="#66FFFF">Brand</th>'; echo'<th scope="col" bgcolor="#66FFFF">Quantity </th>'; echo'<th scope="col" bgcolor="#66FFFF">Paid By: </th>'; echo'<th scope="col" bgcolor="#66FFFF">Amount </th>'; echo'<th scope="col" bgcolor="#66FFFF">Date of Sale </th>'; echo'</tr>'; while($row = dbFetchArray($result)) // while($row = mysql_fetch_array($result)) { echo'<tr>'; echo'<td align="center">'; $SaleID = $row["SaleID"]; echo $SaleID. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Category = $row["Category"]; echo $Category. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Brand = $row["Brand"]; echo $Brand. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Quantity = $row["Quantity"]; echo $Quantity. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $PaidBy = $row["PaidBy"]; echo $PaidBy. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Cost = $row["Cost"]; echo $Cost. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $SaleDate = $row["SaleDate"]; echo $SaleDate. "<br>"; echo'</td align="center">'; echo '</tr>'; } echo'</table>'; //paginating link echo $pagingLink; echo "<br>"; echo "<strong>Sales figures:</strong>"; echo "<hr>"; $sql_total = 'SELECT PaidBy, SUM(Cost) FROM cstocksales WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= SaleDate GROUP BY PaidBy'; $result = dbQuery($sql_total); while($row = dbFetchArray($result)) { echo "Total sales by ". $row['PaidBy']. "<font color=#CC0000> = Kshs <strong>". $row['SUM(Cost)']; echo "</font></strong>"; echo " "; echo"|"; echo " "; } $sql_total_final = 'SELECT SUM(Cost) FROM cstocksales WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= SaleDate'; $result = dbQuery($sql_total_final); while($row = dbFetchArray($result)) { echo "<strong>All sales = <font color=#CC0000> Kshs " .$row['SUM(Cost)']; echo "</font></strong>" ; } } echo "<br>"; echo '<h4><a href="index.php">Back</a> to sales panel.'; } ?> The first page is displayed well. When I click next, the page is blank and the URL is http://localhost/cstock/admin/viewsales/viewsales.php?page=2& The URL for the search should be http://localhost/cstock/admin/viewsales/viewsales.php?action=allsalestoday Followed by the pagination as a user clicks next or a value in the pagination link viewsales.php is the file containing sales functions It only works if I put each function in its own page. My sales functions are many (33 functions) and so they are all in one file called view sales.php in a case statement. How do I make it work if I have a file that contains all my sales functions and I want to use the pagination in each function? I hope I provided clear information... Quote Link to comment Share on other sites More sharing options...
Zhadus Posted October 1, 2008 Share Posted October 1, 2008 I think I understood what you're saying. If you're using a lot of functions, you need to remember that any pertinent data that the functions specifically will use, must be passed into the function as well. If the starting record number is important, the function must be setup as: function funcName($startRecordNum) { // etc. } Quote Link to comment Share on other sites More sharing options...
gnawz Posted October 1, 2008 Author Share Posted October 1, 2008 Hi, Please give a little more light... Quote Link to comment Share on other sites More sharing options...
Zhadus Posted October 1, 2008 Share Posted October 1, 2008 Well one thing, for your link, I saw you put this code: $prev = " <a href=\"$self?page=$page&$strGet/\">[Prev]</a> "; If you want to carry the action, use: $prev = " <a href=\"$self?action=allsalestoday&page=$page&$strGet/\">[Prev]</a> "; Quote Link to comment Share on other sites More sharing options...
gnawz Posted October 1, 2008 Author Share Posted October 1, 2008 How am I gonna do that? I should repeat the pagination function for each search function? I have the pagination function in my functions files which I use anywhere I need it. Any idea doing this without having to repeat the function many times (changing variable names of course)? Quote Link to comment Share on other sites More sharing options...
Zhadus Posted October 1, 2008 Share Posted October 1, 2008 I saw the link it is in the allsalestoday() function, would that have a different action aside from allsalestoday? Quote Link to comment Share on other sites More sharing options...
gnawz Posted October 1, 2008 Author Share Posted October 1, 2008 Yes... It could be action for a month's sales, weeks sales or more.. Remember, I have a switch statement that iterates all my 32 sales functions. It could also be a page instead of a function as I have used it is some other way Also remember, the pagination function is one central function used in any page where it is needed. If I knew a way of telling the pagination that it could be used in a page with one function or a page with multiple functions may be in a switch statement, I would. What approach do you think I can take? I really appreciate your help and I hope I can arrive at a solution. Quote Link to comment Share on other sites More sharing options...
Zhadus Posted October 1, 2008 Share Posted October 1, 2008 I apologize for all the long delays, trying to help you out while at work here I'm still having some difficulty with what you're incorporating this system into. It seems to me like you're not grasping the full concept of what functions are capable of and what is required of them. In order to maintain the same action, you'll have to pass it into the address bar. If it is possible it will have different actions, you'll need to pass the current action INTO the function. Therefore instead of what I had stated with: $prev = " <a href=\"$self?action=allsalestoday&page=$page&$strGet/\">[Prev]</a> "; You would want the function to be like this: $action = $_GET['action']; function allsales_Today($action) { // etc. and the link would be: $prev = " <a href=\"$self?action=$action&page=$page&$strGet/\">[Prev]</a> "; I hope my understanding was clear enough. Quote Link to comment Share on other sites More sharing options...
gnawz Posted October 2, 2008 Author Share Posted October 2, 2008 Thanks for this great help that made sense However still not yet there. You see, my pagination function is in a file I call at the top of every page (functions.php) Pagination functions <? //Pagination functions function getPagingQuery($sql, $itemPerPage = 10) { if (isset($_GET['page']) && (int)$_GET['page'] > 0) { $page = (int)$_GET['page']; } else { $page = 1; } // start fetching from this row number $offset = ($page - 1) * $itemPerPage; return $sql . " LIMIT $offset, $itemPerPage"; } /* Get the links to navigate between one result page to another. */ function getPagingLink($sql, $itemPerPage = 10, $strGet = '') { $result = dbQuery($sql); $pagingLink = ''; $totalResults = dbNumRows($result); $totalPages = ceil($totalResults / $itemPerPage); // how many link pages to show $numLinks = 10; // create the paging links only if theres more than one page of results if ($totalPages > 1) { $self = 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'] ; if (isset($_GET['page']) && (int)$_GET['page'] > 0) { $pageNumber = (int)$_GET['page']; } else { $pageNumber = 1; } // print 'previous' link only if its not // on page one if ($pageNumber > 1) { $page = $pageNumber - 1; if ($page > 1) { $prev = " <a href=\"$self?page=$page&$strGet/\">[Prev]</a> "; || $prev = " <a href=\"$self?action=$action&$strGet/\">[Prev]</a> "; } else { $prev = " <a href=\"$self?$strGet\">[Prev]</a> "; } $first = " <a href=\"$self?$strGet\">[First]</a> "; } else { $prev = ''; // on page one, don't show 'previous' link $first = ''; // nor 'first page' link } // print 'next' link only if its not // on the last page if ($pageNumber < $totalPages) { $page = $pageNumber + 1; $next = " <a href=\"$self?page=$page&$strGet\">[Next]</a> "; || " <a href=\"$self?action=$action&$strGet/\">[Prev]</a> "; $last = " <a href=\"$self?page=$totalPages&$strGet\">[Last]</a> "; || " <a href=\"$self?action=$action&$strGet/\">[Prev]</a> "; } else { $next = ''; // if on the last page, don't show 'next' link $last = ''; // nor 'last page' link } $start = $pageNumber - ($pageNumber % $numLinks) + 1; $end = $start + $numLinks - 1; $end = min($totalPages, $end); $pagingLink = array(); for($page = $start; $page <= $end; $page++) { if ($page == $pageNumber) { $pagingLink[] = " $page "; // no need to create a link to current page } else { if ($page == 1) { $pagingLink[] = " <a href=\"$self?$strGet\">$page</a> "; } else { $pagingLink[] = " <a href=\"$self?page=$page&$strGet\">$page</a> "; } } } $pagingLink = implode(' | ', $pagingLink); // return the page navigation link $pagingLink = $first . $prev . $pagingLink . $next . $last; } return $pagingLink; } ?> Like in this page (list.php) <?php if (!defined('WEB_ROOT')) { exit; } $rowsPerPage = 5; $_SESSION['login_return_url'] = $_SERVER['REQUEST_URI']; checkUser(); $sql = "SELECT * FROM fragrancestock"; $result = dbQuery(getPagingQuery($sql, $rowsPerPage)); $pagingLink = getPagingLink($sql, $rowsPerPage); $errorMessage = (isset($_GET['cstock_error']) && $_GET['cstock_error'] != '') ? $_GET['cstock_error'] : ' '; ?> <form action="index.php?view=add" method="post" name="frmList" id="frmList"> <table width="100%" border="0" align="center"> <tr class="title_text"> <td width="100">Category</td> <td width="400">Brand</td> <td width="100" align="center">Stock</td> <td width="100">Date Added</td> <td width="50">Details</td> <td width="50">Add</td> <td width="50">Delete</td> </tr> <?php if (dbNumRows($result) > 0) { $i = 0; while($row = dbFetchAssoc($result)) { extract($row); if ($i%2) { $class = 'row1'; } else { $class = 'row2'; } $i += 1; ?> <tr class="<?php echo $class; ?>"> <td width="100"><?php echo $Category; ?></td> <td width="400"><?php echo $Brand; ?></td> <td width="100" class="inner_border"><?php echo $Quantity; ?></td> <td width="100"><?php echo $DateAddedStock; ?></td> <td width="50"><a href="<?php echo $_SERVER['PHP_SELF']; ?>?view=detail&StockID=<?php echo $StockID; ?>">View</a></td> <td width="50"><a href="javascript:modifyStock(<?php echo $StockID; ?>);"><strong>Add </strong> </a></td> <td width="50"><a href="javascript:deleteStock(<?php echo $StockID; ?>);">Delete</a></td> </tr> <?php } // end while ?> <tr> <td colspan="7" align="center"> <?php echo $pagingLink; ?> </td> </tr> <?php } else { ?> <tr> <td colspan="7" align="center">No Stock Items Yet</td> </tr> <?php } ?> <tr> <td colspan="7"> </td> </tr> <tr> <td colspan="7" align="right"> <input name="btnAddStock" type="submit" value="New Entry" class="button_image"> </tr> </table> </form> <label class="title_text">View Stock:</label> <hr> <table width="100%" border="0"> <tr> <td><form action="index.php?view=showbybrand" method="post" name="frmByBrand"> <table width="100%" border="0"> <tr> <td width="200">By Brand:</td> <td width="200"><select name="sltBrand"> <option value="0">Select Brand</option> <? $sql = "SELECT DISTINCT Brand FROM cstockitems ORDER BY Brand ASC"; $result = dbQuery($sql); if(dbNumRows($result)) { while($row = dbFetchAssoc($result)) { echo "<option>$row[brand]</option>"; } } else { echo "<option>No Brands Present</option>"; } ?> </select></td> <td width="200"><input type="submit" name="Submit" value="Show" class="button_image" onClick="return CheckShowBrandByBrand();"> </td> </tr> </table> </form></td> </tr> <tr> <td><form name="frmByCategory" method="post" action="showbycategory.php"> <table width="100%" border="0"> <tr> <td width="200">By Category:</td> <td width="200"><select name="sltCategory"> <option value="0">Select Category</option> <? $sql = "SELECT DISTINCT Category FROM cstockitems ORDER BY Category ASC"; $result = dbQuery($sql); if(dbNumRows($result)) { while($row = dbFetchAssoc($result)) { echo "<option>$row[Category]</option>"; } } else { echo "<option>No Categories Present</option>"; } ?> </select></td> <td width="200"><input type="submit" name="Submit2" value="Show" class="button_image" onClick="return CheckShowBrandByCategory();"></td> </tr> </table> </form></td> </tr> <tr> <td><form name="frmSearchBrand" method="post" action="showbysearch.php"> <table width="100%" border="0"> <tr> <td width="200">Search:</td> <td width="200"><input type="text" name="txtSearchBrand" size="40"></td> <td width="200"><input type="submit" name="Submit3" value="Search" class="button_image" onClick="return CheckShowBrandBySearch();"></td> </tr> </table> </form></td> </tr> </table> <label class="title_text">Print product list for re-stocking:</label> <hr> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td><a href="exportoutofstockfile.php">Out of stock:</a></td> <td><a href="exportlowstockfile.php">Low stock:</a></td> <td><a href="exportwarningstockfile.php">Stock running low:</a></td> <td><a href="printstocksheet.php">Print stock sheet</a></td> </tr> </table> <script> function CheckShowBrandByBrand() { form = window.document.frmByBrand; if (form.sltBrand.selectedIndex == 0) { alert('You have not selected a brand to view details!'); return false; } else { return true; } } function CheckShowBrandByCategory() { form = window.document.frmByCategory; if (form.sltCategory.selectedIndex == 0) { alert('You have not selected a Category to view details!'); return false; } else { return true; } } function CheckShowBrandBySearch() { form = window.document.frmSearchBrand; if (form.txtSearchBrand.value == "") { alert('You have not entered a brand name to search!'); return false; } else { return true; } } </script> But you can see that page has one function so the pagination works well using the variable ($page) My problem is how to make it work in a page containing multiple functions AND it works in every function Where should I change without having to make a pagination function for every POST function My page of functions where I want to use it <? <? require_once '../../functions.php'; $_SESSION['login_return_url'] = $_SERVER['REQUEST_URI']; checkUser(); $action = isset($_GET['action']) ? $_GET['action'] : ''; //$page = $_GET['action']; switch ($action) { case 'jan' : sales_January(); break; case 'feb' : sales_February(); break; case 'mar' : sales_March(); break; case 'april' : sales_April(); break; case 'may' : sales_May(); break; case 'june' : sales_June(); break; case 'july' : sales_July(); break; case 'aug' : sales_August(); break; case 'sept' : sales_September(); break; case 'oct' : sales_October(); break; case 'nov' : sales_November(); break; case 'dec' : sales_December(); break; case 'allsalestoday' : allsales_Today(); break; case 'allsalessevendays' : allsales_SevenDays(); break; case 'salesbycategory' : salesbyCategory(); break; case 'salesbybrand' : salesbyBrand(); break; default : } function allsales_Today($action) { $rowsPerPage = 2; //$sql = 'SELECT * FROM cstocksales WHERE SaleDate = current_date'; $sql = 'SELECT * FROM cstocksales WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= SaleDate'; //$result = dbQuery($sql); $result = dbQuery(getPagingQuery($sql, $rowsPerPage)); $pagingLink = getPagingLink($sql, $rowsPerPage); if (dbNumRows($result) == 0) //if none { echo '<p><font color=#CC0000><h4>No sales for today were found in the records...</h4></font></p>'; echo 'Go back <a href="index.php">Here</a> for another search.'; } else { echo "<h5>Today's sales sales:<p></h5>"; echo '<table width="100%" border="1" bordercolor="#000000">'; echo'<tr>'; echo'<th scope="col" bgcolor="#66FFFF">Sale No</th>'; echo'<th scope="col" bgcolor="#66FFFF">Category</th>'; echo'<th scope="col" bgcolor="#66FFFF">Brand</th>'; echo'<th scope="col" bgcolor="#66FFFF">Quantity </th>'; echo'<th scope="col" bgcolor="#66FFFF">Paid By: </th>'; echo'<th scope="col" bgcolor="#66FFFF">Amount </th>'; echo'<th scope="col" bgcolor="#66FFFF">Date of Sale </th>'; echo'</tr>'; while($row = dbFetchArray($result)) // while($row = mysql_fetch_array($result)) { echo'<tr>'; echo'<td align="center">'; $SaleID = $row["SaleID"]; echo $SaleID. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Category = $row["Category"]; echo $Category. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Brand = $row["Brand"]; echo $Brand. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Quantity = $row["Quantity"]; echo $Quantity. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $PaidBy = $row["PaidBy"]; echo $PaidBy. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Cost = $row["Cost"]; echo $Cost. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $SaleDate = $row["SaleDate"]; echo $SaleDate. "<br>"; echo'</td align="center">'; echo '</tr>'; } echo'</table>'; //paginating link echo $pagingLink; echo "<br>"; echo "<strong>Sales figures:</strong>"; echo "<hr>"; $sql_total = 'SELECT PaidBy, SUM(Cost) FROM cstocksales WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= SaleDate GROUP BY PaidBy'; $result = dbQuery($sql_total); while($row = dbFetchArray($result)) { echo "Total sales by ". $row['PaidBy']. "<font color=#CC0000> = Kshs <strong>". $row['SUM(Cost)']; echo "</font></strong>"; echo " "; echo"|"; echo " "; } $sql_total_final = 'SELECT SUM(Cost) FROM cstocksales WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= SaleDate'; $result = dbQuery($sql_total_final); while($row = dbFetchArray($result)) { echo "<strong>All sales = <font color=#CC0000> Kshs " .$row['SUM(Cost)']; echo "</font></strong>" ; } } echo "<br>"; echo '<h4><a href="index.php">Back</a> to sales panel.'; } function allsales_SevenDays() { $sql = 'SELECT * FROM cstocksales WHERE DATE_SUB(CURDATE(),INTERVAL 8 DAY) <= SaleDate'; $result = dbQuery($sql); ///$result = dbQuery(getPagingQuery($sql, $rowsPerPage)); //$pagingLink = getPagingLink($sql, $rowsPerPage); if (dbNumRows($result) == 0) //if none { echo '<p><font color=#CC0000><h4>No sales for the past seven days were found in the records...</h4></font></p>'; echo 'Go back <a href="index.php">Here</a> for another search.'; } else { echo '<h5>Sales in the past seven days:<p></h5>'; echo '<table width="100%" border="1" bordercolor="#000000">'; echo'<tr>'; echo'<th scope="col" bgcolor="#66FFFF">Sale No</th>'; echo'<th scope="col" bgcolor="#66FFFF">Category</th>'; echo'<th scope="col" bgcolor="#66FFFF">Brand</th>'; echo'<th scope="col" bgcolor="#66FFFF">Quantity </th>'; echo'<th scope="col" bgcolor="#66FFFF">Paid By: </th>'; echo'<th scope="col" bgcolor="#66FFFF">Amount </th>'; echo'<th scope="col" bgcolor="#66FFFF">Date of Sale </th>'; echo'</tr>'; while($row = dbFetchArray($result)) { echo'<tr>'; echo'<td align="center">'; $SaleID = $row["SaleID"]; echo $SaleID. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Category = $row["Category"]; echo $Category. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Brand = $row["Brand"]; echo $Brand. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Quantity = $row["Quantity"]; echo $Quantity. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $PaidBy = $row["PaidBy"]; echo $PaidBy. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Cost = $row["Cost"]; echo $Cost. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $SaleDate = $row["SaleDate"]; echo $SaleDate. "<br>"; echo'</td align="center">'; echo '</tr>'; } echo'</table>'; // echo 'Go back <a href="index.php">Here</a> for another search.'; } } ///////////////////////////// function salesbyCategory() { $byCategory = $_POST['sltCategory']; $sql = 'SELECT * FROM cstocksales WHERE Category = "'.$byCategory.'"'; $result = dbQuery($sql); ///$result = dbQuery(getPagingQuery($sql, $rowsPerPage)); //$pagingLink = getPagingLink($sql, $rowsPerPage); if (dbNumRows($result) == 0) //if none { echo '<p><font color=#CC0000><h4>No sales for the Category "'.$_POST['sltCategory'].'" were found in the records...</h4></font></p>'; echo 'Go back <a href="index.php">Here</a> for another search.'; } else { echo '<h5>"'.$_POST['sltCategory'].'" sales:<p></h5>'; echo '<table width="100%" border="1" bordercolor="#000000">'; echo'<tr>'; echo'<th scope="col" bgcolor="#66FFFF">Sale No</th>'; echo'<th scope="col" bgcolor="#66FFFF">Category</th>'; echo'<th scope="col" bgcolor="#66FFFF">Brand</th>'; echo'<th scope="col" bgcolor="#66FFFF">Quantity </th>'; echo'<th scope="col" bgcolor="#66FFFF">Paid By: </th>'; echo'<th scope="col" bgcolor="#66FFFF">Amount </th>'; echo'<th scope="col" bgcolor="#66FFFF">Date of Sale </th>'; echo'</tr>'; while($row = dbFetchArray($result)) { echo'<tr>'; echo'<td align="center">'; $SaleID = $row["SaleID"]; echo $SaleID. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Category = $row["Category"]; echo $Category. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Brand = $row["Brand"]; echo $Brand. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Quantity = $row["Quantity"]; echo $Quantity. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $PaidBy = $row["PaidBy"]; echo $PaidBy. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Cost = $row["Cost"]; echo $Cost. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $SaleDate = $row["SaleDate"]; echo $SaleDate. "<br>"; echo'</td align="center">'; echo '</tr>'; } echo'</table>'; echo 'Go back <a href="index.php">Here</a> for another search.'; } } function salesbyBrand() { $byBrand = $_POST['sltBrand']; $sql = 'SELECT * FROM cstocksales WHERE Brand = "'.$byBrand.'"'; $result = dbQuery($sql); ///$result = dbQuery(getPagingQuery($sql, $rowsPerPage)); //$pagingLink = getPagingLink($sql, $rowsPerPage); if (dbNumRows($result) == 0) //if none { echo '<p><font color=#CC0000><h4>No sales for the brand "'.$_POST['sltBrand'].'" were found in the records...</h4></font></p>'; echo 'Go back <a href="index.php">Here</a> for another search.'; } else { echo '<h5>"'.$_POST['sltBrand'].'" slaes:<p></h5>'; echo '<table width="100%" border="1" bordercolor="#000000">'; echo'<tr>'; echo'<th scope="col" bgcolor="#66FFFF">Sale No</th>'; echo'<th scope="col" bgcolor="#66FFFF">Category</th>'; echo'<th scope="col" bgcolor="#66FFFF">Brand</th>'; echo'<th scope="col" bgcolor="#66FFFF">Quantity </th>'; echo'<th scope="col" bgcolor="#66FFFF">Paid By: </th>'; echo'<th scope="col" bgcolor="#66FFFF">Amount </th>'; echo'<th scope="col" bgcolor="#66FFFF">Date of Sale </th>'; echo'</tr>'; while($row = dbFetchArray($result)) { echo'<tr>'; echo'<td align="center">'; $SaleID = $row["SaleID"]; echo $SaleID. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Category = $row["Category"]; echo $Category. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Brand = $row["Brand"]; echo $Brand. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Quantity = $row["Quantity"]; echo $Quantity. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $PaidBy = $row["PaidBy"]; echo $PaidBy. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Cost = $row["Cost"]; echo $Cost. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $SaleDate = $row["SaleDate"]; echo $SaleDate. "<br>"; echo'</td align="center">'; echo '</tr>'; } echo'</table>'; echo 'Go back <a href="index.php">Here</a> for another search.'; } } ?> ?> I hope it is clear now Quote Link to comment Share on other sites More sharing options...
gnawz Posted October 7, 2008 Author Share Posted October 7, 2008 Any one helping on this? I'm really stuck! Quote Link to comment Share on other sites More sharing options...
R0bb0b Posted October 7, 2008 Share Posted October 7, 2008 I'll break down the things you need to paginate. [*]first you will need to define a variable to hold the rows per page, say 20 [*]then you will define a variable to hold the current page, if $_GET['page'] is not defined then this will be 1, otherwise it will be $_GET['page'] [*]at the end of your query you will have "limit ".$pagenumber.", ".$rowsperpage [*]you then need a total count so you will run the same query without the limit clause at the end and in your column list you will only have something like "select count(PrimaryKey) as total..." [*]take that total and divide it by $rowsperpage which will give you the total amount of pages [*]take this calculation of total pages and $pagenumber and set up your links, "next" will have a url variable of "?page=" . $currentpage+1 and previous will have ?page=" . $currentpage-1. You can also set up page numbers like 5 previous pages and the next 5 pages This is a just a basic explanation, you can certainly apply more imagination. Quote Link to comment Share on other sites More sharing options...
gnawz Posted October 7, 2008 Author Share Posted October 7, 2008 My pagination as in my code snippets works... What I need is how to make it work in a file that contains multiple functions (Where it is used in each of the functions.. Currently it only works in a file with one function; thereby recognising the page. I want my pagnation to be recognising a function or SQL statement. Quote Link to comment Share on other sites More sharing options...
gnawz Posted October 9, 2008 Author Share Posted October 9, 2008 I have compressed my post so I may get some help The following is my pagination function which I use in pages with one function and it works well. All I add is this...in the following example <? $byCategory = ''; if (isset($_POST['sltCategory'])) $byCategory = $_POST['sltCategory']; elseif (isset($_GET['search'])) $byCategory = $_GET['search']; $sql = "SELECT * FROM cstocksales WHERE Category = '$byCategory'"; $rowsPerPage = 10; $result = dbQuery($sql); $result = dbQuery(getPagingQuery($sql, $rowsPerPage)); $pagingLink = getPagingLink($sql, $rowsPerPage, "search=$byCategory"); ?> <? function getPagingQuery($sql, $itemPerPage = 10) { if (isset($_GET['page']) && (int)$_GET['page'] > 0) { $page = (int)$_GET['page']; } else { $page = 1; } // start fetching from this row number $offset = ($page - 1) * $itemPerPage; return $sql . " LIMIT $offset, $itemPerPage"; } /* Get the links to navigate between one result page to another. */ function getPagingLink($sql, $itemPerPage = 10, $strGet = '') { $result = dbQuery($sql); $pagingLink = ''; $totalResults = dbNumRows($result); $totalPages = ceil($totalResults / $itemPerPage); // how many link pages to show $numLinks = 10; // create the paging links only if theres more than one page of results if ($totalPages > 1) { $self = 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'] ; if (isset($_GET['page']) && (int)$_GET['page'] > 0) { $pageNumber = (int)$_GET['page']; } else { $pageNumber = 1; } // print 'previous' link only if its not // on page one if ($pageNumber > 1) { $page = $pageNumber - 1; if ($page > 1) { $prev = " <a href=\"$self?page=$page&$strGet/\">[Prev]</a> "; } else { $prev = " <a href=\"$self?$strGet\">[Prev]</a> "; } $first = " <a href=\"$self?$strGet\">[First]</a> "; } else { $prev = ''; // on page one, don't show 'previous' link $first = ''; // nor 'first page' link } // print 'next' link only if its not // on the last page if ($pageNumber < $totalPages) { $page = $pageNumber + 1; $next = " <a href=\"$self?page=$page&$strGet\">[Next]</a> "; $last = " <a href=\"$self?page=$totalPages&$strGet\">[Last]</a> "; } else { $next = ''; // if on the last page, don't show 'next' link $last = ''; // nor 'last page' link } $start = $pageNumber - ($pageNumber % $numLinks) + 1; $end = $start + $numLinks - 1; $end = min($totalPages, $end); $pagingLink = array(); for($page = $start; $page <= $end; $page++) { if ($page == $pageNumber) { $pagingLink[] = " $page "; // no need to create a link to current page } else { if ($page == 1) { $pagingLink[] = " <a href=\"$self?$strGet\">$page</a> "; } else { $pagingLink[] = " <a href=\"$self?page=$page&$strGet\">$page</a> "; } } } $pagingLink = implode(' | ', $pagingLink); // return the page navigation link $pagingLink = $first . $prev . $pagingLink . $next . $last; } return $pagingLink; } ?> I want to use it in each and every function of the following page. If I add the above sample code to each function, It only shows the first page. When I click next to see subsequent page, it goes blank and does not recognise the case statement or function Below is the page in which I want to employ the pagination in every function <? require_once '../../functions.php'; $_SESSION['login_return_url'] = $_SERVER['REQUEST_URI']; checkUser(); $action = isset($_GET['action']) ? $_GET['action'] : ''; switch ($action) { case 'month' : sales_Month(); break; case 'days' : allsales_Day(); case 'salesbybrand' : salesbyBrand(); break; default : } function allsales_Day() { $day = ''; if (isset($_POST['sltallSales'])) $day = $_POST['sltallSales']; elseif (isset($_GET['search'])) $day = $_GET['search']; $sql = "SELECT * FROM cstocksales WHERE DATE_SUB(CURDATE(),INTERVAL '$day' DAY) <= SaleDate"; $result = dbQuery($sql, "search=$day"); if (dbNumRows($result) == 0) //if none { echo '<p><font color=#CC0000><h4>No sales were found in the records...</h4></font></p>'; echo '<h4><a href="index.php">Back</a> to sales panel.'; } else { //echo "<h5>Today's sales:<p></h5>"; echo '<table width="100%" border="1" bordercolor="#000000">'; echo'<tr>'; echo'<th scope="col" bgcolor="#66FFFF">Sale No</th>'; echo'<th scope="col" bgcolor="#66FFFF">Category</th>'; echo'<th scope="col" bgcolor="#66FFFF">Brand</th>'; echo'<th scope="col" bgcolor="#66FFFF">Quantity </th>'; echo'<th scope="col" bgcolor="#66FFFF">Paid By: </th>'; echo'<th scope="col" bgcolor="#66FFFF">Amount </th>'; echo'<th scope="col" bgcolor="#66FFFF">Date of Sale </th>'; echo'</tr>'; while($row = dbFetchArray($result)) // while($row = mysql_fetch_array($result)) { echo'<tr>'; echo'<td align="center">'; $SaleID = $row["SaleID"]; echo $SaleID. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Category = $row["Category"]; echo $Category. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Brand = $row["Brand"]; echo $Brand. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Quantity = $row["Quantity"]; echo $Quantity. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $PaidBy = $row["PaidBy"]; echo $PaidBy. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Cost = $row["Cost"]; echo $Cost. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $SaleDate = $row["SaleDate"]; echo $SaleDate. "<br>"; echo'</td align="center">'; echo '</tr>'; } echo'</table>'; echo "<br>"; echo "<strong>Sales figures:</strong>"; echo "<hr>"; $sql_total = "SELECT PaidBy, SUM(Cost) FROM cstocksales WHERE DATE_SUB(CURDATE(),INTERVAL '$day' DAY) <= SaleDate GROUP BY PaidBy"; $result = dbQuery($sql_total); while($row = dbFetchArray($result)) { echo "Total sales by ". $row['PaidBy']. "<font color=#CC0000> = Kshs <strong>". $row['SUM(Cost)']; echo "</font></strong>"; echo " "; echo"|"; echo " "; } $sql_total_final = "SELECT SUM(Cost) FROM cstocksales WHERE DATE_SUB(CURDATE(),INTERVAL '$day' DAY) <= SaleDate"; $result = dbQuery($sql_total_final); while($row = dbFetchArray($result)) { echo "<strong>All sales = <font color=#CC0000> Kshs " .$row['SUM(Cost)']; echo "</font></strong>" ; } echo "<br>"; echo '<h4><a href="index.php">Back</a> to sales panel.'; } } function salesbyBrand() { $byBrand = ''; if (isset($_POST['sltBrand'])) $byBrand = $_POST['sltBrand']; elseif (isset($_GET['search'])) $byBrand = $_GET['search']; $sql = "SELECT * FROM cstocksales WHERE Brand = '$byBrand'"; $result = dbQuery($sql, "search=$byBrand"); if (dbNumRows($result) == 0) //if none { echo "<p><font color=#CC0000><h4>No sales for '$byBrand' were found in the records...</h4></font></p>"; echo "<h4><a href='index.php'>Back</a> to sales panel."; } else { echo '<h5>"'.$_POST['sltBrand'].'" slaes:<p></h5>'; echo '<table width="100%" border="1" bordercolor="#000000">'; echo'<tr>'; echo'<th scope="col" bgcolor="#66FFFF">Sale No</th>'; echo'<th scope="col" bgcolor="#66FFFF">Category</th>'; echo'<th scope="col" bgcolor="#66FFFF">Brand</th>'; echo'<th scope="col" bgcolor="#66FFFF">Quantity </th>'; echo'<th scope="col" bgcolor="#66FFFF">Paid By: </th>'; echo'<th scope="col" bgcolor="#66FFFF">Amount </th>'; echo'<th scope="col" bgcolor="#66FFFF">Date of Sale </th>'; echo'</tr>'; while($row = dbFetchArray($result)) { echo'<tr>'; echo'<td align="center">'; $SaleID = $row["SaleID"]; echo $SaleID. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Category = $row["Category"]; echo $Category. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Brand = $row["Brand"]; echo $Brand. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Quantity = $row["Quantity"]; echo $Quantity. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $PaidBy = $row["PaidBy"]; echo $PaidBy. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Cost = $row["Cost"]; echo $Cost. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $SaleDate = $row["SaleDate"]; echo $SaleDate. "<br>"; echo'</td align="center">'; echo '</tr>'; } echo'</table>'; echo "<br>"; echo "<strong>Sales figures:</strong>"; echo "<hr>"; $sql_total = "SELECT PaidBy, SUM(Cost) FROM cstocksales WHERE Brand = '$byBrand' GROUP BY PaidBy"; $result = dbQuery($sql_total); while($row = dbFetchArray($result)) { echo "Total sales by ". $row['PaidBy']. "<font color=#CC0000> = Kshs <strong>". $row['SUM(Cost)']; echo "</font></strong>" ; echo " "; echo"|"; echo " "; } $sql_total_final = "SELECT SUM(Cost) FROM cstocksales WHERE Brand = '$byBrand'"; $result = dbQuery($sql_total_final); while($row = dbFetchArray($result)) { echo "<strong>All sales = <font color=#CC0000> Kshs " .$row['SUM(Cost)']; echo "</font></strong>" ; } echo "<br>"; echo '<h4><a href="index.php">Back</a> to sales panel.'; } } function sales_Month() { $month = $_POST['MonthSales']; $sql = "SELECT * FROM cstocksales WHERE MONTH(SaleDate) = '$month' AND YEAR(SaleDate) = YEAR(CURDATE())"; $result = dbQuery($sql); ///$result = dbQuery(getPagingQuery($sql, $rowsPerPage)); //$pagingLink = getPagingLink($sql, $rowsPerPage); if (dbNumRows($result) == 0) //if none { echo '<p><font color=#CC0000><h4>No sales were found in the records...</h4></font></p>'; echo '<h4><a href="index.php">Back</a> to sales panel.'; } else { echo '<h5>January sales:<p></h5>'; echo '<table width="100%" border="1" bordercolor="#000000">'; echo'<tr>'; echo'<th scope="col" bgcolor="#66FFFF">Sale No</th>'; echo'<th scope="col" bgcolor="#66FFFF">Category</th>'; echo'<th scope="col" bgcolor="#66FFFF">Brand</th>'; echo'<th scope="col" bgcolor="#66FFFF">Quantity </th>'; echo'<th scope="col" bgcolor="#66FFFF">Paid By: </th>'; echo'<th scope="col" bgcolor="#66FFFF">Amount </th>'; echo'<th scope="col" bgcolor="#66FFFF">Date of Sale </th>'; echo'</tr>'; while($row = dbFetchArray($result)) { echo'<tr>'; echo'<td align="center">'; $SaleID = $row["SaleID"]; echo $SaleID. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Category = $row["Category"]; echo $Category. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Brand = $row["Brand"]; echo $Brand. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Quantity = $row["Quantity"]; echo $Quantity. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $PaidBy = $row["PaidBy"]; echo $PaidBy. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $Cost = $row["Cost"]; echo $Cost. "<br>"; echo'</td align="center">'; echo'<td align="center">'; $SaleDate = $row["SaleDate"]; echo $SaleDate. "<br>"; echo'</td align="center">'; echo '</tr>'; } echo'</table>'; echo "<br>"; echo "<strong>Sales figures:</strong>"; echo "<hr>"; $sql_total = "SELECT PaidBy, SUM(Cost) FROM cstocksales WHERE MONTH(SaleDate) = '$month' AND YEAR(SaleDate) = YEAR(CURDATE()) GROUP BY PaidBy"; $result = dbQuery($sql_total); while($row = dbFetchArray($result)) { echo "Total sales by ". $row['PaidBy']. "<font color=#CC0000> = Kshs <strong>". $row['SUM(Cost)']; echo "</font></strong>"; echo " "; echo"|"; echo " "; } $sql_total_final = "SELECT SUM(Cost) FROM cstocksales WHERE MONTH(SaleDate) = '$month' AND YEAR(SaleDate) = YEAR(CURDATE())"; $result = dbQuery($sql_total_final); while($row = dbFetchArray($result)) { echo "<strong>All sales = <font color=#CC0000> Kshs " .$row['SUM(Cost)']; echo "</font></strong>" ; } echo "<br>"; echo '<h4><a href="index.php">Back</a> to sales panel.'; } } /* End of month sales*/ ?> Some body help here... 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.