How do I sort by nested php array

Question

This is an excerpt from the xml feed I'm accessing:

And here's my current code:

$file = file_get_contents('feed.xml');
$file = preg_replace('/(<role[^>]+>)([^<]+)/si', '$1', $file);
$xml = new SimpleXMLElement($file);

$search_term = preg_replace('/[,.\/\\\(\)\[\]\{\}`~!@#\$%\^&*;:\'"\-_<>]*/is', '', $_GET['work']);

$productions = $xml->xpath('//production');
?>

<table width="300px" cellspacing="5px" cellpadding="5px" border="1px" >
<tr>
<th>year</th>
<th>company</th>
</tr>

<?php
foreach($productions as $prod) {

    $prod_attrs = $prod->attributes();
    $prod_date = $prod_attrs->startdate;

    echo "<tr><td>", $prod_date, "</td><td>", html_encode($prod_attrs->company), "</td></tr>";


    }               
?>

</table>

This is the output:

My question is, how do I get the table to sort in descending numerical order (i.e. most recent year first)? I've searched here and tried to understand the sort() function (e.g. this answer), but it's a bit beyond me still and I can't figure out how to get that to work here.

---

UPDATE

I'm playing around with @Chris Goddard's answer below..

This is the code I've got, but it doesn't seem to have done the trick:

<?php


    function html_encode($var){

    $var = html_entity_decode($var, ENT_QUOTES, 'UTF-8');
    $var = htmlentities($var, ENT_QUOTES, 'UTF-8');
    return $var;
}


$file = file_get_contents('feed.xml');
$file = preg_replace('/(<role[^>]+>)([^<]+)/si', '$1', $file);
$xml = simplexml_load_string($file);
$json = json_encode($xml); 
$array = json_decode($json,TRUE); 


$search_term = preg_replace('/[,.\/\\\(\)\[\]\{\}`~!@#\$%\^&*;:\'"\-_<>]*/is', '', $_GET['work']);


$works = $xml->xpath('//work');

foreach($works as $work) {
$Productions = $work->xpath('./production');

$Sorter = array();

foreach ($Productions as $prod) {

$prod_attrs = $prod->attributes();
    $Sorter[$key] = $prod_attrs->startdate;
array_multisort($Sorter, SORT_DESC, $Productions);
  }
}
echo "<pre>".print_r($works, true)."</pre>";
?>

What am I doing wrong?

Answer 1

array_multisort will do the trick

you put an array in the first one (which has the keys of each element, and the sorting value, the direction then the big array)

Edit

$Productions = json_decode(json_encode((array) simplexml_load_string($file)), 1);
$Sorter = array();

foreach ($Productions as $Key => $prod)
    $Sorter[$Key] = $prod->attributes->startdate;
array_multisort($Sorter, SORT_DESC, $Productions);

foreach ($Productions as $prod) { ...

Answer 2

you can place the values in an array, then use usort to sort by a user defined function.

And notice that the elements are cast to string when they are set in the array. Comparing them as the XML objects they are isn't what you want.

A little explanation:

First I'm placing the XML data into an array, just because the data is easier to work with that way.

Then, I sort the array based on my custom function date_sort. Looking at the documentation for usort you see:

The comparison function must return an integer less than, equal to, or greater than zero if the first argument is considered to be respectively less than, equal to, or greater than the second.

So, because you want to sort by date desc, if the dates are equal then the sort order is also equal(return 0), if the date is later it should be earlier in the list (return -1), and if the date is greater it should be later in the list (return 1)

Then you just traverse through the array printing as you go.

If you want to use additional data, just place it in the array in the first loop.

$production_array = array();
foreach($productions as $prod) {

    $prod_attrs = $prod->attributes();
    $prod_date = $prod_attrs->startdate;
    $production_array[] = array(
        'date'=>(string) $prod_date,
        'company'=>(string) $prod_attrs->company,
        'review_en'=>(string) $prod->review['quote'],
        'review_de'=>(string) $prod->review->translation
    );
}
usort($production_array, 'date_sort');

foreach($production_array as $production) {
    echo "<tr><td>", $production['date'], "</td><td>", html_encode($production['company']), "</td><td>",$production['review_en'], "</td></tr>";
}

function date_sort($a, $b) {
    if($a['date'] == $b['date']) return 0;
    return $a['date'] > $b['date'] ? -1 : 1;
}

Answer 3

I dont understand half of that code (what's all the regex for?), but to achieve desired sorted table you can simply do:

$profile = simplexml_load_file('http://andrewfinden.com/test/feed.xml');
$productions = $profile->xpath(
    '/profile/repertoire/composer/work/role/production'
);

to load the XML and get the list of all productions as they appear in the XML. To sort them you can use a custom sort:

usort($productions, function($a, $b) {
    return $b['startdate'] - $a['startdate'];
});

which will compare the startdates of the simplexml elements and sort them in descending order. And then it's only a matter of just iterating the productions:

<table>
    <thead>
        <tr>
            <th>Year</th>
            <th>Company</th>
        </tr>
    </thead>
    <tbody>
    <?php foreach ($productions as $production): ?>
        <tr>
            <td><?php echo htmlspecialchars($production['startdate'])?></td>
            <td><?php echo htmlspecialchars($production['company'])?></td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>

See demo

Also see this demo for your additional requests in chat, e.g. limit productions to those with a review and sorting works by production startdate.