Link to home
Start Free TrialLog in
Avatar of jej07
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-Disposition: attachment; filename=$fileName.xls");
    print "$header\n$data";
Avatar of karunamoorthy
karunamoorthy
Flag of India image

Both option1 and option 2 are wrong,
the correct one is

registration
| name        | registration |
| John Doe    | 52           |
| John Doe    | 54           |
| Jane Doe    | 52           |
| Jane Doe    | 53           |
| Jane Doe    | 54           |
Any comments pl.
Avatar of jej07
jej07

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.
SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jej07

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            |
Avatar of jej07

ASKER

Thank you very much for your help!