0

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

2
Contributors
5
Replies
92
Views
3 Years
Discussion Span
Last Post by Bin_2
0

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>";
?>
0

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.

0

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)......

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.