SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multidimensional array - handling SQL results

    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
    PHP Code:
    $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
    HTML Code:
    <?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; ?>

  2. #2
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,436
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Hi Andy,

    How about something like this?
    PHP Code:
    $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 Code:
    <?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; ?>

  3. #3
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  4. #4
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,436
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Quote Originally Posted by andygout View Post
    That seems to have done the trick - thank you SO much!
    No worries

    Quote Originally Posted by andygout View Post
    I've not come across adding rows into the opening brackets before - could you talk me through that please?
    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:
    PHP Code:
    // 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'
    Quote Originally Posted by andygout View Post
    And I may have a couple more questions to follow, if that's okay?
    Absolutely, any questions just shout.

  5. #5
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  7. #7
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,436
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    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?

  8. #8
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #9
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,436
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    You can get everything you need in one pass:

    Code MySQL:
    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:
    PHP Code:
    $people mysqli_fetch_all($resultMYSQLI_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.

  10. #10
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  11. #11
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,436
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Quote Originally Posted by andygout View Post
    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!
    Cool, no worries

    Quote Originally Posted by andygout View Post
    I'm using 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?
    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.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •