473,326 Members | 2,125 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

PHP & MySQL database retrieval question

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
3 2869
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: James | last post by:
HI, I'm looking for a script that will allow users/admins to have a one click backup solution for a MYSQL Database.. 'BACK DATABASE' button, click and its done... The a restore option, that...
5
by: sandy | last post by:
Hi All, I am a newbie to MySQL and Python. At the first place, I would like to know what are the general performance issues (if any) of using MySQL with Python. By performance, I wanted to...
4
by: Shaun Campbell | last post by:
I know you're going to say search the mail archives, but I have and I can't find an answer to my simple question of is there an SQL statement to load an image in a .jpg file into a database table?...
7
by: Will | last post by:
On the subject of Data Warehouses, Data Cubes & OLAP…. I would like to speak frankly about Data Warehouses, Data Cubes and OLAP (on-line analytical processing). Has it dawned on anyone else...
4
by: MLH | last post by:
A programmer developed an AMP (Apache/MySQL/PHP) application for me. When he was done, he sent me the PHP files and the MySQL dump file. Now, when I connect to the application on my LAN using...
2
by: pmz | last post by:
Dear Group, I'm connecting in C# with remote (BSD) MySQL server with ODBC Driver, and I'm trying to find the best sollution in such problem: As I've read on MySQL manual, they have suggested...
0
by: dtsearch | last post by:
A new beta build offers 64-bit developer access to dtSearch's "terabyte indexer," and preliminary MS Word 2007 and Excel 2007 support (in both 64-bit and 32-bit versions) BETHESDA, MD (July 22,...
0
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted...
16
by: William Gill | last post by:
I am writing some new editing pages for a db using php. I want to use an object approach for the underlying MySQL tables, and the edit page will display related data from several tables. I am...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.