jej07
asked on
I need help with my Database architecture and exporting to Excel
I'm setting up two tables; one that contains events and one that contains event registrations. My thought is to give each event a unique key and either implode the data and insert it into one column in the registrations table (Option1 below). Or, insert each event key in a separate column of the registrations table (option2 below).
I'm not really sure which method is best; but I do know every two weeks I'll need to export the registrations to Excel. And the end user requesting the Excel document will need a separate column for each event, and will want the column headers to show the event name, not the unique key.
Using the code below, I can export the registrations table; but I can't figure out how to replace the uniquieID with the event name.
example:
events
| description | date | uniqueID |
| Fun Event 1 | 2012-08-16 | 52 |
| Fun Event 2 | 2012-08-17 | 53 |
| Fun Event 3 | 2012-08-18 | 54 |
Option1
registrations
| name | registrations |
| John Doe | 52,54 |
| Jane Doe | 52,53,54 |
Option2
registrations
| name | 52 | 53 | 54 |
| John Doe | y | | y |
| Jane Doe | y | y | y |
Desired export
| name | Fun Event 1 | Fun Event 2 | Fun Event 3 |
| John Doe | y | | y |
| Jane Doe | y | y | y |
$export = $DB->query("SELECT * FROM `registrations` ORDER BY `name` DESC");
$fields = mysql_num_fields($export);
for($i = 0; $i < $fields; $i++){
$header .= mysql_field_name($export,$ i) . "\t";
}
while($row = mysql_fetch_assoc($export) )
{
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r","",$data) ;
if ($data == "") {
$data = "\n(0) Records Found!\n";
}
header("Pragma: public");
header("Expires: 0");
header("Content-Type: application/vnd.ms-excel") ;
header("Content-Dispositio n: attachment; filename=$fileName.xls");
print "$header\n$data";
I'm not really sure which method is best; but I do know every two weeks I'll need to export the registrations to Excel. And the end user requesting the Excel document will need a separate column for each event, and will want the column headers to show the event name, not the unique key.
Using the code below, I can export the registrations table; but I can't figure out how to replace the uniquieID with the event name.
example:
events
| description | date | uniqueID |
| Fun Event 1 | 2012-08-16 | 52 |
| Fun Event 2 | 2012-08-17 | 53 |
| Fun Event 3 | 2012-08-18 | 54 |
Option1
registrations
| name | registrations |
| John Doe | 52,54 |
| Jane Doe | 52,53,54 |
Option2
registrations
| name | 52 | 53 | 54 |
| John Doe | y | | y |
| Jane Doe | y | y | y |
Desired export
| name | Fun Event 1 | Fun Event 2 | Fun Event 3 |
| John Doe | y | | y |
| Jane Doe | y | y | y |
$export = $DB->query("SELECT * FROM `registrations` ORDER BY `name` DESC");
$fields = mysql_num_fields($export);
for($i = 0; $i < $fields; $i++){
$header .= mysql_field_name($export,$
}
while($row = mysql_fetch_assoc($export)
{
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r","",$data)
if ($data == "") {
$data = "\n(0) Records Found!\n";
}
header("Pragma: public");
header("Expires: 0");
header("Content-Type: application/vnd.ms-excel")
header("Content-Dispositio
print "$header\n$data";
for creating query check the URL:
http://stackoverflow.com/questions/6158230/mysql-pivot-table-with-dynamic-headers-based-on-single-column-data
http://stackoverflow.com/questions/6158230/mysql-pivot-table-with-dynamic-headers-based-on-single-column-data
Any comments pl.
ASKER
Thank you very much for your reply. I had a feeling my options might not be the best way to go about things. Just to help me better understand, what is the advantage to creating separate rows for each registration, rather than imploding the data?
I think the URL sent is exactly what I needed. I need to play with it a little more to see if I can get the logic figured out.
I think the URL sent is exactly what I needed. I need to play with it a little more to see if I can get the logic figured out.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow, that's a lot to digest. A junction table may be a better solution so I can add contact information for each registrant. If I understand correctly, the registrations and events table would have unique rows. Then I would need a third table matching up the other two?
I haven't been able to get through all of the URLs you both referenced yet, so I'm not sure if this is addressed or not. But I'm not sure right off how I would select the values so they can be exported correctly.
BTW @ Ray_Paseur, I download the first four chapters of the phpmysql4 book and have found it very informative. I do plan on downloading the entire book after I've finished reading those chapters.
events
| description | date | uniqueID |
| Fun Event 1 | 2012-08-16 | 52 |
| Fun Event 2 | 2012-08-17 | 53 |
| Fun Event 3 | 2012-08-18 | 54 |
registrations
id | name | address | city |
1 | John Doe | 101 State Street | Chicago |
2 | Jane Doe | 1512 Elm Street | New York |
event_registrations
registration_id | event_id |
1 | 52 |
1 | 54 |
2 | 52 |
2 | 53 |
2 | 54 |
I haven't been able to get through all of the URLs you both referenced yet, so I'm not sure if this is addressed or not. But I'm not sure right off how I would select the values so they can be exported correctly.
BTW @ Ray_Paseur, I download the first four chapters of the phpmysql4 book and have found it very informative. I do plan on downloading the entire book after I've finished reading those chapters.
events
| description | date | uniqueID |
| Fun Event 1 | 2012-08-16 | 52 |
| Fun Event 2 | 2012-08-17 | 53 |
| Fun Event 3 | 2012-08-18 | 54 |
registrations
id | name | address | city |
1 | John Doe | 101 State Street | Chicago |
2 | Jane Doe | 1512 Elm Street | New York |
event_registrations
registration_id | event_id |
1 | 52 |
1 | 54 |
2 | 52 |
2 | 53 |
2 | 54 |
ASKER
Thank you very much for your help!
the correct one is
registration
| name | registration |
| John Doe | 52 |
| John Doe | 54 |
| Jane Doe | 52 |
| Jane Doe | 53 |
| Jane Doe | 54 |