Arrays, two tables, outputting to table...

[mattvit]

Hi all, I'll try to make this as succinct and simple as possible. Thanks in advance for reading / helping, I do always appreciate it greatly.

 

I have two tables (edit: in the mysql database) - one has member details and their total credits. The other is an 'events' table which gives a more detailed history of how they gained those credits - including whether it was from a referral.

 

Basically, I'd like to make an output table so that a referrer can know how much each person he/she referred is earning them - the referrer gets a 5% cut of everything the referred is earning.

 

Events look like this in the database:

member id of who the event most relates to, event description for aesthetic output (irrelevant here), event type (referral earning), earning amount during that instance (ie, a small amount).

 

Ideally the table will only show two variables. The first name of the member based on member_id, and the total they have earned.

 

 

Jim's Referrals Table:

Sally | 5 credits

Matt  | 4 credits

 

 

The columns relevant are:

TABLE MEMBERS:

- member_id

- totalcredits

 

TABLE EVENTS:

- member_id

- eventtype (ie, referral)

- amount (ie, amount earned on that event by referrer).

 

 

I'm not asking for you to write the whole thing for me!!!  That'd be lazy of me.

I would say I'm leaving 'beginner' status when it comes to PHP. One of the few things I STILL have not picked up on how to do well is arrays, which are needed here I think.

 

 

Could anyone give me some kind of pseudo-script on how they would do this? Then I can write it myself and post back if I have troubles with it. A bit of a pointer on the array (if you suggest one) would be fantastic, too.

 

Again thank you in advance!!! Please ask if you need me to clarify any part of this (or all of it..!).

 

edit: just realised I'll need another column in the events mysql table which indicates who the referred person is, because currently when the referrer earns 5%, it only says who it is in the aesthetic eventdescription column. Given that I add this column, what would you recommend to do what I need to do? (that is, create a table, each row contains the name of the referred person and the total earnings they've contributed to the referrer).

[mattvit]

edit: Ignore the above - that makes it too confusing. I can do everything except what follows. Any help would be great. 

 

Still can't figure out how to do this one. I'm guessing I'd need dynamic arrays or something. Here's a pseudo script of how I'd want to do it, could anyone give the array translation of this pseudo?? Thanks.

 

 

from EVENTS: get member_id and newcredit amount

member_id = [1], totalcredit = totalcredit + newcredit

 

from EVENTS: get member_id and newcredit amount

have we got the member_id already? then totalcredit(for that member_id) = totalcredit + newcredit

if this is a new member_id, then

member_id = [2], totalcredit = totalcredit + newcredit

 

from EVENTS: get member_id and newcredit amount

have we got the member_id already? then totalcredit(for that member_id) = totalcredit + newcredit

if this is a new member_id, then

member_id = [3], totalcredit = totalcredit + newcredit

 

and so on, etc

 

How would I do this? Specifically, how would I set an array which stores the unqiue member_id and his relevant total credits, adding to the credits each time there's an occurrence of his member_id?

 

Urgh this is giving me a headache!! 

[mattvit]

Final post! Just to give a strong idea of what I'm trying to do... Here's a snippet which I know won't work but gives myself the pseudo-framework to work on.

 

while($row = mysql_fetch_array($result)) {
$member_id = $row['member_id'];
$newcredits = $row['earned'];
$membercredits[$member_id]] = $membercredits[$member_id]] + $newcredits;
}

 

So some more questions - first, how do I make that work, and second, I'll need the variables $member_id and the total credits now associated with that, after going through and summarizing all credits earned.

 

p.s. I just realised that it might be easier to have a field in the Members table which gives the total earnings the member has given the referrer. Would this be the best solution? The script is not currently in public use so it wouldn't be a problem...

[Psycho]

You're losing me. I understand that you want to get a list of users, that were referred by a specific user, and the sum of their credits. All you need is a single query to get the data you need. You don't need any arrays.

 

I could provide a sample query, but the one thing that doesn't make sense is your reference above to "Jim's Referrals Table". You shouldn't have a different table for each person's referrals. Just use one table with a column for referrerID. You are makign this way harder than it needs to be. Assuming you had a referrerID column you would be able to get the data you want using a query such as

 

SELECT members.name, SUM(events.amount) as earned
FROM members
JOIN events USING member_id
WHERE members.referrerID = $referrerID
GROUP BY members.memberID

[mattvit]

Perfect, thank you very much. You're absolutely correct, I'm doing this the way-too-hard-way. All I needed to do, was add a field / column on the referred member's details which says how much they've earned the referrer.

 

Thanks for taking the time to read through my gibberish!

[Psycho]

Perfect, thank you very much. You're absolutely correct, I'm doing this the way-too-hard-way. All I needed to do, was add a field / column on the referred member's details which says how much they've earned the referrer.

 

Um, no, that doesn't make sense either. That requires you to update that value whenever the member has an event that changes their credits. There is no need for that as long as you have a record of all the credits for the members. You can then determine the totals dynamically. You should have to track the ame values (or even the sum of the same values) in different places.