Member Avatar for Octavian_1

I have the following code that is working but i need further functionality.
The code shows the payments made and is based on two tables. The payments table that has the following fields mID, type (account type), dayentered(date when the money was donated) and the members table has the following fields name, mID (this is the primary key) and status
Now my proble is that i can not add the following functionality to the code
1. Total for each type of payment account (the payments are based on different types eg for utilities, for building and these are recurrent for each month)
2. Total for all the accounts for each year
3. A code so that the name of the person should be shown on the page (The members page has the following fields name, mID (this is the primary key), status.
please help

Here is the code

<html>
<head>
<link rel="stylesheet" type="text/css" href="mystyle.css" />
        <title>details</title>
        <h1>Details of your payments</h1>
</head>
<body>

<?php
//require_once('auth.php');
/* 

*/

        // connect to the database
        include('connect.php');



 $mID = $_GET['mID'];
        $result = mysql_query("SELECT DISTINCT mID, sum(amount), type, dayentered
FROM payments
WHERE mID=$mID
GROUP BY TYPE, YEAR(dayentered), MONTH(dayentered)
") 
                or die(mysql_error());  

        // display data in table

        echo "<p><b>View All</b> </a></p>";

        echo "<table border='1' cellpadding='10'>";
        echo "<tr> <th>SERIAL</th> <th>ACC TYPE</th> <th>MONTH PAID</th> <th>AMOUNT</th></tr>";

        // loop through results of database query, displaying them in the table
        while($row = mysql_fetch_array( $result )) {

                // echo out the contents of each row into a table
                echo "<tr>";
                echo '<td>' . $row['mID'] . '</td>';
                echo '<td>' . $row['type'] . '</td>';
                echo '<td>' . $row['dayentered'] . '</td>';
                echo '<td>' . $row['sum(amount)'] . '</td>';

        } 

        // close table>
        echo "</table>";

?>

<p><a href="home.php">Home</a></p>

</body>
</html>
  • 2 Contributors
  • 2 Replies
  • 260 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Octavian_1
Member Avatar for diafol

I think I'd be looking more to a schema like this:

Members

mID (int,PK)
Firstname (varchar,30)
Surname (varchar,30)
(...other details...)
Status (what does this do??)

Payments

pID (int,PK)
aID (tinyint,FK)
DayEntered (date, Y-m-d)
PaidAmount (decimal,2)

AccountTypes

tID (tinyint,PK)
TypeName (varchar, 20)
(...maybe other fields, like tax codes/% etc...)

Account

aID (int, PK)
tID (tinyint, FK)
mID (int, FK)
OpenDate (date, Y-m-d)
FirstPayment (date, Y-m-d)
LastPayment( date, Y-m-d)
RecurringDay (tinyint,2), e.g. 3 (for the 3rd day every month)
DueAmount (decimal, 2)

You could have other tables like 'Late' etc to identify missed payments and any resolutions

Anyway, back to your schema - BUT you don't mention that the payments table has an 'amount' field - I'll assume it has for now, from the code you supply:

SELECT m.name, SUM(p.amount) AS amt, p.type, MONTH(p.dayentered) AS month, YEAR(p.dayentered) AS year FROM members as m INNER JOIN payments AS p ON m.mID = p.mID WHERE mID = $mid GROUP BY YEAR(p.dayentered), MONTH(p.dayentered), p.type

Not tested - off the top of my head. If a member only makes one payment of each type every month, then this may be pointless, as a simple Order By (pay date) will probably give you the same result without all the summing and grouping.

Is that the sort of thing you need?

Member Avatar for Octavian_1

Yes, the selct query has worked fine, however your schema looks better so i will try it. Status is for active and non active i.e. to show if the member is still active
thanks

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.