PHP, MySQL, and Excel, OH MY!!!

Factual Questions
1

I’m working (or trying to, at least) with a PHP web page and a server-hosted MySQL database. I have a main table and several sub-tables all exporting to a single Excel spreadsheet (don’t ask why). I end up with one row for each record in the main table and one column for every field in every table (main and sub). Since many of the fields in the sub-tables are the same, I’m wondering if it’s possible to combine/group those columns to keep the number of columns to a minimum.

The information is coming from several variations of a business reply card (BRC) As some of the questions will be changing from time to time, if I don’t figure something out we’ll soon have a two-mile wide spreadsheet.

The BRCs basically ask for name and address, and have checkboxes. For simplicity, assume the checkboxes just ask “do you own the following pets?” So, I’ve got two text fields and varying numbers of checkboxes to work with. Here’s the basic structure of my tables (all foreign keys relate back to the main table’s primary key):

tblMain
PrimaryKey:
Name:
Address:

tblBRC1
PrimaryKey:
ForeignKey:
Dogs
Cats
Ferrets

tblBRC2
PrimaryKey:
ForeignKey:
Dogs
Cats
Horses

tblBRC3
PrimaryKey:
ForeignKey:
Dogs
Cats
Ferrets
Cows
Armadillos
Platypuses

Here’s the basics of my query:

SELECT tblMain.Name, tblMain.Address, *.tblBRC1, *.tblBRC2, *.tblBRC3 FROM (((tblMain LEFT JOIN tblBRC1 ON tblMain.PrimaryKey = tblBRC1.ForeignKEY) LEFT JOIN tblMain LEFT JOIN tblBRC2 ON tblMain.PrimaryKey = tblBRC2.ForeignKEY) LEFT JOIN tblMain LEFT JOIN tblBRC3 ON tblMain.PrimaryKey = tblBRC3.ForeignKEY)

I’ve tested and retested this (hopefully I haven’t made a mistake in rewriting it here) and it works fine. However, I end up with a spreadsheet with the following columns:


Name  Address  Dogs  Cats  Ferrets  Dogs  Cats  Horses  Dogs  Cats  Ferrets  Cows  Armadillos  Platypuses

What I want is to end up with a spreadsheet that has these for columns:


Name  Address  Dogs  Cats  Ferrets  Horses  Cows  Armadillos  Platypuses

And so as time goes on and we add more and different questions to the BRCs, the number of columns only expands when there is a new category, not a new BRC.

If this makes any sense at all, can anyone offer some suggestions?

Thanks!

Rhythm

2

Simplest thing to do is to explicitly select only those columns you want. Don’t select the dups.

3

Sorry for the confusion. I need all of the information that is showing up in the separate columns. I’m hoping to find a way to put it all in a single column.

The example in the OP assumes three BRCs. Each BRC asks for name and address (this information goes into tblMain) and then asks for someone to check off (from a list) the pets they own. The sub-tables, tblBRC1,2&3 are linked to tblMain’s primary key, and have fields for each animal.

Imagine three BRCs being returned:
[ul][li]BRC1 has a name, address, and “dogs” checked off. [/li][li]BRC2 has name,address, “Dogs” and “Horses” checked off. [/li][li]BRC3 has “Dogs” and " Platypuses" checked off. [/li][/ul]

The spreadsheet is going to have a separate column for BRC1.dogs, BRC2.dogs, and BRC3.dogs. It’s all neatly organized by name, but there’s no way to easily see all the people who checked off dogs—there isn’t a single column.