Hi all.I have two tables sales and purchase.And it has supporting sales_dy and purchase_dy tables respectively which stores quantities and rates etc.All other details are stored in sales and purchase.Problem is I want to display the stock from a particular date and I am using nested while loop for this.But it doesn’t seem working. #1.If any of the tables dont have values, it shows only the portion above while loop #2.If both the tabals have values, it displays items name same in all the columns. #3.It shows purchase quantity properly but dont show sales quantity. #4.Im not sure about GROUP BY is right choice here.
Here is my code:
<?PHP
if(isset($_POST['Submit']))
{ //if isset
$to=$_POST['to'];
$from=$_POST['from'];
$sales=mysql_query
("SELECT
sales_dy.item_id,
SUM(sales_dy.qty) as sales_quantity,
item_name.name,
sales.inv_date
FROM
sales_dy
INNER JOIN
item_name
ON
sales_dy.item_id=item_name.item_id
INNER JOIN
sales
ON
sales.number=sales_dy.number
WHERE
((sales.inv_date >='".$to."'
AND
sales.inv_date <='".$from."'))
GROUP BY
sales_dy.item_id");
$purchase=mysql_query
("SELECT
purchase_dy.item_id,
SUM(purchase_dy.qty) as purchase_quantity,
item_name.name,
purchase.inv_date
FROM
purchase_dy
INNER JOIN
item_name
ON
purchase_dy.item_id=item_name.item_id
INNER JOIN
purchase
ON
purchase.number=purchase_dy.number
WHERE
((purchase.inv_date >='".$to."'
AND
purchase.inv_date <='".$from."'))
GROUP BY
purchase_dy.item_id");
if(mysql_num_rows($sales) > 0 || mysql_num_rows($purchase) > 0)
{ //if num rows
echo "<div align='center'><table border='0' width='40%'>";
echo "<tr>
<th scope='col'>Name</th>
<th scope='col'>Purchase</th>
<th scope='col'>Sales</th>
<th scope='col'>Balance</th>
</tr>";
while($sales_qty=mysql_fetch_array($sales))
{ //while row
while($purchase_qty=mysql_fetch_array($purchase))
{ //while row1
$balance=$purcahse_qty['purchase_quantity']-$sales_qty['sales_quantity'];
echo "<tr>
<td>"; echo $sales_qty['name']; echo "</td>
<td>"; echo $purchase_qty['purchase_quantity']; echo "</td>
<td>"; echo $sales_qty['sales_quantity']; echo "</td>
<td>"; echo $balance; echo "</td>
</tr>";
}//while row
}//while row1
echo "</table></div>";
}//if num rows
else
{ //else num rows
echo "<div align='center' style='height:50px; width:auto; margin-top:20px' class='information'>Sorry No Records Found..</div>";
} //else num rows
} //if isset
?>
Similarly, I would like to know how can I show the same records date wise.Like,
01/12/2011 Computer - purchase-10 sales-2 and so on.Please help me in this regard.
I am not sure if it can be managed with a single query from by modifying you database table design but from the current queries you can get the results as follows (untested whole code though):
if(isset($_POST['Submit']))
{
//if isset
$to = $_POST['to'];
$from = $_POST['from'];
$sql = "SELECT
sales_dy.item_id, SUM(sales_dy.qty) as sales_quantity, item_name.name, sales.inv_date
FROM sales_dy
INNER JOIN item_name ON sales_dy.item_id=item_name.item_id
INNER JOIN sales ON sales.number=sales_dy.number
WHERE sales.inv_date BETWEEN '".$to."' AND '".$from."'
GROUP BY sales_dy.item_id";
$sales = mysql_query($sql) or die(mysql_error());
$sql = "SELECT
purchase_dy.item_id, SUM(purchase_dy.qty) as purchase_quantity, item_name.name, purchase.inv_date
FROM purchase_dy
INNER JOIN item_name ON purchase_dy.item_id=item_name.item_id
INNER JOIN purchase ON purchase.number=purchase_dy.number
WHERE purchase.inv_date BETWEEN '".$to."' AND '".$from."'
GROUP BY purchase_dy.item_id";
$purchase = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($sales) > 0 || mysql_num_rows($purchase) > 0)
{
//if num rows
echo '<div align="center">
<table border="0" width="40%">
<tr>
<th scope="col">Name</th>
<th scope="col">Purchase</th>
<th scope="col">Sales</th>
<th scope="col">Balance</th>
</tr>';
for($i = 0; $i < mysql_num_rows($sales); $i++){
mysql_field_seek($sales, $i);
mysql_field_seek($purchase, $i);
$sales_qty = mysql_fetch_array($sales);
$purchase_qty = mysql_fetch_array($purchase);
$balance = $purcahse_qty['purchase_quantity'] - $sales_qty['sales_quantity'];
echo '<tr>
<td>' . $sales_qty['name'] . '</td>
<td>' . $purchase_qty['purchase_quantity'] . '</td>
<td>' . $sales_qty['sales_quantity'] . '</td>
<td>' . $balance . '</td>
</tr>';
}
echo "</table>
</div>";
}//if num rows
else
{ //else num rows
echo "<div align='center' style='height:50px; width:auto; margin-top:20px' class='information'>Sorry No Records Found..</div>";
} //else num rows
} //if isset
If you mean ‘sales.inv_date’ then add ‘order by sales.inv_date desc/asc’ in both of the SQL queries and add a column in the table and echo it in the same way as others.
Well its working well but not displaying balance quantities.I altered the code as per my requirements and it displays records date wise.But the balance quantity is not displayed properly.
Here is my code:
<?PHP
if(isset($_POST['Submit']))
{
//if isset
$to = $_POST['to'];
$from = $_POST['from'];
$sql = "SELECT
sales_dy.item_id, sales_dy.qty as sales_quantity, item_name.name, sales.inv_date
FROM sales_dy
INNER JOIN item_name ON sales_dy.item_id=item_name.item_id
INNER JOIN sales ON sales.number=sales_dy.number
WHERE sales.inv_date BETWEEN '".$to."' AND '".$from."'
ORDER BY sales.inv_date";
$sales = mysql_query($sql) or die(mysql_error());
$sql = "SELECT
purchase_dy.item_id, purchase_dy.qty as purchase_quantity, item_name.name, purchase.inv_date
FROM purchase_dy
INNER JOIN item_name ON purchase_dy.item_id=item_name.item_id
INNER JOIN purchase ON purchase.number=purchase_dy.number
WHERE purchase.inv_date BETWEEN '".$to."' AND '".$from."'
ORDER BY purchase.inv_date";
$purchase = mysql_query($sql) or die(mysql_error());
//query to fetch total purchase quantity so as to be used to calculate total balance available
$sql = "SELECT
item_id,
SUM(qty) as total_purchase_quantity
FROM
purchase_dy
GROUP BY
item_id";
$total_purchase = mysql_query($sql) or die(mysql_error());
//query to fetch total sales quantity so as to be used to calculate total balance available
$sql = "SELECT
item_id,
SUM(qty) as total_sales_quantity
FROM
sales_dy
GROUP BY
item_id";
$total_sale = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($sales) > 0 || mysql_num_rows($purchase) > 0)
{
//if num rows
echo '<div align="center">
<table border="0" width="40%">
<tr>
<th scope="col">Date</th>
<th scope="col">Name</th>
<th scope="col">Purchase</th>
<th scope="col">Sales</th>
<th scope="col">Balance</th>
</tr>';
for($i = 0; $i < mysql_num_rows($purchase); $i++){
mysql_field_seek($sales, $i);
mysql_field_seek($purchase, $i);
mysql_field_seek($total_purchase, $i);
mysql_field_seek($total_sale, $i);
$sales_qty = mysql_fetch_array($sales);
$purchase_qty = mysql_fetch_array($purchase);
$total_sales_qty = mysql_fetch_array($total_sale);
$total_purchase_qty = mysql_fetch_array($total_purchase);
$balance = $total_purchase_qty['total_purchase_quantity'] - $total_sales_qty['total_sales_quantity'];
if(empty($sales_qty['inv_date'])){
$inv_date=(date('d-m-Y', strtotime($purchase_qty['inv_date'])));
}
else{
$inv_date=(date('d-m-Y', strtotime($sales_qty['inv_date'])));
}
echo '<tr>
<td>' . $inv_date. '</td>
<td>' . $purchase_qty['name'] . '</td>
<td>' . $purchase_qty['purchase_quantity'] . '</td>
<td>' . $sales_qty['sales_quantity'] . '</td>
<td>' . $balance . '</td>
</tr>';
}
echo "</table>
</div>";
}//if num rows
else
{ //else num rows
echo "<div align='center' style='height:50px; width:auto; margin-top:20px' class='information'>Sorry No Records Found..</div>";
} //else num rows
} //if isset
?>
Result is :
which must be :
Purchase Sales Balance
10 1 9
10 0 10
5 0 14
As per my understanding you still have the problem with the nested looping. Please read this thread, I wish it will work for you.
www(dot)dmxzone(dot)com/go?4937
