0

The array below is a sample of data returned from a MySQL query for all training records that are due within X interval (1 week, 1 month, etc.).

array [
    0 => [
        'company_id' => '10',
        'date' => '2016-11-11'
        'company' => ['name' => 'Company 1'],
        'user' => ['first_name' => 'John', 'last_name' => 'Doe'],
        'course' => ['name' => 'Course 1'],
        'department' => ['name' => 'Dep 1'],
    ],
    1 => [
        'company_id' => '11',
        'date' => '2016-11-30',
        'company' => ['name' => 'Company 2'],
        'user' => ['first_name' => 'Mark', 'last_name' => 'Smith'],
        'course' => ['name' => 'Course 1'],
        'department' => ['name' => 'Dep 1'],
    ],
    2 => [
        'company_id' => '11',
        'date' => '2016-12-07',
        'company' => ['name' => 'Company 2'],
        'user' => ['first_name' => 'Mark', 'last_name' => 'Smith'],
        'course' => ['name' => 'Course 2'],
        'department' => ['name' => 'Dep 1'],
    ]

What I'm looking to do is select all records for each company and send a single email to the company manager containing the details of all training records for that company.

What would be the best way to do this?

What I was thinking was to create a new array containing just the company id's and then loop through that array and do an array_search on the original array for all records for that company.

Am I on the right track or am I over complicating things?

I can work out the sending of the emails myself... I just need to get the info into an array I can work with.

Something like this:

$records = [
    'company_id' => '10'
    'training_details' => [
        0 => [
            'user' => 'John Doe',
            'course' => 'Course 1',
            'department' => 'Dep 1',
        ]
    ],
    'company_id' => '11'
    'training_details' => [
        0 => [
            'user' => 'Mark Smith',
            'course' => 'Course 1',
            'department' => 'Dep 1',
            'date' => '2016-11-30',
        ],
        1 => [
            'user' => 'Mark Smith',
            'course' => 'Course 2',
            'department' => 'Dep 1',
            'date' => '2016-12-07',
        ],
    ],
];
Keith McLaughlin
  • 3
  • 3
  • if the query orders by company_id, you should be able to just loop the results once sending the mail. i see no reason to create a new array, its just more work –  Nov 09 '16 at 22:26
  • @Dagon I don't get what you mean. Can you provide some code as an example? I only want to send 1 email per company with the email containing all training records. – Keith McLaughlin Nov 09 '16 at 22:32
  • foreach loop ... create the email content as you go through each record. every time the company_id changes, send an email (previous company) and start creating the next one. –  Nov 09 '16 at 22:32
  • If you use PDO - try to fetch the results with `PDO::FETCH_GROUP`. – Paul Spiegel Nov 09 '16 at 22:46

0 Answers0