Request for assistance...

  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Received via PM...passing along so others may help as well:

i got this application that keeps adding member savings weekly and i need a way to organize mysql database or php to display the current and previous savings info even after the members have made their savings. any ideas on how perharps i can organinze my database or even handle this at php? currently am able to view the current total member's outstandings which comes as a result of overwriting the previous total outstandings of the member's total savings. if possible i need a way to be displaying the current and previous total member's outstandings at an ealier or later dates. for example if John's total savings as at 3rd june 2009 is 4000 then i need to show 3000 as the previous outstanding and 4000 as the current outstanding after he saved 1000 on this date. the 3000 was 27th may 2009 outstanding. i need to allow users to view the outstanding as at any date or even after months and show the recent previous dates outstandings. the savings are done weekly. thanks in advance and if am not clear please let me know, anyone...
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Tiffix, what's your current database setup look like? Are you saving the individual numbers as they insert them and then totalling within your application, or are you just keeping a total in the table?
  • tiffix
  • Student
  • Student
  • User avatar
  • Posts: 65
  • Loc: kenya

Post 3+ Months Ago

am totaling at the php... sorry my net was down for while
  • tiffix
  • Student
  • Student
  • User avatar
  • Posts: 65
  • Loc: kenya

Post 3+ Months Ago

hello anyone there...?
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Please allow sufficient time for response. Keep in mind that this is a forum, not a chat room. You took 12 minutes to reply yourself, and within 7 minutes following that, you posted again and PM'ed me. We have jobs to do, so be patient. ;)

If you have the different numbers stored in the database, then why can't you sum all but the most recent entry? That would give you:

Previous + New = Total
3000 + 1000 = 4000
  • tiffix
  • Student
  • Student
  • User avatar
  • Posts: 65
  • Loc: kenya

Post 3+ Months Ago

sorry i got you right, this is my first forum ever. otherwise, the current totals are stored in one table and the cumulative savings in another. the trick is to display each members current outsanding and still show the previous outstanding before the savings. this should happen each time a user view savings for a certain date earlier or later. thanks
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Language barriers suck. :(

I'm sorry, but I'm afraid I just don't understand the question. Perhaps someone will pitch in who's getting what the underlying issue is.
  • tiffix
  • Student
  • Student
  • User avatar
  • Posts: 65
  • Loc: kenya

Post 3+ Months Ago

3000 is a previous outstanding and 4000 a new one as at 3rd jun 09, so somewhere in july 2009 lets say 20th, after the savings the member outstanding can be something like 4000 previous and 5000 new outstanding. and somewhere in apr (earlier view) the outstanding previous could be 2000 and current 3000. get what i mean...
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Sure, but if you're storing them in the table each month, then you can figure out the amounts...

Table.Outstanding
_____________________
Apr 09 - 2000
May 09 - 3000
Jun 09 - 4000
Jul 09 - 5000

If I know that Apr was 2000 & May is 3000, then in PHP, I can say 3000 - 2000 = 1000 (savings for the month of May)
  • tiffix
  • Student
  • Student
  • User avatar
  • Posts: 65
  • Loc: kenya

Post 3+ Months Ago

i gave out the monthly outstanding so that i can at least be clear, members make a weekly savings from which i store the date, month and php timestamp. so if in june 3rd 09, how can we then view savings for may 27th 09 (total outstanding and still view june 3rd 09). if this can be done if for the previous weeks.
  • tiffix
  • Student
  • Student
  • User avatar
  • Posts: 65
  • Loc: kenya

Post 3+ Months Ago

remember this total outstanding are gotten after adding the savings from a diffrent table using php not in mysql
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Quote:
members make a weekly savings from which i store the date, month and php timestamp.


When you do that, store the amount of the savings as well. If you have the amount each time, then you could take your cumulative savings amount, decide which dates are newer than the target date, and then do the math:

Cumulative savings = 5000

Savings (with new amount field):
March 1st 09 - 1000
April 10th 09 - 1000
May 10th 09 - 1000
June 3rd 09 - 1000

Given that information, I can say "What was my outstanding amount in May of 09?

Cumulative - all prior months = target outstanding

Does March come after or equal to May? no - ignore it.
April after/equal May? no - ignore it.
May after/equal may? yes - use it.
June after/equal May? yes - use it.

5000 - 1000 - 1000 = 3000 outstanding in April '09

Without having those amounts stored somewhere or knowing that they are a set amount each time, I don't think you can figure out a past outstanding amount. You should set up a new column for the savings amount and back fill the column with historical data if possible.
  • tiffix
  • Student
  • Student
  • User avatar
  • Posts: 65
  • Loc: kenya

Post 3+ Months Ago

wow, i think you have a great concept and am trying to grab it. will let you know if i succeed with what am seeking. thank BIG TIME for the support, your time and ideas. keep up. am finding the forum usefull already...
  • tiffix
  • Student
  • Student
  • User avatar
  • Posts: 65
  • Loc: kenya

Post 3+ Months Ago

can u kindly give me a mysql select qeury perharps to try and express the descirption given above
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Sure, if you can show me your table structures(something like below), then I would be glad to put something together...

Example:
table savings
column amount (int)
column date (datetime)

Maybe if you can show me the code where you're adding up the total outstanding, then I can help you work that in as well.
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

If you can do us a

Code: [ Select ]
SHOW CREATE TABLE tablename


query on all your database tables then we could give you a php example of sql for you query, extracting the array, and calculating the differences in savings with date comparisons.
  • tiffix
  • Student
  • Student
  • User avatar
  • Posts: 65
  • Loc: kenya

Post 3+ Months Ago

check this out=>

--------------------------------------
### structure of table `emg_shares` ###

DROP TABLE IF EXISTS `emg_shares`;

CREATE TABLE `emg_shares` (
`id` int(4) NOT NULL,
`member_name` varchar(100) NOT NULL,
`date` varchar(15) NOT NULL,
`amount` decimal(10,2) NOT NULL DEFAULT '0.00',
`month` varchar(8) NOT NULL,
`timestamp` bigint(10) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


### data of table `emg_shares` ###



### structure of table `emg_shares_totals` ###

DROP TABLE IF EXISTS `emg_shares_totals`;

CREATE TABLE `emg_shares_totals` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`member_name` varchar(100) NOT NULL,
`totals` decimal(10,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=44 DEFAULT CHARSET=latin1 AUTO_INCREMENT=44;


### data of table `emg_shares_totals` ###

insert into `emg_shares_totals` values ('1', 'ANN W. KIHARA', '0.00');
insert into `emg_shares_totals` values ('2', 'CATHERENE MUTURI ', '0.00');
insert into `emg_shares_totals` values ('3', 'CHARLES NJENGA KANG\'ETHE', '0.00');
insert into `emg_shares_totals` values ('4', 'DANIEL KIYO ', '0.00');
insert into `emg_shares_totals` values ('5', 'DAVID KIMANI ', '0.00');
insert into `emg_shares_totals` values ('6', 'DAVID MUHIA ', '0.00');
insert into `emg_shares_totals` values ('7', 'ELIZABETH GICHURU ', '0.00');
insert into `emg_shares_totals` values ('8', 'FRANCIS MBUGUA THARAU', '0.00');
insert into `emg_shares_totals` values ('9', 'FRANCIS MUCHABA ', '0.00');
insert into `emg_shares_totals` values ('10', 'FRED K. MWANGI', '0.00');
insert into `emg_shares_totals` values ('11', 'GEORGE KINGIRI ', '0.00');
insert into `emg_shares_totals` values ('12', 'GEORGE WAITHAKA ', '0.00');
insert into `emg_shares_totals` values ('13', 'GRACE WANJA IRUNGU', '0.00');
insert into `emg_shares_totals` values ('14', 'JACINTA NDEGWA ', '0.00');
insert into `emg_shares_totals` values ('15', 'JAMES NJENGA ', '0.00');
insert into `emg_shares_totals` values ('16', 'JANE MUGURE WAITATHU', '0.00');
insert into `emg_shares_totals` values ('17', 'JANET MUIRURI ', '0.00');
insert into `emg_shares_totals` values ('18', 'JOSEPH MATHII ', '0.00');
insert into `emg_shares_totals` values ('19', 'JOSEPH MOCHU NJIHIA', '0.00');
insert into `emg_shares_totals` values ('20', 'JOSPHAT NGAMAU ', '0.00');
insert into `emg_shares_totals` values ('21', 'JOYCE GATHONI NJOROGE', '0.00');
insert into `emg_shares_totals` values ('22', 'JUDY KIARIE ', '0.00');
insert into `emg_shares_totals` values ('23', 'KENNETH NDERITU ', '0.00');
insert into `emg_shares_totals` values ('24', 'LUCY GATHONI NJUGUNA', '0.00');
insert into `emg_shares_totals` values ('25', 'MARTHA KAMAU ', '0.00');
insert into `emg_shares_totals` values ('26', 'MARY NGIGI ', '0.00');
insert into `emg_shares_totals` values ('27', 'NANCY NDUNG\'U ', '0.00');
insert into `emg_shares_totals` values ('28', 'NELLY GICHURU ', '0.00');
insert into `emg_shares_totals` values ('29', 'PENINAH NG\'ENDO ', '0.00');
insert into `emg_shares_totals` values ('30', 'PETER M. KINGE', '0.00');
insert into `emg_shares_totals` values ('31', 'PETER W. KAMAU', '0.00');
insert into `emg_shares_totals` values ('32', 'PETER W. NJERI', '0.00');
insert into `emg_shares_totals` values ('33', 'RICHARD MBURU ', '0.00');
insert into `emg_shares_totals` values ('34', 'ROSEMARY MUGURE ', '0.00');
insert into `emg_shares_totals` values ('35', 'RUTH KARUNDA ', '0.00');
insert into `emg_shares_totals` values ('36', 'RUTH NJOKI MAINA', '0.00');
insert into `emg_shares_totals` values ('37', 'SALOME CHEGE ', '0.00');
insert into `emg_shares_totals` values ('38', 'SIMON MUNGAI ', '0.00');
insert into `emg_shares_totals` values ('39', 'STEPHEN NDUNG\'U ', '0.00');
insert into `emg_shares_totals` values ('40', 'SUSAN NJERI KAMOTHO', '0.00');
insert into `emg_shares_totals` values ('41', 'SUASAN NJERI NGOTHO', '0.00');
insert into `emg_shares_totals` values ('42', 'VERONICAH MIHARI ', '0.00');
insert into `emg_shares_totals` values ('43', 'VICTORIA MBUGUA ', '0.00');
  • tiffix
  • Student
  • Student
  • User avatar
  • Posts: 65
  • Loc: kenya

Post 3+ Months Ago

each members totals are gotten after the cumulative weekly savings.
  • tiffix
  • Student
  • Student
  • User avatar
  • Posts: 65
  • Loc: kenya

Post 3+ Months Ago

the timestamp column is gotten from converting for example (date column) May 09, 2009 to string using php function strtorime().
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

First off, rather than storing a datein two varchars and a bigint, why not use a single datetime data type?

Given that, if I wanted to figure out what the outstanding was in April, then I would do something like this...

Code: [ Select ]
 
SELECT emg.*,ttl.totals FROM emg_shares emg
LEFT JOIN emg_shares_totals ttl
ON ttl.member_name = emg.member_name
WHERE emg.member_name = 'JOHN SMITH'
AND emg.date > '2009-04-01'
 
  1.  
  2. SELECT emg.*,ttl.totals FROM emg_shares emg
  3. LEFT JOIN emg_shares_totals ttl
  4. ON ttl.member_name = emg.member_name
  5. WHERE emg.member_name = 'JOHN SMITH'
  6. AND emg.date > '2009-04-01'
  7.  


That would give me the current total as well as all payments John made from April to now.

Total - Payments = outstanding in April
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

remember to format your date when you store it in a datetime column type.

Should be Y-m-d

I'm not sure if your sql will make the comparison correctly with the inserted value of '04/01/2009' . I've never really done that, I always use Y-m-d and only change the string if it's going to be displayed. Will mySQL resolve that format? (...and I'm assuming the topic creator is using mysql)
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Didn't really put the screws to it as I was typing since I swap between SQL Svr and MySQL quite a bit - post changed to reflect the standard format. Thanks for catching it. I know SQL Server deals with it, but not sure about MySQL. Better to form good habits anyways. :)
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

sorry, my mistake datetime. He should just use a date format

field which would be Y-m-d

Code: [ Select ]
$date = date("Y-m-d");


Instead of YYYY-MM-DD HH:MM:SS since he's storing a

timestamp anyway. A date comparison may still work without storing the time but I've never tried that. I like fields to be exactly what i'm storing so I've never tested it.

I think a wiseman said:
Just because something is possible doesn't make it right for
every situation. Avoiding things like that helps to prevent
unnecessary code obfuscation. Simplicity always lends a
helping hand to maintainability. :-)
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Yeah there's a bit of oddity surrounding what and how it's stored. I probably would have gone down the same road had I designed the tables and all. I certainly like my fields to reflect what I'm storing, too. Now THAT could cause some headaches! heh
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

Actually, UPSGuy I was telling the topic creator to remember to format the date. The second part of my post was directed to you because I just wasn't entirely sure mysql would resolve the format, but I was certain you knew better and was just a slip up probably due to swapping between sql svr and mysql which you confirmed for me was the case.
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

Tiffix,

Here's a link that may help and again I'm assuming you're using mysql which is a very good chance if you're using php.

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

After you make your changes to your date field and update your sql inserts then finding calculations for a give date range should be a cinch and UPSGuy's query should work nicely. Keep us posted with any problems making your changes.
  • tiffix
  • Student
  • Student
  • User avatar
  • Posts: 65
  • Loc: kenya

Post 3+ Months Ago

it is not a must that a member makes a saving some do some skip. so i can't rely on the emg_shares alone i need to work with the emg_shares_totals too. or should i create another table for the outstanding totals that hold the outstanding as at a given date as the members make savings but this will not be reliable since not all members make savings. so for example if 27th May 09 not all members made a savings so this new table wont be correct since it will only store the outstanding of a give date for only the members who made a savings
  • tiffix
  • Student
  • Student
  • User avatar
  • Posts: 65
  • Loc: kenya

Post 3+ Months Ago

if we have this example:

6th saving =1000/-
13th saving =1000/-
20th saving =1000/-
27th saving =1000/-

which makes the true current total outstanding to be 4000 (for example) assuming the member started saving from 6th.
----------------------------------------------------------

now if we want to have such a view for example for (13th):

member num
----------
1

contrib date
------------
May 13, 2009

amount contributed
------------------
1000.00



and also this view at the same time below the above view:

member num
----------
1


total outstd before 13th saving
---------------------------
1000


outstd as at 13th
-----------------
2000


current true total outstd as at 27th
------------------------------------
4000


this is what am trying to do. any help on this? keep me posted
  • tiffix
  • Student
  • Student
  • User avatar
  • Posts: 65
  • Loc: kenya

Post 3+ Months Ago

yes am using mysql
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

This may not be 100% right but it's a start as I try to understand. Maybe this will help you tailor it to your needs if it's atleast close.

Code: [ Select ]
 
 
//fill in with memberid you want to display
$memberid = ;
 
// remember correct format
$begindate = "2009-05-01"
$enddate = "2009-05-31";
 
echo "<table>";
// lets get our members totals
$qryz = "SELECT * FROM emg_shares_totals WHERE id = '$memberid'";
$runanother = mysql_query($qryz);
while ($rowz = mysql_fetch_array($runanother)) {
$mem_id = $rowz['id'];
$mem_name = $rowz['member_name'];
$totalz = $rowz['totals'];
 
echo "<tr><td>Member Num</td><td>Member Name</td><td>Total</td></tr>";
echo "<tr><td>$mem_id</td><td>$mem_name</td><td>$totalz</td></tr>";
echo "<tr><td>Date</td><td>Savings</td><td>Total</td></tr>";
// lets get each savings from a given timeframe
$qry = "SELECT * FROM emg_shares WHERE id = '$mem_id' AND date > '$begindate' AND date < '$enddate'";
$runit = mysql_query($qry);
while ($row = mysql_fetch_array($runit)) {
$amountsaved = $row['amount'];
$datesaved = $row['date'];
$totalsavings += $amountsaved;
$difference_saved = $totalz - $amountsaved;
//lets display
echo "<tr><td>$datesaved</td><td>$amountsaved</td><td>$difference_saved</td></tr>";
 
}
 
echo "<tr><td></td><td>TOTAL Savings</td><td>$totalsavings</td></tr>
 
 
}
echo "</table>";
 
 
  1.  
  2.  
  3. //fill in with memberid you want to display
  4. $memberid = ;
  5.  
  6. // remember correct format
  7. $begindate = "2009-05-01"
  8. $enddate = "2009-05-31";
  9.  
  10. echo "<table>";
  11. // lets get our members totals
  12. $qryz = "SELECT * FROM emg_shares_totals WHERE id = '$memberid'";
  13. $runanother = mysql_query($qryz);
  14. while ($rowz = mysql_fetch_array($runanother)) {
  15. $mem_id = $rowz['id'];
  16. $mem_name = $rowz['member_name'];
  17. $totalz = $rowz['totals'];
  18.  
  19. echo "<tr><td>Member Num</td><td>Member Name</td><td>Total</td></tr>";
  20. echo "<tr><td>$mem_id</td><td>$mem_name</td><td>$totalz</td></tr>";
  21. echo "<tr><td>Date</td><td>Savings</td><td>Total</td></tr>";
  22. // lets get each savings from a given timeframe
  23. $qry = "SELECT * FROM emg_shares WHERE id = '$mem_id' AND date > '$begindate' AND date < '$enddate'";
  24. $runit = mysql_query($qry);
  25. while ($row = mysql_fetch_array($runit)) {
  26. $amountsaved = $row['amount'];
  27. $datesaved = $row['date'];
  28. $totalsavings += $amountsaved;
  29. $difference_saved = $totalz - $amountsaved;
  30. //lets display
  31. echo "<tr><td>$datesaved</td><td>$amountsaved</td><td>$difference_saved</td></tr>";
  32.  
  33. }
  34.  
  35. echo "<tr><td></td><td>TOTAL Savings</td><td>$totalsavings</td></tr>
  36.  
  37.  
  38. }
  39. echo "</table>";
  40.  
  41.  
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

Post Information

  • Total Posts in this topic: 32 posts
  • Users browsing this forum: No registered users and 66 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.