Mehrere geliche Einträge innerhalb von 12 Stunden mit anzahl ausgeben lassen?

[Nightland Driver]

Hey,

noch ein kleines Problem welches mir aufgefallen ist, ich habe vor langer zeit mal eine funktion eingebaut die aufzeichnet was ein nutzer gemacht hat z.B. Eine Datei löschen, bearbeiten, hinzufügen etc.
Nun ist das ganze aber ziemlich ausgeartet und die Seite wo man das einsehen kann ist kaum aufzurufen. Daher wollte ich dort ein wenig aufräumen und die Einräge mal zusammen führen.
Nur habe ich keine idee wie ich das machen kann.

Ich habe mal ein Beispiel Bild:
actions.png

Und ich würde es gerne so anzeigen lassen:
action-1.png

Jetzt die frage wie ich das anstelle? Ich habe derzeit diesen Code zum Auslesen & ausgeben:

<?php
$sql = "SELECT * FROM last_activity";
if ($erg = $conn->query($sql)) {
while ($datensatz = $erg->fetch_object()) {
$daten[] = $datensatz;
}
}

$result = $conn->query($sql);
?>

<?php
if(mysqli_num_rows($result)!=0){
$reversed = array_reverse($daten);

foreach ($reversed as $content) {

if($content->action == 0){
$action = "hinzugefügt";
} else if($content->action == 1){
$action = "gelöscht";
} else if($content->action == 2){
$action = "bearbeitet";
} else if($content->action == 3){
$action = "archiviert / dearchiviert";
} else {
$action = "[<span style='color: darkred;'>Error</span>]";
}

if($content->location == "article"){
$location = "Hat einen Beitrag";
} else if($content->location == "contact"){
$location = "Hat eine Nachricht";
} else if($content->location == "commands"){
$location = "Hat einen Befehl";
} else if($content->location == "videos"){
$location = "Hat ein Video";
} else {
$location = "[<span style='color: darkred;'>Error</span>]";
}

if($_COOKIE['userRights'] == '997' || $_COOKIE['userRights'] == '977'){
echo'<a class="list-group-item list-group-item-action">';
echo'<div class="media">';

if(file_exists('img/user/avatar_' . $content->username . '.png')){
echo'<img class="d-flex mr-3 rounded-circle" style="width: 45px; height: 45px;" src="img/user/avatar_' . $content->username . '.png" alt="' . $content->username . '">';
} else {
echo'<img class="d-flex mr-3 rounded-circle" style="width: 45px; height: 45px;" src="img/avatar.png" alt="' . $content->username . '">';
}

echo'<div class="media-body">';
echo'<strong>' . $content->username . '</strong> ' . $location . ' ' . $action;
echo'<div class="text-muted smaller">' . $date = date("d.m.Y",strtotime($content->created)) . ' um ' . $time = date("H:i",strtotime($content->created)) . ' Uhr</div>';
echo'</div>';
echo'</div>';
echo'</a>';
}
}
echo'<a class="list-group-item list-group-item-action" href="manage_lastactivity.php">Weitere Aktivitäten...</a>';
}
?>

[Sempervivum]

Versuche es mit COUNT und Gruppierung:

SELECT count(1) as cnt, username, action, location FROM `last_activity` GROUP BY username, action, location

[Nightland Driver]

Ich habe auch noch ein Datum in der Datenbank stehen, kann ich dass dann mit den 12 Stunden zusammen fassen machen, weil das obere fasst ja alle zusammen?

[Sempervivum]

Du kannst ja zusätzlich nach dem Datum gruppieren, wahrscheinlich hinter dem Usernamen, damit die Aktionen des Benutzers zusammen stehen:

SELECT count(1) as cnt, username, date, action, location FROM `last_activity` GROUP BY username, date, action, location

Dieses habe ich jetzt nicht getestet.

[Nightland Driver]

Jetzt sortiert er das nach Benutzern, Problem ist das mach nicht wirklich sinn, da die Reihenfolge nicht werhalten bleibt.
Ich versuche es nochmal einfach zu erklären.

Aktionen wie Mitglieder hinzufügen, löschen& Beiträge schreiben, bearbeiten werden folgendermaßen in die Datenbank gespeichert: 2=(bearbeitet)

id	username	action	location	created
1	Nightland	2	article	2021-02-05 12:58:48

...

Die Daten daraus lade ich in den Aktivitäten bereich wo die dann für das AdminTeam einsehbar sind. Nun kann es aber vorkommen dass ein Benutzer mehrere Beiträge bearbeitet.
Dafür wird derzeit jedesmal ein eigenes Feld in dem Aktivitäten bereich angezeigt. Das möchte ich gerne ein wenig aufräumen und möchte daher gleiche Aktivitäten die innerhalb von 12 Stunden durchgeführt wurden zusammen fügen und mit der gesamtzahl ausgeben. Die reihenfolge der Aktionen soll dabei erhalten bleiben. Außer es wird von mehreren Nutzern gleichzeitig etwas gemacht, dann darf die reihenfolge leicht abweichen.

Ich hoffe das war soweit verständlicher vormuliert.

[Sempervivum]

Dann gruppiere als erstes nach Datum:

SELECT count(1) as cnt, username, date, action, location FROM `last_activity` GROUP BY date, username, action, location

[Nightland Driver]

Dann habe ich die nach datum sortiert. Bringt mich aber nicht weiter, wie bekomme ich die dann gruppiert?

[Sempervivum]

Die Daten werden nicht richtig gruppiert weil date auch Stunden, Minuten etc. beinhaltet. Gruppieren wir nur nach dem Datum müsste es besser funktionieren:

SELECT count(1) as cnt, username, date, action, location
    FROM last_activity
    GROUP BY DATE_FORMAT(date, "%Y-%c-%D"), username, action, location

[Nightland Driver]

Danke, jetzt funktioniert es.
Vielen Vielen Dank.

[Nightland Driver]

Nun fehlt mir doch noch ein kleines Detail.

Ich brauche den Count noch an einer anderen stelle, und da komme ich niocht ganz zurecht.
Ich frage halt beide Datenbanken wie folgt ab:

<?php
$sql = "SELECT count(1) as count, user_id FROM last_activity GROUP BY user_id";
if ($erg = $conn->query($sql)) {
while ($datastring = $erg->fetch_object()) {
$data2[] = $datastring;
}
}
?>

<?php
$sql = "SELECT * FROM users";
if ($erg = $conn->query($sql)) {
while ($datastring = $erg->fetch_object()) {
$data[] = $datastring;
}
}
?>

Danach werden die ganzen Daten mit foreach ausgegebn. Dasfunktioniert auch einwandfrei. Leider nur solange bis ein nutzer keinen Count bei den Aktionen hat. Dann wird nichts ausgegeben.
Wie bekomme ich es hin dass dann eine 0 ausgegeben wird?

<?php
foreach ($data as $content) {
//More Content...
foreach ($data2 as $content2) {
if($content->id == $content2->user_id){
if($content2->count != 0){
echo $content2->count;
} else {
echo '0';
}
}
}
//More Content...
}
?>

Was mir natürlich noch lieber wäre, wenn ich beide Datenbank abfragen in einer machen kann und dann den count mit bei den nutzern habe, aber da weiß ich nicht wie man dass machen kann.

[Sempervivum]

Kann man das Ganze verbal so formulieren:
Du möchtest alle User ermitteln mit der Anzahl ihrer Aktivitäten. Und hast dabei das Problem, dass ein User unter den Tisch fällt, wenn er keine Aktivitäten hatte?
Das lässt sich sicher in einer DB-Abfrage machen. Poste am besten wieder einen Dump der beiden Tabellen.

[Sempervivum]

Ich habe das jetzt untersucht. Zunächst eine Lösung wo beide Tabellen getrennt gelesen werden und in einer Nachverarbeitung mit PHP verknüpft werden:

$sql1 = "SELECT id, username
FROM users";
$sql2 = "SELECT user_id, count(action) nract
FROM last_activity
GROUP BY user_id";
// User auslesen
$usersArr = [];
$users = $pdo->query($sql1);
$usersArr = $users->fetchAll();

// Aktivitaeten auslesen und in ass. Array mit User-ID als Schlüssel ablegen
$activities = $pdo->query($sql2);
foreach ($activities as $row) {
    $activitiesArr[$row['user_id']] = $row['nract'];
}

// Beide Tabellen verknüpfen
$resultArr = [];
// Ueber alle User
foreach ($usersArr as $user) {
    // Sind Aktivitaeten für den User vorhanden?
    if (isset($activitiesArr[$user['id']])) {
        // Anzahl in Ergebnis eintragen
        $resultArr[] = ['username' => $user['username'], 'nract' => $activitiesArr[$user['id']]];
    } else {
        // 0 in Ergebnis eintragen
        $resultArr[] = ['username' => $user['username'], 'nract' => 0];
    }
} 

Hier kannst Du selbstverständlich noch weitere Infos über den User hinzu fügen.

[Sempervivum]

Und die Version nur mit SQL:

$sql = "SELECT users.id, users.username,
    SUM(CASE
      WHEN last_activity.action IS NULL THEN 0
      ELSE 1
    END) AS nractivity
    FROM last_activity
    RIGHT JOIN users ON last_activity.user_id = users.id
    GROUP BY users.id
    ORDER BY users.username";
$result = $pdo->query($sql);
$resultArr = $result->fetchAll(); 

Dank an AndreasB in einem anderen Forum, der die Basis für diese Abfrage entwickelt hat.

[Nightland Driver]

Also der Vorschlag die mit php zu verbinden gibt den folgenden Fehler aus:

Call to undefined method mysqli_result::fetchAll()

Aber das mit SQL scheint sehr gut zu funktionieren.
Ich benötige aber noch die gesamten restlichen daten vom benutzer, wie kann ich die noch mit auslesen?

Ich habe mal einen Dump der Users Tabelle erstellt:
https://pastebin.com/uhXevu6b

[Sempervivum]

Oh, ich hatte nicht daran gedacht, dass Du mysqli verwendest, selber benutze ich PDO. Allerdings verstehe ich dann nicht, warum dann die Version mit SQL funktioniert, denn die benutzt auch fetchAll(). Sicher hast du dort nur die Query verwendet?

Würdest Du vielleicht noch Mal einen Dump der beiden Tabellen posten? Interessehalber würde ich gern mal die Schnelligkeit untersuchen und dafür brauche ich Daten mit einem gewissen Volumen.

Ich benötige aber noch die gesamten restlichen daten vom benutzer, wie kann ich die noch mit auslesen?

Wenn es Daten vom Benutzer sind, ist es einfach, Du brauchst sie dann nur in der ersten Zeile hinter den anderen Spalten hinzuzufügen:

$sql = "SELECT users.id, users.username, users.geburtsdatum, users.rank, -- hier hinzu fügen
    SUM(CASE
      WHEN last_activity.action IS NULL THEN 0
      ELSE 1
    END) AS nractivity
    FROM last_activity
    RIGHT JOIN users ON last_activity.user_id = users.id
    GROUP BY users.id
    ORDER BY users.username";