Hi all.I found this code on forum.But dont know how to alter this to show the records datewise.I want to display sales and purchase for given period of time and show the balance quantity by deducting total sales from the total purchase.

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  
?>

It displays result as follows:
Date Purchase Sales Balance
23-12-2011 10 1 13
25-12-2011 10 1 9
25-12-2011 5 1 -1

Where it has to show balance 9 on 23-12-2011 and 13 on 25-12-2011.Please tell me where I am going wrong?Is this possible through query?

Recommended Answers

All 2 Replies

Member Avatar for diafol

Post this again using code-tags - it's too difficult for me to follow.

Ok.Sorry for that.

<?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
?>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.