dogstats/web/pages/smart/controller.php

<?php


class Smart extends Page {
    function setMenu()
    {
        $this->menu_text = 'Smart';
        $this->menu_image = 'fas fa-robot';
        $this->menu_priority = 1;
    }

    function index()
    {
        $this->set('user', $_SESSION['user']->data);
        $this->set('template', "smart.html.php");
    }

    function search()
    {
        $db = new SQLite3(ROOT.DS.'../crawler/data.db', SQLITE3_OPEN_READONLY);
        $q = $_REQUEST['q'];
        if(!$q || strlen($q) < 3 || strpos($q, ' ') === false)
            return partial('error.html', ['errorTitle' => 'Error', 'errorMessage' => 'Error: Bitte Nachname und Vorname eingeben.']);

        $stmt = $db->prepare("SELECT DISTINCT(hund) FROM results WHERE teilnehmer LIKE :q");
        $stmt->bindValue(':q', $q, SQLITE3_TEXT);

        $res = $stmt->execute();
        $results = [];
        $dogs = [];

        while($row = $res->fetchArray())
        {
            $dogs[] = $row['hund'];
        }

        if(count($dogs) == 0)
            return partial('error.html', ['errorTitle' => 'Error', 'errorMessage' => 'Error: Keine Ergebnisse gefunden. Bitte Nachname und Vorname prüfen.']);

        foreach($dogs as $dog)
        {
            $res = $db->query("SELECT * FROM results WHERE teilnehmer LIKE '$q' AND hund LIKE '$dog'");
            while($row = $res->fetchArray())
            {
                $row['date'] = $db->querySingle("SELECT date FROM events WHERE id = ".$row['event']);
                $row['event'] = $db->querySingle("SELECT name FROM events WHERE id = ".$row['event']);
                $row['unixtimestamp'] = strtotime($row['date']);
                $row['run'] = $db->querySingle("SELECT name FROM runs WHERE id = ".$row['run']);
                $row['ago'] = printRelativeTime(time(),$row['unixtimestamp']);
                $results[$dog][] = $row;
            }

            //sort results by date
            usort($results[$dog], function($a, $b) {
                return $a['unixtimestamp'] <=> $b['unixtimestamp'];
            });
        }

        $this->set('results_dogs', $results);
        $this->set('dogs', $dogs);
        $this->set('template', 'search.html.php');

    }

}