php calculation from mysql

[Bin_2]

hey guys! this is my first post to daniweb. and also i've not been so long on php.

i m trying to manage customer transection records but stuck on one place and i need help..

here is my mysql table for transection of customer id-1 =>

+-----+------+----------+--------+------+-------|
| svn | cid  |   date   |  samt  | rvn  | ramt  |
|-----+------+----------+--------+------+-------|
|  1  |  1   | 2014/3/1 |  5000  |      |       |
|     |  1   | 2014/3/4 |        |  2   | 2000  |
|  4  |  1   | 2014/3/7 |  4000  |      |       |
|     |  1   | 2014/3/9 |        |  5   | 2000  |
+-----+-------+---------+--------+------+-------|

and in php i wish this to be dispalyed as =>

ledger for => John

+-----+-----+----------+---------+------+---------+
| svn | rvn |   date   |  sales  | ramt | balance |
|-----+-----+----------+---------+------+---------+
|  1  |     | 2014/3/1 |   5000  |      |  5000   |
|     |  2  | 2014/3/4 |         | 2000 |  3000   |
|  4  |     | 2014/3/7 |   4000  |      |  7000   |
|     |  5  | 2014/3/9 |         | 2000 |  5000   |
+-----+-----+----------+---------+------+---------|
|        Total         |   9000  | 4000 |  5000   |
+-------------------------------------------------+

i've retrived everything except the 'balance' column. i failed to manage the balance record after each transection. please help me find solution for this and please with explanations.

thank u in advance...

[pritaeas]

What do you have so far? Show your code.

[Bin_2]

it might be some longer
it is derived from single table. the same table have sales and receipt columns with different ids. the columns are

| ssn | rsn | date | cid | sref | samt | rref | ramt |

where
ssn = Sales Serial Number
rsn = Receipt Serial Number
date = for both transaction
cid = Customer ID
sref = Sales Reference Number
samt = Sales Amount
rref = Receipt Reference Number
ramt = Receipt Amount

<?php
    require_once('functions.php');
    $user = new User;
    if (!$user->isLoggedIn) {
        die(header("location: login.php"));
    }


$cid = $_GET['cid'];
$sql11 = "SELECT * FROM customer where cid = '$cid' ";
$result11 = mysqli_query($connect, $sql11);
$rs = mysqli_fetch_object($result11);
$ob = $rs->cob;

// select customer
$sql0 = "SELECT * FROM customer WHERE cid = '$cid'";
$result0 = mysqli_query($connect, $sql0) or die("Error: " . mysqli_error($connect));
$row0 = mysqli_fetch_array($result0);
//select sales num
$result2 = mysqli_query($connect, "SELECT * FROM tran WHERE cid='$cid' AND ssn != 0") or die("Error: " . mysqli_error($connect));
$row2 = mysqli_fetch_array($result2);
$n = mysqli_num_rows($result2);
//select receipt num
$result3 = mysqli_query($connect, "SELECT * FROM tran WHERE cid='$cid' AND rsn != '0'") or die("Error: " . mysqli_error($connect));
$row3 = mysqli_fetch_array($result3);
$n1 = mysqli_num_rows($result3);


$result4 = mysqli_query($connect, "SELECT sum(samt), sum(ramt) FROM tran WHERE cid='$cid'") or die("Error: " . mysqli_error($connect));
$row4 = mysqli_fetch_array($result4);
$TotalSales = $row4['sum(samt)'];
$TotalReceipt = $row4['sum(ramt)'];
$balance = $ob + $TotalSales - $TotalReceipt;

echo "
<table border='1' cellpadding='6' style='font-size:14px' cellspacing='0'>
    <tr bgcolor='#006699' align='center' style='font-weight:bold;'>
        <td width='150px'>Date</td>
        <td>Sales No.</td>
        <td>Receipt No.</td>
        <td width='200px'>Sales</td>
        <td width='150px'>Receipt</td>
        <td width='150px'>Balance</td>
    </tr>
    <tr bgcolor='#CCCCCC' align='center'>
        <td colspan='5'><a href='cedit.php?cid=$cid' class='index'>Opening Balance</a></td>
        <td align='right'>Rs." . number_format(($ob), 2, '.', ',') . "</td>
    </tr>";
// select tran
$sql = "SELECT * FROM tran WHERE cid = '$cid' ORDER BY date";
$result = mysqli_query($connect, $sql) or die("Error: " . mysqli_error($connect));

while($row = mysqli_fetch_array($result)) {
echo "
    <tr bgcolor='#009999' align='center'>
        <td>" . $row['date'] . "</td>
        <td>";
        if ($row['sref'] > 0) {
        echo "<a href='salesedit.php?ssn=$row[ssn]' class=slink>" . $row['sref'];
        } else {
            echo " ";
        }
        echo "
        </td>
        <td>";
        if ($row['rref'] > 0) {
        echo "<a href='receiptedit.php?rsn=$row[rsn]' class='slink'>" . $row['rref'] . "</a>";
        } else {
            echo " ";
        }
        echo "</td>";
        if ($row['samt'] > 0) {
        echo "<td align='right'>Rs." . number_format(($row['samt']), 2, '.', ',') . "</td>";
        } else {
            echo "<td align='center'>-</td>";
        }
        if ($row['ramt'] > 0) {
        echo "<td align='right'>Rs." . number_format(($row['ramt']), 2, '.', ',') . "</td>";
        } else {
            echo "<td align='center'>-</td>";
        }
        echo "
        <td align='right'>Rs." . // . number_format(($rowbal), 2, '.', ',') . 
        "</td>
    </tr>";
    }
echo "
    <tr bgcolor='#FFFFFF' align='center'>
        <td>Total:</td>
        <td>" . $n . " Sale(s)</td>
        <td>" . $n1 . " Receipt(s)</td>
        <td align='right'>Rs. "  . number_format(($TotalSales), 2, '.', ',') . "</td>
        <td align='right'>Rs. "  . number_format(($TotalReceipt), 2, '.', ',') . "</td>
        <td align='right'>Rs. "  . number_format(($balance), 2, '.', ',') . "</td>
    </tr>
</table>";
?>

[Bin_2]

noone is interested here!?

[pritaeas]

Why do you have so many queries, if everything is coming from the same table?

Haven't had time yet to try your code btw.

[Bin_2]

because they different conditions are applied, one is "WHERE ssn!=0", another "WHERE rsn!=0" and another with no condition. first one selects 'sales' (where ssn is Sales Serial Number) and next one selects 'receipts' (where rsn is Receipt Serial Number)......