By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,404 Members | 2,145 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,404 IT Pros & Developers. It's quick & easy.

PHP & MySQL database retrieval question

P: n/a
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 -=
Jul 17 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
el********@hotmail.com (Eric Linders) writes:
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.
You can do this with GROUP BY.
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


Does your table contain dates? The example you gave doesn't show
any.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Jul 17 '05 #2

P: n/a
mf***@fuhr.org (Michael Fuhr) wrote in message news:<3f**********@omega.dimensional.com>...
el********@hotmail.com (Eric Linders) writes:
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.
You can do this with GROUP BY.


Excellent. Do you happen to know of an online example where I could
see how this is done?
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


Does your table contain dates? The example you gave doesn't show
any.


Yes, sorry for for not including all fields. I wanted to give you less
to have to read through, but I should have included that. :-) The date
is stored in yyyy-mm-dd format (2003-12-25).
Jul 17 '05 #3

P: n/a
el********@hotmail.com (Eric Linders) writes:
mf***@fuhr.org (Michael Fuhr) wrote in message news:<3f**********@omega.dimensional.com>...
el********@hotmail.com (Eric Linders) writes:
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.


You can do this with GROUP BY.


Excellent. Do you happen to know of an online example where I could
see how this is done?


Any decent SQL tutorial should have examples of using GROUP BY.
The MySQL and PostgreSQL documentation both mention it.

http://www.mysql.com/documentation/m...#Counting_rows
http://www.postgresql.org/docs/curre...#QUERIES-GROUP

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Jul 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.