I have a JSON file which I will be running through a PHP program to populate a MySQL database.
I have most of the database tables and columns in place, but I am having a logic issue.
I am dealing with meetings, and people going to the meetings.
Each person is going to multiple unique meetings, and each meeting has multiple people attending.
Each person has a unique ID, and each meeting has a unique ID.
My issue:
I know I can't put multiple IDs in a single row for each person, so I am trying to think of a way to relate a person's ID to multiple meeting IDs, and vice-versa.
Ultimately, I want to be able to run queries that will return all the meetings that someone is attending, and the people attending meetings.
Here is an example of the info I have for a person:
"District": "Massachusetts District 1",
"Email": null,
"FirstName": "Lynn",
"Id": 14869,
"LastName": "Aaronson",
"MeetingIds": [
15650,
15651,
15652,
15653,
15654,
15655,
15656,
15657,
15658,
15659,
15660
]
},
This is a meeting:
{
"AddressLine1": "717 Hart Senate Office Building",
"AddressLine2": "120 Constitution Avenue, NE",
"CongressPersonID": 8766,
"CongressPersonStateDistrict": "WI",
"ConstituentIds": [
14810,
14811
],
"End": "2014-05-08T10:15:00.0000000-04:00",
"Id": 15898,
"Location": "SH717 ",
"MeetingWith": "Kathleen Laird",
"Name": "Sen. Tammy Baldwin",
"PhoneNumber": "(202) 224-5653",
"Start": "2014-05-08T10:00:00.0000000-04:00",
"Status": "Confirmed"
},
Expanding on my comment, the basic principles would be like this. Now keep in mind that this is untested code that I wrote off the top of my head, and I'm assuming a lot here, like using PDO and prepared statements and that you already have queries for inserting the data in the people and meetings tables, not to mention the structure of your JSON data.
You'll have to adjust this to make it work for your exact situation, this example is just to illustrate the concepts of how I would solve this problem.
Import script:
$data = json_decode($json_data, true);
foreach ($data['meetings'] as $meeting) {
// insert meeting details into `meetings` table
$stmt = $db-prepare('INSERT INTO `meetings` ...');
$stmt->execute(array(...));
}
foreach ($data['people'] as $person) {
// insert person details into `people` table
$stmt = $db->prepare('INSERT INTO `people` ...');
$stmt->execute(array(...));
foreach ($person['MeetingIds'] as $meetingId) {
// insert attendance into `attendees` table
$stmt = $db->prepare('INSERT INTO `attendees` (`person_id`, `meeting_id`) VALUES (:person_id, :meeting_id)');
$stmt->execute(array(':person_id' => $person['Id'], ':meeting_id' => $meetingId));
}
}
Then where you need to fetch the details:
// fetch a specific person
$stmt = $db->prepare('SELECT * FROM `people` WHERE `id` = :id');
$stmt->execute(array(':id' => $person_id));
$person = $stmt->fetch(PDO::FETCH_ASSOC);
// fetch all meetings for this person
$stmt2 = $db->prepare('
SELECT `meetings`.*
FROM `attendees`
INNER JOIN `meetings`
ON `meetings`.`id` = `attendees`.`meeting_id`
WHERE `attendees`.`person_id` = :person_id
');
$stmt2->execute(array(':person_id' => $person['id']));
$meetings = $stmt2->fetchAll(PDO::FETCH_ASSOC);
You can do the exact same thing in reverse if you have a meeting and you need to find all people that attended it.
I didn't test this JOIN on an actual database, so don't be surprised if it doesn't work exactly like I wrote it. I'm sure someone will correct me if it's wrong :-)