[PHP] SQL Daten ändern mit History

[Angel]

Hallo Leute, ich habe da mal eine kleine Frage an Euch.
Es dreht sich um folgendes, ich habe eine Eingabe-Maske für Daten Diese Daten werden in einer MySQL Datenbank gespeichert und können auch per klick auf "Edit" überarbeitet werden. Soweit kein Problem, es handelt sich ja am Ende um einen UPDATE Befehl!

Was ich jetzt gerne möchte ist, wenn man auf SUBMIT drückt um das UPDATE auszuführen, dass die aktuellen Daten einmal gesichert werden und die neuen Daten dann gespeichert werden. Ich glaube die vB Foren-Software macht so etwas ähnliches. Es geht darum, dass man vielleicht die letzten 5 Veränderungen nachvollziehen kann, wer diese getätigt hat, und was vorher mal drin stand

Ich habe schon überlegt ob ich mir von meiner SQL Tabelle einfach eine 1zu1 Kopie erstelle, und die "original" Daten vor dem Update immer erst dort rein kopieren lasse. Doof ist jetzt allerdings das ein Datensatz aus zwei Tabellen mit einer 1-n Beziehung besteht. Wobei bei n maximal 5 Datensätze (Reihen) á 5 Felder sein können. Jetzt ist halt die Frage wie man das sinnvoll macht, entweder einmal alles abrufen, als String mit ";" getrennt zusammen fügen und in eine Backup Tabelle packen wo dann ein mediumtext drin ist, oder eine andere Variante.

Aber vielleicht habt Ihr da noch eine sinnvolle Variante, wie man das lösen / erledigen kann.
Mfg. Angel

[Roin]

Ich überlege mir gerade auch für ein paar Projektchen etwas ähnliches.

Dabei denke ich an eine mit Absicht denormalisierte Tabellenstruktur.
Die Sicherungstabellen besteht dann aus den Feldern:

ID, ÄnderungsID, Tabelle, Feldname, Feldwert, Datum, User

Oder etwas normalisierter und möglicherweise einfacher zu handhaben:

tbl_1
ID, Tabelle, Datum, User

tbl_2
ID, ÄnderungsID (ID von tbl_1), Feldname, Feldwert

Welche Datentypen die Felder da bekommen sollen ist wohl klar...

Dann würde ich einfach mit einem P-Statement alle zu ändernden Felder einer Tabelle auslesen, die Daten sichern, Änderung durchführen und bei Miss-Erfolg die gesicherten Daten wieder herstellen bzw aus der Änderungtabelle löschen

[Angel]

Joa, klingt ja schon mal gar nicht verkehrt..
Lässt Du dann immer prüfen, welche Felder verändert worden sind und die "Originale" sicherst Du dann in den Feldern.. oder wie hast Du das geplant?

Im Prinzip ist das ja so sehr gut, das spart auch Speicherplatz, da nicht gleich alles gesichert wird.. Aber wie prüfst Du denn immer ob ein Feld verändert worden ist, ohne das es da zu Problemen kommt? Diese Überprüfung muss ja rein von der Theorie her, 100% funktionieren...

Oder verstehe ich etwas falsch?

[Roin]

Hast mich gerade dazu gebracht meine SQL-Aktionen sicher und komfortabler (für mich) zu machen.

EIn Teil davon ist folgender:

/**
* Sichert die Update-Daten und updated anschließend die Tabelle.
*
* @param string $table        Die Tabelle, die geupdatet werden soll
* @param array  $new_values   Ein Array, welches als Schlüssel die Feldnamen
*                             und als values die Feldinhalte beinhaltet.
* @param string $where        Die WHERE-Klausel in Stringform. Maskierung muss
*                             bereits beachtet sein.
* @param string $savty_table  Beim ersten Aufruf wird der Name der Sicherungstabelle
*                             ebenfalls erwartet.
* @param bool   $id_update    Falls die ID eines Datensatzes verändert werden soll,
*                             muss dieser Parameter auf true/1 gesetzt werden.
*
* return bool                 War das Update erfolgreich?
*/
function query_update($table, $new_values = array(), $where = NULL, $savty_table = "", $id_update = false) {
    static $st = "";
    $change_id = 0;
    
    if("" !== $savty_table) {
        $st = $savty_table;
    }
    
    if(array() == $values || !is_array($values)) {
        return false;
    }
    
    if("" !== $st) {
        if(!$id_update) {
            $select = "SELECT ID, ".implode(', ', array_keys($new_values))." FROM ".$table;
        } else {
            $select = "SELECT ".implode(', ', array_keys($new_values))." FROM ".$table;
        }
        if(NULL !== $where) {
            $select .= " WHERE ".$where;
        }
        $data = query_fetch(query_select($select));
        
        if(!empty($data) && is_array($data)) {
            $select_change_id = "SELECT MAX(ChangeID) as CID FROM ´".$st."´";
            $select_change_id = query_fetch(query_select($select_change_id));
            $change_id = ($select_change_id[0]['CID']+1);
            foreach($data as /*$num => */$array) {
                foreach($array as $key => $val) {
                    $insert = array('ChangeID' => $change_id, 'Tablename' => $table, 'Field' => $key, 'Value' => $val);
                    //"INSERT INTO ".$st." (ChangeID, Tablename, Field, Value) VALUES (".intval($change_id).", '".query_escape($table)."', '".query_escape($key)."', '".query_escape($val)."')";
                    query_insert($st, $insert);
                }
            }
        }
    }
    
    $update = "UPDATE ´".$table."´ SET ";
    foreach($values as $key => $val) {
        $update .= "´".query_escape($key, 's')."´ = ".query_escape($val, 's').",";
    }
    $update  = substr($update, 0, strlen($update)-1);
    if(NULL !== $where) {
        $update .= " WHERE ".$where;
    }
    
    if(!query($update)) {
        if("" !== $st) {
            query_delete($st, "´ChangeID´ = ".query_escape($change_id, 'i'));
        }
        return false;
    }
    return true;
} 

Tabellenstruktur der Sicherungstabelle ist nun folgender:
ID, ChangeID, Tablename, Field, Value

Alle restlichen Daten entnehme ich der zuverändernden Tabellen, da ich dort auch Referenzen/Beziehungen zu anderen Tabellen (z.B. der User-Tabelle) habe.

Erläuterung der verwendeten Funktionen:

• query() - Sendet die Query ab.
• query_select() - Funktion, die einen SELECT-Befehl ausführen soll - macht nichts besonderes außer die Zeit messen.
• query_fetch() - gibt ein Array zurück*, geht also im Prinzip nur mysql_fetch_assoc() durch.
• query_insert() - Fügt einen entsprechenden Datensatz in eine Tabelle ein.
• query_escape() - Maskiert jede Variable gemäß ihrem Datentyp
• query_delete() - Löscht einen Datensatz mit einer bestimmten ID.

* array{ Datensatznummer => array {Feldname => Feldwert [, Feldname2 => Feldwert2] }} ....

Wie man vielleicht erkennt, arbeite ich viel mit den Array-Schlüsseln als Namen für Felder oder ähnliches.

Bisher ungetestet, da ich mit dem Rest noch nicht ganz zufrieden bin. Im Laufe des Tages werde ichs aber denke ich mal testen.

[Angel]

Moin moin, vielen Dank für dein Beispiel. Das werde ich mir mal verinnerlichen und schauen, was ich davon umsetzen kann. Wenn es noch eine Frage gibt, werde ich rufen und nach harken

Mfg. Angel

[braegler]

Einen solchen Audittrail kann man AFAIK bei MySQL über Trigger realisieren.
--> http://kahimyang.info/kauswagan/HowtoBlogs.xhtml?b=552