How do I Paginate my database results?

gnawz · 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>" ;
	}
}

}

?>

trq · October 1, 2008

Have you tried googling a tutorial? There are literally thousands out there.

dropfaith · October 1, 2008

http://www.phpfreaks.com/tutorial/basic-pagination

Zhadus · 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.

gnawz · 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...

Zhadus · 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.
}

gnawz · October 1, 2008

Hi,

Please give a little more light...

Zhadus · 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> ";

gnawz · 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)?

Zhadus · October 1, 2008

I saw the link it is in the allsalestoday() function, would that have a different action aside from allsalestoday?

gnawz · 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.

Zhadus · 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.

gnawz · 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

gnawz · October 7, 2008

Any one helping on this?

I'm really stuck!

R0bb0b · 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.

gnawz · 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.

gnawz · 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...

Sign In

How do I Paginate my database results?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information