Hi,
Pretty new to PHP/MySQL, but I have some programming experience in a
past life. Hopefully you can help me out as I learn PHP/MySQL! :-)
I have a MySQL database that includes customer records, along with the
source of that record broken down into two fields: the affiliate #
that customer originated from, and a subID code. Each affiliate can
have multiple subID codes to allow them to know how they sent us the
traffic, what's working, and what is not working. Below is an example
of what you might find in the DB:
Customer ID, Name, Address, City (etc), AffiliateID, SubID
1, Tom, 123 Elm St., Anytown (etc), 1, a
2, Dale, 6644 Elm St., Anytown (etc), 1, b
3, Steve, 552 Elm St., Anytown (etc), 1, h
4, Larry, 4333 Elm St., Anytown (etc), 1, b
5, Joe, 223 Elm St., Anytown (etc), 1, c
6, Jake, 333 Elm St., Anytown (etc), 1, a
Say that Affiliate #1 sent us 1,000 customers in November, kind of
like the example above. I can tell that easily enough by doing a
select count(*) where affiliateID = '1'. But, my affiliates would like
to know the breakdown of the 1,000 customers. How many came from subID
a, how many came from subID b, etc.
When they provide a starting date of 2003-11-01 and an ending date of
2003-11-30 in a web-based form, I'd like to give them something like
what you see below in a nicely formatted table:
Totals for Affiliate #1 for 11-1-2003 through 11-30-2003 = 1,000
From SubID a = 450
From subID b = 200
From subID c = 300
From subID h = 50
Notice how there's no subID d, e, f, or g. That's because this
particular month, affiliate #1 did not provide any customers from
those subIDs that. (for the sake of argument, the subID code could
represent a Web site affiliate #1 owns, or different e-mails to an
opt-in list).
My theory on how to do this would be to
1. get a list of distinct subIDs for the time between the starting and
ending dates so that I know which ones have at least one record for
that time period.
2. Insert those subIDs into an array
3. Do a select count(*) where subID = [arrayElement] between the
starting and ending date and write that data to a row in a table.
Does this sound correct? If so, can anyone point me to a code sample
that does something like this? I know it can't be too difficult, but
I'm stuck on how to implement it. Once I see a couple of code samples,
I know I'll be able to tweak it to my specific situation.
Thanks in advance for any help you might be able to provide!
=- Eric -=
Pretty new to PHP/MySQL, but I have some programming experience in a
past life. Hopefully you can help me out as I learn PHP/MySQL! :-)
I have a MySQL database that includes customer records, along with the
source of that record broken down into two fields: the affiliate #
that customer originated from, and a subID code. Each affiliate can
have multiple subID codes to allow them to know how they sent us the
traffic, what's working, and what is not working. Below is an example
of what you might find in the DB:
Customer ID, Name, Address, City (etc), AffiliateID, SubID
1, Tom, 123 Elm St., Anytown (etc), 1, a
2, Dale, 6644 Elm St., Anytown (etc), 1, b
3, Steve, 552 Elm St., Anytown (etc), 1, h
4, Larry, 4333 Elm St., Anytown (etc), 1, b
5, Joe, 223 Elm St., Anytown (etc), 1, c
6, Jake, 333 Elm St., Anytown (etc), 1, a
Say that Affiliate #1 sent us 1,000 customers in November, kind of
like the example above. I can tell that easily enough by doing a
select count(*) where affiliateID = '1'. But, my affiliates would like
to know the breakdown of the 1,000 customers. How many came from subID
a, how many came from subID b, etc.
When they provide a starting date of 2003-11-01 and an ending date of
2003-11-30 in a web-based form, I'd like to give them something like
what you see below in a nicely formatted table:
Totals for Affiliate #1 for 11-1-2003 through 11-30-2003 = 1,000
From SubID a = 450
From subID b = 200
From subID c = 300
From subID h = 50
Notice how there's no subID d, e, f, or g. That's because this
particular month, affiliate #1 did not provide any customers from
those subIDs that. (for the sake of argument, the subID code could
represent a Web site affiliate #1 owns, or different e-mails to an
opt-in list).
My theory on how to do this would be to
1. get a list of distinct subIDs for the time between the starting and
ending dates so that I know which ones have at least one record for
that time period.
2. Insert those subIDs into an array
3. Do a select count(*) where subID = [arrayElement] between the
starting and ending date and write that data to a row in a table.
Does this sound correct? If so, can anyone point me to a code sample
that does something like this? I know it can't be too difficult, but
I'm stuck on how to implement it. Once I see a couple of code samples,
I know I'll be able to tweak it to my specific situation.
Thanks in advance for any help you might be able to provide!
=- Eric -=
Comment