Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Posted by John Weland
John Weland
42,478 Points

using usort

So I have some PHP that returns $data but just before that data is returns I know I want to do a usort because I need that data to be sorted by its status.

as you can see by the code below my data has its status here $hostDetails["status"] there are currently 4 things it can return "CRIT", "WARN", "INFO" and "OK" (a fifth status maybe added later). When it sorts the priority order should be

"CRIT" "WARN" "INFO" "OK" (The fifth status if its applied would take priority over "CRIT")

public function importantEvents()
    {
        $sql = "SELECT v.hostname,
                       v.entity_name,
                       v.entity_version,
                       v.event_type_name,
                       v.uptime,
                       v.last_hb_ts,
                       v.event_last_ts,
                       v.event_id,
                       v.msg_base,
                       v.msg_dynamic,
                       v.event_state_name,
                       v.project_id,
                       v.server_id,
                       v.entity_id,
                       p.project_id AS project_pin,
                       p.server_id AS server_pin,
                       p.entity_id AS entity_pin
                  FROM vw_events AS v
             LEFT JOIN user_pin AS p
                    ON ((v.project_id = p.project_id OR p.project_id IS NULL)
                   AND  (v.server_id = p.server_id OR p.server_id IS NULL)
                   AND  (v.entity_id = p.entity_id OR p.entity_id IS NULL)
                   AND p.user_id = :uid)
                 WHERE v.event_type_name <> 'UP'
              ORDER BY v.hostname ASC,
                                 v.event_type_priority DESC ";
        $sth = $this->_db->prepare($sql);
        $params = array("uid" => $this->uid);
        $r = $sth->execute($params);
        if ($r === false) {
            $err = $sth->errorInfo();
            filelog(__CLASS__.":".__LINE__.":".$err[0].":".$err[2]);
            return false;
        }
        $data = array();
        while (($row = $sth->fetchObject()) !== false) {
            $host_key = $row->hostname . "-";
                $lastSeen = time() - strtotime($row->last_hb_ts);
            $detail_key = $row->hostname . "-" . $row->entity_name . "-" . $row->event_id;
            if (!array_key_exists($host_key, $data)) {
                $hostSummary = array();
                $hostSummary["host"] = $row->hostname;
                $hostSummary["ip"] = gethostbyname($row->hostname);
                $hostSummary["proc"] = "";
                $hostSummary["version"] = "";
                $hostSummary["status"] = $row->event_type_name;
                $hostSummary["uptime"] = "";
                $hostSummary["seen"] = "";
                $hostSummary["state"] = "";
                $hostSummary["pin"] = !is_null($row->server_pin) && is_null($row->entity_pin);
                $hostSummary["pin_id"] = $row->server_id."-";
                $hostSummary["type"] = "summary";
                $hostSummary["msg"] = "";
                $hostSummary["child_count"] = array();
                $hostSummary["data-tt-id"] = $host_key;
                $hostSummary["data-tt-parent-id"] = "";
                $data[$host_key] = $hostSummary;
            }
            $hostDetails = array();
            $hostDetails["host"] = $row->hostname;
            $hostDetails["ip"] = "";
            $hostDetails["proc"] = $row->entity_name;
            $hostDetails["version"] = $row->entity_version;
            $hostDetails["status"] = $row->event_type_name;
            $uptime = $row->uptime;
            if (!empty($uptime)) {
                $uptime = $this->secsToTime($uptime);
            } else {
                $uptime = "";
            }
            $hostDetails["uptime"] = $uptime;
            if (!empty($row->last_hb_ts)) {
                $lastSeen = time() - strtotime($row->last_hb_ts);
                $lastSeen = $this->secsToTime($lastSeen) . " ago";
            } else {
                $lastSeen = "";
            }
            $hostDetails["seen"] = $lastSeen;
            $eventLastSeen = time() - strtotime($row->event_last_ts);
            $hostDetails["msg"] = $row->msg_base." ".$row->msg_dynamic ."<br />"." " ." Event Last Seen: ".$this->secsToTime($eventLastSeen) . " ago";
            $hostDetails["state"] = $row->event_state_name;
            $hostDetails["pin"] = !is_null($row->entity_pin);
            $hostDetails["pin_id"] = $row->server_id."-".$row->entity_id;
            $hostDetails["type"] = "detail";
            $hostDetails["event_id"] = $row->event_id;
            $hostDetails["data-tt-id"] = $detail_key;
            $hostDetails["data-tt-parent-id"] = $host_key;

            if (!array_key_exists($detail_key, $data)) {
                // Increase the summary message
                if (!array_key_exists($row->event_type_name, $data[$host_key]["child_count"])) {
                    $data[$host_key]["child_count"][$row->event_type_name] = 1;
                } else {
                    $data[$host_key]["child_count"][$row->event_type_name]++;
                }
                $msg = array();
                foreach ($data[$host_key]["child_count"] as $type_name => $count) {
                    $msg[] = $count . " " . $type_name;
                }
                $data[$host_key]["msg"] = join(", ", $msg);
            }

            $data[$detail_key] = $hostDetails;
        }
        // run usort here
        return $data;
    }

I assume I need to create a function to sort the priority order and then call usort with what I am sorting and how I am sorting.

 function tableSort (status) {
    // sort priority order
 }

...

usort ($data, tableSort($hostDetails["status]));

Any help with this would be greatly appreciated.

John Weland
John Weland
42,478 Points

Randy Hoyt, Hampton Paulk, have any ideas Sirs?

miguelcastro2
miguelcastro2
Courses Plus Student 6,573 Points

Philosophically, I like to have my database do all of the sorting whenever I can. The database will perform sorting more efficiently and the larger your data grows, the more sorting your app has to do which will increase load times. Plus database can take advantage of using indexes to increase lookup and sort speeds.

First of all the values for "CRIT" "WARN" "INFO" "OK" should be in a lookup table. Let's call them event_types. Event types has a primary key of id, event_type_name, and event_type_priority. The priority field should be an int. You then can use the priority field to choose the order of severity, perhaps 1 is the highest priority while 4 is the lowest. Now you can easily sort on priority. Lastly, you can modify your function to accept a sort order which will modify the query and returned a sorted result back to you.

I think this solution scales well. Any time you want to add a new event_type you can easily do it and set the priority without having to update your code or any sorting algorithm you have.

John Weland
John Weland
42,478 Points

yeah I've tried that here

ORDER BY v.hostname ASC,
                       v.event_type_id ASC";

changing it too

ORDER BY v.event_type_id DESC,
                   v.hostname ASC";

which in the DB works as intended.... in the browser no such luck. I inherited someone else mess, and now have 5 days before launch and the feature requests just keep rolling in.

miguelcastro2
miguelcastro2
Courses Plus Student 6,573 Points

So it sorts fine when you perform the query directly on the database, but when you build the output array and display it you lose the sorting?

John Weland
John Weland
42,478 Points

precisely, my boos pulled the code as well and is stumped because we don't see anywhere else where sorting is being handled.

Ted Sumner
Ted Sumner
Courses Plus Student 17,967 Points

How is it being sorted wrong?

As far as usort, I have not used it but here is the documentation:

http://php.net/manual/en/function.usort.php

It looks like you make a function that contains your sort routine, then you use usort(array, "function"); to perform the sort on the array. Get it working, then refactor it to work with a database sort.

I would try a vardump on the array as it is returned from the database to see if it is sorted coming out of the database. If it is, then you know the construction of the data to HTML is wrong. If it comes out of the vardump wrong, then the issue is in creating the array.

I think sorting on output from the database makes the most sense in the long term as well.

miguelcastro2
miguelcastro2
Courses Plus Student 6,573 Points

John,

I believe you are losing the order when you perform this statement:

<?php 

$data[$detail_key] = $hostDetails;

?>

Any chance you could issue the code below and inspect the array to see if the order is preserved?

<?php 

// Just append to array
$data[] = $hostDetails;

?>
John Weland
John Weland
42,478 Points

miguelcastro2 you're right sir

$data[$detail_key] = $hostDetails;

is where I lose the sort and that is because of this line further up

$detail_key = $row->hostname . "-" . $row->entity_name . "-" . $row->event_id;

I changed it to 

$detail_key = $row->hostname . "-" . $row->event_type_priority . "-" . $row->entity_name . "-" . $row->event_id;

and that sorts by host name and then by priority switching those two actually breaks the table relationship between the table rows (host) and the rows (events) which are paired together using treetables.

1 Answer

Ted Sumner
PLUS
Ted Sumner
Courses Plus Student 17,967 Points
Ted Sumner
Ted Sumner
Courses Plus Student 17,967 Points

In your $sql SELECT, I don't see that you select v.event_type_id, but you try to order by it. But maybe I just missed it.

miguelcastro2
miguelcastro2
Courses Plus Student 6,573 Points

Ted,

Some databases require you to SELECT the field in order to sort by it, while others do not. MySQL does not require the field to be in the SELECT statement.

Ted Sumner
Ted Sumner
Courses Plus Student 17,967 Points

That is good to know. I obviously had no idea.

That said, I would think you would want that information in this case so you know where the transitions are.