Can't wrap my brain around Viral (forwards) Statistics

[thepixel]

I'm trying to pull statistics from 2 tables. Here's what happens:

An affiliate sends out emails to 10 (or so) people.
One of those people sends out an email to 5 people.
One of those 5 sends out a video to 2 people.

I need to track the total resulted from the original 10 sent out, which should be 7.

Here are the table schemas:

CREATE TABLE `msg_from` (
  `id` bigint(25) NOT NULL auto_increment,
  `affiliate_id` bigint(25) default NULL,
  `parent_sender` bigint(25) default NULL,
  `email` varchar(200) default NULL,
  `message` longtext,
  `stamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `msg_to` (
  `id` bigint(25) NOT NULL auto_increment,
  `send_id` bigint(25) default NULL,
  `hash` varchar(12) default NULL,
  `email` varchar(200) default NULL,
  `stamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

msg_from.affiliate_id holds the ID of the affiliate from another table. It's not really relevant here because it's merely there to lookup what text to display on the email.

The messages are split up by each msg_from record where affiliate_id = a specific id and email IS NULL (meaning it draws the email from the affiliate table, where all other instances would have a value)

msg_from.parent_sender stores the msg_to.id when someone forwards a message to other users. They are inserted into the msg_from table with their msg_to.id placed into the parent_sender field.

msg_to.send_id is a reference back to msg_from.id to associate those recipients with it's sender.

Does that make sense? I've been running around with it in my head for 2 days now and starting to get really confused about it all. I think maybe someone that has done something like this might be able to help.

What I'm looking for is 2 queries:
1. Output first set of messages from the msg_to table that were directly sent by the affiliate (10 records from my example)
2. For each item in #1, find out how many records were a result of the email (through a recursive function). So I would pass the ID of the message, and it would then find out how many messages they sent it to (similar to #1, except using msg_from.id->msg_to.send_id as relationship), as well as each how many each recipient sent it to, and so on.

Somewhere in there I know the parent_sender is important. Like I said, I'm so deep into this thing I can't step back and see the big picture. Please help!

 

[DonQuichote]

May I point you at a tutorial about this subject?

http://www.sitepoint.com/article/hierarchical-data-database/

Your more traditional way has its drawbacks. Recursive SQL queries are not possible. Off course, you can write a stored procedure, but you may as well fire a lot of queries from PHP. If the tables are not extremely huge and are properly indexed, performance should be OK.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)