Displaying Data using FOR loop in WHERE Clause.

General PHP Help
#1

Hello

I need your help with an issue where i need to get the data based on some condition. I just started learning php mysql.

I have a query which displays Product name and total amount, quantity of products sold each month for a particular year.

In the Where Clause I have Year and category of Products as conditions for which it has to filter the data and display Product names and quantity sold each month.

But It displays Only value of 1st month under every month. So i thought to add a where clause to select month with a for loop which loops 12 times for 12 months. But it is not sending month values to the where clause which causes it not to display any data.

I am attaching the query, code and image which displays results. Please let me know how can i solve this issue.

<?
include_once "configs.php";
include_once "paging.php";

date_default_timezone_set("America/Chicago");

$DBcon_MySQL = MakeDBConnection_MySQL($HotName, $UserName, $Pwd, $Database);

$Paging = new Paging();

//Post var
$OptionFlag = 0;

$token = trim(stripslashes($_REQUEST["token"]));
if (empty($token))
    $token = "";

$StartDate = trim(stripslashes($_REQUEST["StartDate"]));
if (empty($StartDate))
    $StartDate = date("Y");

$category_name = trim(stripslashes($_REQUEST["category_name"]));
if (empty($category_name)) {
    $category_name = "";
} else {
    $OptionFlag = 1;
}

//paging
$PageNumber = stripslashes(trim($_REQUEST["PageNumber"]));
if (empty($PageNumber))
    $PageNumber = 1;
$ContentsPerPage = stripslashes(trim($_REQUEST["ContentsPerPage"]));
if (empty($ContentsPerPage))
    $ContentsPerPage = 30;
$PageGroupPerPage = stripslashes(trim($_REQUEST["PageGroupPerPage"]));
if (empty($PageGroupPerPage))
    $PageGroupPerPage = 10;
$OrderBy = stripslashes(trim($_REQUEST["OrderBy"]));
if (empty($OrderBy))
    $OrderBy = "$StartDate";
$Sort = stripslashes(trim($_REQUEST["Sort"]));
if (empty($Sort))
    $Sort = "desc";

$oMonth_DB;

//query
$listSQL = "SELECT  op.name as 'prodname',op.model, SUM(op.quantity) AS QTY, SUM((op.price + op.tax) * op.quantity) AS Total, cd.name as 'CategoryName',YEAR(o.date_added) AS 'oYear', MONTH(o.date_added) AS 'oMonth',COUNT(o.order_id) AS numOfOrders FROM oc_order_product op INNER JOIN oc_order o ON op.order_id = o.order_id INNER JOIN oc_product_to_category p2c ON op.product_id = p2c.product_id INNER JOIN oc_category_description cd ON cd.category_id = p2c.category_id where LOWER(cd.name) LIKE '%" . strtolower($category_name) . "%'  AND YEAR(o.date_added) = '" . $StartDate . "' AND o.order_status_id > '0'   ";
//$listSQL = $listSQL . " AND  MONTH(o.date_added) = '".$oMonth_DB."' ";

$listSQL = $listSQL . "GROUP BY op.name, oMonth  ORDER BY op.name, oYear, oMonth ";

$listSQL    = $listSQL . " limit " . ($PageNumber - 1) * $ContentsPerPage . ", $ContentsPerPage";
$listResult = mysql_query("$listSQL", $DBcon_MySQL);

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title>Order Details List</title>
</head>

<body style="background-color: fff;">
<p style="width:1015px;">
<?php
echo file_get_contents( "error_log" ); // get the contents, and echo it out.
?>
</p>
<form name="Tanga" id="Tanga" method="get">
<input type="hidden" name="token" id="token" value="<?= $token ?>" />
<table cellpadding="0" cellspacing="0" style="background-color: rgb(79, 79, 79); width: 100%; padding: 5px;">
	<tr>
		<td height="25"></td>
	</tr>
</table>

<table cellpadding="0" cellspacing="0" style="border:#003399 solid 2px;">
	<tr>
  	   <td height="15"></td>
    </tr>
	<tr>
	<td width="5"></td>
  <td width="80" height="25">YEAR</td>
  <td width="220">
	<select type="text" name="StartDate" id="StartDate"  value="<?= $StartDate ?>">
		<option><?= $StartDate ?></option>
  </td>
  <td width="80" height="25">Category</td>
  <td width="220">
	<input type="text" name="category_name" id="category_name"  value="<?= $category_name ?>" />
  </td>
  <td width="100">
	<input type="submit" style="width:80px" value="Submit" />
  </td>
   <td width="150">
	<a href="Products_Purchased_category.php"><span class="btnMoreoption">[Reset]</span></a>
  </td>
  
</tr>
 
</table>

<table cellpadding="0" cellspacing="0" border="0">
	<tr><td height="25"></td></tr>
  <tr>
  	<td valign="middle" colspan="12" style="padding-top:10px; padding-bottom:10px; text-align:center; border:1px #ccc solid;"><h2>Product Names</h2></td>
    
  </tr>
  
 
  <?
while ($listRslist = mysql_fetch_array($listResult)) {
    
    $prod_name_DB = $listRslist["prodname"];
    
    $total_DB           = $listRslist["QTY"];
    $month_DB           = $listRslist["oMonth"];
    $year_DB            = $listRslist["oYear"];
    $numOfOrders_DB     = $listRslist["numOfOrders"];
    $order_status_id_DB = $listRslist["order_status_id"];
?>
  <tr>
  	<td valign="middle" colspan="12" style="padding-top:10px; padding-bottom:10px;">
    	<b><?
			echo $prod_name_DB;
		?></b>
    </td>
  </tr>
  <tr>
	<td valign="top">
		<table border="1">
			<tr>
				<?	
					$month = array(
						"January",
						"February",
						"March",
						"April",
						"May",
						"June",
						"July",
						"August",
						"September",
						"October",
						"Novemeber",
						"December"
					);
					//loop to get 12 Month names as <td>'s
					for ($i = 0; $i < 12; $i++) {
						
						echo '<td width="100" style="text-align: center; padding:10px; color:blue; background-color:#ccc;">' . $month[$i] . '</td>';
					}					
				?>
			
			</tr>
			<tr>
				<?
							//loop to get each month data 				
					for ($i = 1; $i < 13; $i++) {
						//$oMonth_DB = $i;
						if ($oMonth_DB = $i) {
							echo '<td width="100" style="text-align: center; padding:10px;"> ' .  ' Tot:' . $total_DB . ', month '. $oMonth_DB . '</td>';
						}
						
					}
				   
				?>			
			</tr>
		</table>
    	
    </td>
	
  </tr>
  
  <tr><td colspan="8" height="15"></td></tr> 
  <?
} //while($Rslist = mysql_fetch_array($Result)) {
?>
  <tr><td colspan="8" height="25"></td></tr> 
  <tr>
  	<td colspan="8" style="font-size:24px" height="40" align="center"><?= $Paging->ShowGrid_QueryString("Products_Purchased_category.php", $PageTotalRows, $ContentsPerPage, $PageGroupPerPage, $PageNumber, "token=$token&StartDate=$StartDate"); ?></td>
  </tr>
</table>
</form>
<script>

for (i = new Date().getFullYear(); i > 1900; i--)
{
    $('#StartDate').append($('<option />').val(i).html(i));
}
</script>

</body>
</html>
<?
if ($DBcon_MySQL) {
    mysqli_close($DBcon_MySQL);
    unset($DBcon_MySQL);
}
?>

Here is the image

http://nimb.ws/Zvrc58
Thank you for your help.

#2

Your code is obsolete and has been completely removed from PHP. You need to use PDO. You are also mixing mysql with mysqli.
https://phpdelusions.net/pdo

#3

First, you need to stop using MySQL and update your code to MySQLi. The old version is not available in most servers now.
The improved version is many many years old and the old version should not be used. I will assume this is for a class as
a lot of text books used nowadays are still in use and teaching old out-of-date PHP functions!

Next, your query is very hard to read. I suggest you alter it to this format which is much easier to read:
[php]
$listSQL = “SELECT op.name as ‘prodname’,op.model, SUM(op.quantity) AS QTY, SUM((op.price + op.tax) * op.quantity) AS Total, cd.name as ‘CategoryName’,YEAR(o.date_added) AS ‘oYear’, MONTH(o.date_added) AS ‘oMonth’,COUNT(o.order_id) AS numOfOrders FROM oc_order_product op INNER JOIN oc_order o ON op.order_id = o.order_id INNER JOIN oc_product_to_category p2c ON op.product_id = p2c.product_id INNER JOIN oc_category_description cd ON cd.category_id = p2c.category_id where LOWER(cd.name) LIKE '%” . strtolower($category_name) . “%’ AND YEAR(o.date_added) = '” . $StartDate . "’ AND o.order_status_id > ‘0’
GROUP BY op.name, oMonth ORDER BY op.name, oYear, oMonth
LIMIT " . ($PageNumber - 1) * $ContentsPerPage . “, $ContentsPerPage”;
[/php]
It is also much faster not to use, server-wise as it does not need the extra string handling…

So, you need to look into the way you are pulling out data. You group and order items, but, you can also add your totals by
just adding in a SUM function. Loosely, it is done something like this:

SELECT MONTHNAME(date), SUM(total)
FROM theTable
GROUP BY YEAR(date), MONTH(date);

What this does is select all of the dates and totals, sums the totals grouping by year and month. In doing this, the database
system will pull all of the data for that year/month and sum all of the data stored for that year/month that is in the total field.
You might want to look up MySQLi functions on summing and grouping.

Hope that helps…

#4

I know that I have started learning some old stuff from people who didn’t update themself. i tried converting code to pdo or mysqli but instead got heaps of errors so i ended up using old stuff due to shortage of time at the moment.

If i dont group them by [php]GROUP BY prodname,oMonth[/php] it is outputting [php]SUM(op.quantity) AS QTY[/php] as total not per month basis.

honestly this approach i am taking is not up to my liking as i like to take clean and shorter way to display the data. But i got no other option.

#5

I am checking and i will try to rewrite using the above tutorial. Thanks for the link.

#6

Well, if you group by just product and month, you will have problems with summing on qty as it will pull the months from all
of the years in the data. You group by product and month, but order by year and month? Are you trying to group all years into
the display table?

I think updating from MySQL to MySQLi is much faster and easier as a first step. PDO is the final goal in getting your code
up to date. Most teachers are now either using MySQLi or the old out-of-date MySQL. So, to continue learning with them,
you need to use MySQLi. But, as Kevin mentioned, MySQL does not even exist in PHP 7… Later on, you should study up on
filtering your inputs from the posted form. They have changed from the way you are doing it now, also…

#7

I recommend you download my free PDO Bumpstart Database from my signature link. Don’t even waste your time with Mysqli, go straight to PDO.

Sponsor our Newsletter | Privacy Policy | Terms of Service