Restructure array from sql query so that I can echo proper values

Question

I'm trying to get the content I need from a joined query and properly use values as an array key so that I can build some DIV lists properly

My php query and array:

$getTickers = "
    SELECT d.ID as displayID, d.display_name as display, l.location_name as locationName, d.location_id as location, t.id as ticker, tc.id as contentID, tc.content
        FROM displays d
            INNER JOIN locations l on d.location_id = l.id
            INNER JOIN tickers t on d.id = t.display_id
            INNER JOIN ticker_content tc on t.id = tc.ticker_id;
";

$tickers = $mysqlConn->query($getTickers);

$tickerDisplays = array();
foreach($tickers as $subArray) {
    if(!array_key_exists($subArray['displayID'], $tickerDisplays)) {
        $tickerDisplays[$subArray['displayID']] = array();

    }
    // here you add `display_name` under key `display_id`
    $tickerDisplays[$subArray['displayID']][$subArray['location']] = $subArray['display'];
}

All examples and code below, but I don't need the html structure help with this, just how to restructure the array/key to give me the desired results and how I should loop them on the front end.

I'm getting 4 divs as I expect right now (one for each unique display/location) but I need to figure out how to correcty arrange it so I can echo the DIsplay name as the h4, the location as h5, and then each content in my list

So the query result gives me this:

displayID | display |  locationName  | location | ticker | contentID |    content         | 

  1         Office      Building 4       4         1          1         testing content
  2         Lobby       Building 4       4         2          2         testing content 2
  3         Lobby       Building 1       1         3          3         testing content 3
  4         Office      Building 1       1         4          4         testing content 4
  4         Office      Building 1       1         4          5         testing content again

I'm trying to loop on this with the expected result of having a a div for each location/display combo like so:

OFFICE           
Building 4

testing content

---------------

LOBBY           
Building 4

testing content 2

------------------

LOBBY           
Building 1

testing content 3

------------------


OFFICE 
Building 1

testing content 4
testing content again

----------------------

Here's the way I'm currently trying to loop that

<?php foreach($tickerDisplays as $key => $ticker):?>

        <h4><?php echo $key ?></h4>     //so this should be the display Name (office, lobby)
        <h5><?php echo //location?></h5> //this should be the location name (Building 1, Building 4)

        //This will be another foreach for any content associated with the location/display
        <ul class="tickerContent">
            <li>
        </ul>
      </div>
    </div>
<?php endforeach;?>

I am guessing from this that displayID will show up in the query result multiple times because of the joins? — ryantxr
– ryantxr, Commented Sep 12, 2018 at 14:44
Yes that's correct, since multiple records of content will be assigned to one display — Geoff_S
– Geoff_S, Commented Sep 12, 2018 at 14:45
It would be helpful if you could add a raw dump of the query result into your question. — ryantxr
– ryantxr, Commented Sep 12, 2018 at 14:54
The query results above are the actual ones, you mean you want a dump from workbench? — Geoff_S
– Geoff_S, Commented Sep 12, 2018 at 14:55
The raw dump from $mysqlConn->query($getTickers); Is that the table you posted in the question? If so, can you label the columns. I don't want to assume. — ryantxr
– ryantxr, Commented Sep 12, 2018 at 14:58

ryantxr · Accepted Answer · 2018-09-12 15:58:05Z

The approach here is to make a child array for each display line to contain all the multiple content records.

// Dummy the data from the query
$tickers = [

['displayID' => 1,          'display' => 'Office',      'locationName' => 'Building 4',        'location' => 4,          'ticker' => 1,          'contentID' => 1,          'content' => 'testing content'],
['displayID' => 2,          'display' => 'Lobby',       'locationName' => 'Building 4',        'location' => 4,          'ticker' => 2,          'contentID' => 2,          'content' => 'testing content 2'],
['displayID' => 3,          'display' => 'Lobby',       'locationName' => 'Building 1',        'location' => 1,          'ticker' => 3,          'contentID' => 3,          'content' => 'testing content 3'],
['displayID' => 4,          'display' => 'Office',      'locationName' => 'Building 1',        'location' => 1,          'ticker' => 4,          'contentID' => 4,          'content' => 'testing content 4'],
['displayID' => 4,          'display' => 'Office',      'locationName' => 'Building 1',        'location' => 1,          'ticker' => 4,          'contentID' => 5,          'content' => 'testing content again']
];

// A place to keep the reorganized data
$tickerDisplays = [];

// Walk through the query result
foreach($tickers as $row) {
    $displayID = $row['displayID']; // for convenience and readability
    $location = $row['location'];   // for convenience and readability
    $display = $row['display'];
    $contentID = $row['contentID'];

    if ( ! array_key_exists($row['displayID'], $tickerDisplays) ) {
        $tickerDisplays[$displayID] = [
            'displayID' => $row['displayID'],
            'display' => $row['display'],
            'ticker' => $row['ticker'],
            'contentID' => $row['contentID'],
            'content' => $row['content'],
            'location' => $row['location'],
            'locationName' => $row['locationName'],
            '@content' => [] // to store the content data                
        ];

    }
    $tickerDisplays[$displayID]['@content'][$contentID] = ['content' => $row['content']];
}

print_r($tickerDisplays);

foreach ( $tickerDisplays as $key => $ticker ) {
    // Output the display and location name
    out($ticker['display']);
    out($ticker['locationName']);
    // Output all the content records.
    foreach ( $ticker['@content'] as $contentID => $content ) {
        out($content['content']);
    }
    out('------------');
}
// Just a utility function
function out($msg) {
    echo "$msg\n";
}

Output:

Office
Building 4
testing content
------------
Lobby
Building 4
testing content 2
------------
Lobby
Building 1
testing content 3
------------
Office
Building 1
testing content 4
testing content again
------------

I think this ended up doing exactly what I wanted, thank you!

Collectives™ on Stack Overflow

Restructure array from sql query so that I can echo proper values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related