Request for assistance...

If you can do us a



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.

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');

each members totals are gotten after the cumulative weekly savings.

the timestamp column is gotten from converting for example (date column) May 09, 2009 to string using php function strtorime().

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



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

Total - Payments = outstanding in April

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)

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.

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

field which would be 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.

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

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.

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.

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

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

yes am using mysql

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.