Member Avatar for 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...

  • 2 Contributors
  • 5 Replies
  • 327 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by Bin_2

Recommended Answers

All 5 Replies

Member Avatar for pritaeas

What do you have so far? Show your code.

Member Avatar for 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>";
?>
Member Avatar for Bin_2

noone is interested here!?

Member Avatar for 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.

Member Avatar for 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)......

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.