Say I want to create a page for each company, which in turn will list each employee and each of the duties they perform. I want (for Holiday Inn):-
Michelle Hopkins … Duty Manager / Accountant / Front Desk
Anthony Archer … Night porter / Room service
Hannah Newell … Reservations / Bar Service
But the below outputs:-
Michelle Hopkins … Reservations / Bar Service
Anthony Archer … Reservations / Bar Service
Hannah Newell … Reservations / Bar Service
What is the correct syntax for storing the ‘employee_duties’ array in the ‘employee_person_duties’ array so that it can be displayed as above?
And is it possible to do this using a single SQL SELECT statement? NB. I want the option to display the duty results in a form other than a concatenated string (i.e. a vertical list, in separate table cells, etc.), so I don’t think the SQL GROUP_CONCAT command will be the solution here.
Thanks!
COMPANY
company company_id
Holiday Inn 1
Travelodge 2
Buena Vista 3
COMPANYYPERSON
companyid personid duty
1 14 Duty Manager
1 14 Accountant
1 14 Front Desk
1 15 Night porter
1 15 Room service
1 16 Reservations
1 16 Bar service
PERSON
person_id person_name
14 Michelle Hopkins
15 Anthony Archer
16 Hannah Newell
PHP
$sql = "SELECT DISTINCT person_id, person_name
FROM companyperson
INNER JOIN person
ON personid = person_id
WHERE companyid = '$company_id' ";
while ($row = mysqli_fetch_array($result))
{
$employee_people[] = array('person_id' => $row['person_id'], 'person_name' => $row['person_name']);
}
foreach($employee_people as $employee_person)
{
$person_id = $employee_person ['person_id'];
$sql = "SELECT duty
FROM companyperson
INNER JOIN person
ON personid = person_id
WHERE companyid = '$company_id'
AND person_id = '$person_id' ";
$employee_duties = array();
while ($row = mysqli_fetch_array($result))
{
$employee_duties[] = array('duty' => $row[duty']);
}
$employee_person_duties[] = array($employee_duties, 'person_name' => $employee_person['person_name'], 'person_id' => $employee_person['person_last_name']);
}
HTML
<?php foreach ($employee_person_duties as $employee_person_duty): ?>
<a href = "/person/<?php htmlout($employee_person_duty ['person_url']); ?>">
<?php htmlout($employee_person_duty ['person_name']); ?></a>
.....
<?php $duties_array = array(); foreach ($employee_duties as $employee_duty){
$duties_array[] = htmlspecialchars($employee_duty ['duty'], ENT_QUOTES, 'UTF-8');
} echo implode(" / ", $duties_array); ?>
<?php endforeach; ?>
Hi Andy,
How about something like this?
$sql = "SELECT DISTINCT person_id, person_name
FROM companyperson
INNER JOIN person
ON personid = person_id
WHERE companyid = '$company_id' ";
$employees = array();
while ($row = mysqli_fetch_array($result))
{
$employees[$row['person_id']] = array('person_name' => $row['person_name'], 'duties' => array());
}
$sql = "SELECT * FROM companyperson WHERE companyid = '$company_id'";
while ($row = mysqli_fetch_array($result))
{
$employees[$row['personid']]['duties'][] = $row['duty'];
}
<?php foreach ($employees as $employee): ?>
<a href = "/person/<?php htmlout($employee['person_url']); ?>">
<?php htmlout($employee['person_name']); ?></a>
.....
<?php echo implode(" / ", $employee['duties']); ?>
<?php endforeach; ?>
That seems to have done the trick - thank you SO much!
I’ve not come across adding rows into the opening brackets before - could you talk me through that please?
And I may have a couple more questions to follow, if that’s okay?
Thanks again!
No worries 
Sure, well basically with the first query it creates a new entry in the array using the person_id as a key. When we loop through the second query, we use the personid again to add each duty to a sub-array of the correct entry. Using the brackets to add elements to an array works like this:
// Create an empty array and create a new key called Ford, assigning an array as the value
$cars = array();
$cars['Ford'] = array('Escort', 'Fiesta', 'Granada');
// The above is the same as:
$cars = array('Ford' => array('Escort', 'Fiesta', 'Granada'));
// Using empty brackets, I can create a new entry in the Ford array
$cars['Ford'][] = 'Ka';
Absolutely, any questions just shout.
Yes, that makes complete sense but I would never have arrived at that myself, nor could seem to find that explanation published anywhere else, so i really can’t thank you enough.
I’ve had a little play around with the code and think I’ll be okay from here on in for what I need to do.
You’re a lifesaver! Thanks again,
Andy
How would I go about adding a single extra row (rather than a full array) to an array (using the example posted on 01 Jul at 21:36 and let’s say the database is designed to guarantee that each person only has one duty)?
I cannot use INNER JOIN as some of the employees will not yet necessarily have an assigned duty and therefore no results will be returned for that employee as the INNER JOIN cannot be performed and so an empty set would be returned.
I could always use the above method but seems a bit excessive when I know I’m only calling a single row rather than a full array.
Any ideas? Cheers! Andy
Hi Andy,
In the case where an employee does not yet have a duty, do they still have an entry in the companyperson table, but with a NULL duty column or something?
Sorry, let me rephrase. Say I want to display an array of people, and each row will also display the company they work for (people can only work for one company, so I only need to call a single result).
Some people may have an association to a company (via COMPANYPERSON), while others may not (hence INNER JOIN not being an option as it will just return an empty set if there is no INNER JOIN link so one of the entries).
So how do I perform a separate function to call the company and add that extra row to each row in an array of people? Sorry if I’m not explaining this very well.
You can get everything you need in one pass:
SELECT person.*, companyperson.*
FROM person
LEFT OUTER JOIN companyperson
ON person_id = personid;
Using a left outer join will return null values for the columns from companyperson if there is no matching record. Then you just need to check if those columns are set when trying to use the data:
$people = mysqli_fetch_all($result, MYSQLI_ASSOC);
foreach ($people as $person) {
echo 'Name: ' . $person['person_name'];
if ($person['duty']) {
echo 'Duty: ' . $person['duty'];
}
}
Note it’s not a good idea to use mysqli_fetch_all if there are a lot of records.
Ah, I’d heard OUTER JOINs mentioned but never needed to use them, so this is an excellent introduction and works perfectly for my needs - thank you again!
I’m using
mysqli_fetch_array($result)
to call my results (and there are likely to be a lot in the future). Is that command suitable do you think? How could it prove detrimental?
Cheers! Andy
Cool, no worries
I don’t think you’d run into any problems necessarily, it just depends on your needs and/or preferences. If the data is for displaying to the user, I’d use fetch_all to get all the data I need and pass it to my code that handles the output so I can keep DB functions/logic separate from view logic, but if it was some kind of data processing script that was dealing with a lot of data and was resource-intensive then I might fetch the data a row at a time as I process it.