Mit PHP MySQL Datenbank bearbeiten

flosai · 27. Mai 2021

Ich sitze hier vor einen kleinen Problem was mich etwas trollt.

Ich habe ein XML Datei, die aus einem SAP (AX) exportiert wird. Dieses lese ich in PHP zeilenmäßig ein und trage die Werte in eine SQL Datenbank ein. Die Tabelle (Main) füllt sich somit nach und nach. Allerdings fallen nach und nach auch wieder Werte aus der Tabelle, bis dato aber händisch.

Code:

// Code nur für das eintragen neuer Artikel
if(file_exists($filename))
{
    $xml = simplexml_load_file($filename);
    if($xml)
    {
        foreach($xml->ProdOverviewDSTable0->Detail_Collection->Detail AS $article)
        {
        $sql =  "SELECT prod FROM tabelle WHERE prod='".$article['ProdId1']."'";
        $result = mysqli_query ($mysqli, $sql);      
            if (mysqli_num_rows ($result) > 0)
            {          
                ?><script>alert("Datensatz schon vorhanden"); window.location = overview.php';</script><?php  
            }
            else
            {
                $sql = "INSERT INTO tabelle (id, pos, prod, qyt, name, kdartnr, out) VALUES ('', '".$number."', '".$article['ProdId1']."', '".$article['QtyCalc1']."', '".$article['Name1']."', '".$article['pdtgetCustItemId1']."', "")";               
                if(mysqli_query($mysqli, $sql))
                {
                    ?><script>alert("Datensatz erfolgreich übernommen"); window.location = overview.php';</script><?php
                }
                else
                {
                    echo "Fehler: Could not able to execute $sql1. ".mysqli_error($mysqli)."";
                }
            }
        }
    }
}

Nun wollte ich das automatsieren, die exportierte XML liefert immer nur die aktuellen Artikel. Sodass ich dachte, ich kann dies zum abgleich nutzen. So wollte ich ich, wenn er in die Dankbank (Main) die neuen Artikel schreibt. Ebenfalls in eine zweite Datenbank (Temp) die eindeutigen, einmaligen Prod-Nummern der Artikel schreibt.

Der nächste Schritt wäre nun der Abgleich zwischen Datenbank (Main) und der Datenbank (Temp) auf vorhanden sein der Prod-Nummer. Wenn diese in beiden vorhanden ist, ist der Artikel noch aktuell und soll in der Datenbank (Main) verbleiben. Wenn die Prod-Nummer in der Datenbank (Temp) nicht mehr vorhanden ist, soll der entsprechende Datensatz (Artikel) in der Datenbank (Main) gelöscht werden.

Am Ende soll dann jedesmal die Datenbank (Temp) zurückgesetzt werden.

Code:

// Code Erweiterung das eintragen neuer Artikel und löschen alter, ungültiger
if(file_exists($filename))
{
    $xml = simplexml_load_file($filename);
    if($xml)
    {
        foreach($xml->ProdOverviewDSTable0->Detail_Collection->Detail AS $article)
        {
        $sql =  "SELECT prod FROM tabelle WHERE prod='".$article['ProdId1']."'";
        $result = mysqli_query ($mysqli, $sql);      
            if (mysqli_num_rows ($result) > 0)
            {          
                $sql = "INSERT INTO tabelle_temp (id, prod) VALUES ('', '".$article['ProdId1']."')";               
                if(mysqli_query($mysqli, $sql))
                {
                    $sql = "DELETE FROM tabelle WHERE prod NOT EXISTS (SELECT prod FROM database_temp)";
                    if(mysqli_query($mysqli, $sql))
                    {
                        $sql = "TRUNCATE TABLE tabelle_temp";
                        if(mysqli_query($mysqli, $sql))
                        {
                            ?><script>alert("Datensatz schon vorhanden"); window.location = overview.php';</script><?php
                        }
                    }
                }      
            }
            else
            {
                $sql = "INSERT INTO tabelle (id, pos, prod, qyt, name, kdartnr, out) VALUES ('', '".$number."', '".$article['ProdId1']."', '".$article['QtyCalc1']."', '".$article['Name1']."', '".$article['pdtgetCustItemId1']."', "")";               
                if(mysqli_query($mysqli, $sql))
                {
                    $sql = "INSERT INTO tabelle_temp (id, prod) VALUES ('', '".$article['ProdId1']."')";               
                    if(mysqli_query($mysqli, $sql))
                    {
                        $sql = "DELETE FROM tabelle WHERE prod NOT EXISTS (SELECT prod FROM tabelle_temp)";
                        if(mysqli_query($mysqli, $sql))
                        {
                            $sql = "TRUNCATE TABLE tabelle_temp";
                            if(mysqli_query($mysqli, $sql))
                            {
                                ?><script>alert("Datensatz erfolgreich übernommen"); window.location = overview.php';</script><?php
                            }
                        }
                    }
                }
                else
                {
                    echo "Fehler: Could not able to execute $sql1. ".mysqli_error($mysqli)."";
                }
            }
        }
    }
}

Der Effekt ist nun, das er zwar die nicht mehr vorhanden Artikel zwar löscht aus der Main tabelle, diese aber komplett neuschreibt und die Temp tabelle mit Inhalt belässt. ID Nummer zählt hoch und alle Änderung in der Main tabelle sind verschwunden (man kann händisch bei Spalte out ja/nein setzen in einer Eingabemaske).

Yaslaw · 27. Mai 2021

Du meinst hoffentlich nicht Datenbank "MAIN" und "TEMP" sondern Tabellen derselben Datenbank.
Das normale Vorgehen wird mit SQL gelöst. Benenne deien Tabellen nicht datenbank. Das ist als ob du den dein Steuerrad am Auto selber Auto nennst. Datenbank ist der ganze Topf mit allen Daten drin. Die Tabellen sind Teil der Datenbank. Diese als database zu benennen kann man, gibt aber im Kopf des Lesers und zukünftigen Programmierers deiner Lösung (wer weiss, ob es nur bei dir bleibt) reichlich Chaos.

mysqli_* ist seit Jahren unsicher & veraltet und wird mit den neuen PHP-Versionen nicht mehr funktionieren!

- Daten aus der STage-Tabelle löschen "TRUNCATE TABLE STG_ARTIKEL"
- Neue Daten in Stage-Tabellen schreiben "INSERT INTO STG_ARTIKEL() VALUES() "
- Abgleichen mit der Hapttabelle "TBL_ARTIKEL" mittels eines DELETE und INSERT...SELECT.

Zvoni · 27. Mai 2021

Wenn ich dich richtig verstanden habe:
1) deine temp ist leer
2) du lädst das xml in die Temp
3) du lädst das xml in die main, wobei du Artikel welche schon in main existieren ignorierst
4) du machst nen Abgleich zwischen main und temp
5) Artikel welche in Main sind, aber NICHT in Temp sollen aus main gelöscht werden
6) setze temp wieder auf leer

Korrekt?

flosai · 27. Mai 2021

Yaslaw hat gesagt.:
Du meinst hoffentlich nicht Datenbank "MAIN" und "TEMP" sondern Tabellen derselben Datenbank.
Das normale Vorgehen wird mit SQL gelöst. Benenne deien Tabellen nicht datenbank. D

mysqli_* ist seit Jahren unsicher & veraltet und wird mit den neuen PHP-Versionen nicht mehr funktionieren!

1) Nach meinem Kentnisstand ist mysql ohne i veraltet, was ist denn der Nachfolger vom mysgli?
2) Ja ich meine Tabelle, war da Gedanklich gerade wo anders. Ich hab die jetzt einfach im Besipiel nur Tabelle und Tabelle_temp genannt, kannst auch tabelle 1 und tabelle 2 nehmen. Die haben im Projekt selbst korrekte, zuordenbare Namen.

Zvoni hat gesagt.:
Wenn ich dich richtig verstanden habe:
1) deine temp ist leer
2) du lädst das xml in die Temp
3) du lädst das xml in die main, wobei du Artikel welche schon in main existieren ignorierst
4) du machst nen Abgleich zwischen main und temp
5) Artikel welche in Main sind, aber NICHT in Temp sollen aus main gelöscht werden
6) setze temp wieder auf leer

Korrekt?

genau, wobei in die Temp nur die Spalte "prod" geschrieben wird, da ich diese zum Abgleich nehme, da diese eine Nummer enthält, die für jeden Artikel einmalig ist.

Zvoni · 27. Mai 2021

Was deine Probleme angeht (Main ist "neu", temp bleibt voll):
Du fügst Sätze einzeln hinzu
1) Main ist "neu", weil du nach jedem einzelnen hinzufügen eines Satzes den Rest löschst
2) temp bleibt voll, weil dein TRUNCATE sich innerhalb eines If-Konstrukts befindet, welches wahrscheinlich nicht betreten wird (Bin kein PHP-Experte)

Würde eh wie folgt vorgehen:
1) Prüfe ob Temp leer ist (bzw. feuer ein TRUNCATE gleich von Anfang an ab)
2) Lade das XML in einem Rutsch in die Temp
3) Lade das XML in einem Rutsch in die Main, und zwar mit INSERT IGNORE -Syntax
4) Feuer ein DELETE an Main für die Produkte, welche nicht in Temp sind (leicht per LEFT JOIN und IS NULL)
4a) (optional) Vergleiche Anzahl Produkte in Main und Temp zzgl. Vergleich mit Anzahl Produkte bei INNER JOIN zwischen Main und Temp
5) TRUNCATE temp

Keine If/Else-Abfragen notwendig

Bemerkungen:
1) Das Abfragen, ob ein Satz bereits in Main existiert, ist in diesem Kontext Unfug und bremst dich nur aus. INSERT IGNORE erledigt alles für dich: Existiert der Satz bereits, wird das INSERT ignoriert, existiert er nicht, wird INSERT ausgeführt
2) Schritt 3 kann man sich auch sparen, wenn man einen AFTER INSERT-Trigger auf Temp schreibt, welches das INSERT IGNORE auf Main übernimmt.
Voraussetzung: die Spalten in Main sind korrekt definiert (Primary Key, Unique etc.), und temp um die Spalten ergänzen, welche auch in Main eingefügt werden (dürfte kein grosser Overhead sein). Du hast momentan nur 2 Spalten in temp

Yaslaw · 27. Mai 2021

flosai hat gesagt.:
1) Nach meinem Kentnisstand ist mysql ohne i veraltet, was ist denn der Nachfolger vom mysgli?
2) Ja ich meine Tabelle, war da Gedanklich gerade wo anders. Ich hab die jetzt einfach im Besipiel nur Tabelle und Tabelle_temp genannt, kannst auch tabelle 1 und tabelle 2 nehmen. Die haben im Projekt selbst korrekte, zuordenbare Namen.

Stimmt, mein Fehler

flosai · 27. Mai 2021

Yaslaw hat gesagt.:
Stimmt, mein Fehler

Kein Problem, wie ich gedanklich Datenbank = Tabelle gedanklich vertauscht hatte.

Zvoni hat gesagt.:
Was deine Probleme angeht (Main ist "neu", temp bleibt voll):
Du fügst Sätze einzeln hinzu
1) Main ist "neu", weil du nach jedem einzelnen hinzufügen eines Satzes den Rest löschst
2) temp bleibt voll, weil dein TRUNCATE sich innerhalb eines If-Konstrukts befindet, welches wahrscheinlich nicht betreten wird (Bin kein PHP-Experte)
......

Danke für deine Hinweise, habe verstanden und werde es umsetzen und Feedback geben ob es klappt oder nicht ;-)

Yaslaw · 27. Mai 2021

Ich hatte mal eine kleine Anleitung inkl. dem Sinn der Stagetabelle für Access/VBA geschrieben. Aber gilt ähnlich hier.
[VBA][Access]Daten über eine Stagetable importieren [Yaslaw.Info]

flosai · 27. Mai 2021

Yaslaw hat gesagt.:
Ich hatte mal eine kleine Anleitung inkl. dem Sinn der Stagetabelle für Access/VBA geschrieben. Aber gilt ähnlich hier.
[VBA][Access]Daten über eine Stagetable importieren [Yaslaw.Info]

Das muss ich mir mal in Ruhe anschauen, Stagetable kenne ich so noch nicht aber man lernt ja gerne neue Dinge

@Zvoni
Das TRUNCATE wird jetzt als erstes abgearbeiten
Die Überprüfung pro Zeile habe ich entfernt und den Insert geändert auf:

Code:

"INSERT IGNORE INTO tabelle1 (id, pos, prod, qyt, name, kdartnr, out) VALUES ('', '".$number."', '".$article['ProdId1']."', '".$article['QtyCalc1']."', '".$article['Name1']."', '".$article['pdtgetCustItemId1']."', ""

Hab den DELETE umgebaut auf:

Code:

"DELETE t1 FROM `tabelle1` t1 LEFT JOIN `tabelle2` t2 ON t1.`prod` = t2.`prod` WHERE t2.`prod` IS NULL"

Klappt nun alles Wunderbar

Die Bemerkung 2 habe ich dagegen jetzt noch nicht ganz durchschaut aber, heißt es schreibe die XML mit allen Spalten ebenfalls mit INSERT IGNORE in die Temp-Tabelle und wenn dies erfolgrich ist, soll er per AFTER INSERT das selbe bei bei Main-Tabelle tun und wendet dort automatisch auch den INSERT IGNORE an?

Zvoni · 27. Mai 2021

flosai hat gesagt.:
Die Bemerkung 2 habe ich dagegen jetzt noch nicht ganz durchschaut aber, heißt es schreibe die XML mit allen Spalten ebenfalls mit INSERT IGNORE in die Temp-Tabelle und wenn dies erfolgrich ist, soll er per AFTER INSERT das selbe bei bei Main-Tabelle tun und wendet dort automatisch auch den INSERT IGNORE an?

Korrekt, ausser dass beim Schreiben in temp ein "echtes" Insert benutzt werden kann, da temp eigentlich leer sein sollte. Ein INSERT IGNORE ist in dem Fall ein No-Op, was nichts ausmachen würde.
Der AFTER INSERT-Trigger sorgt dann dafür, dass "dasselbe" auch in Main geschrieben wird.
Das INSERT IGNORE-Statement wäre dann irgendwas in der Art (Aircode):

SQL:

INSERT IGNORE INTO MainTable (Spalte1,Spalte2,Spalte3,Spalte4)
VALUES (new.Spalte1,new.Spalte2,new.Spalte3,new.Spalte4)

Siehe auch: MySQL AFTER INSERT Trigger By Practical Examples

Generell sollte man so viel wie möglich vom Server selbst machen lassen, sofern es überhaupt geht.

EDIT: Mir ist was eingefallen, warum es mit dem Trigger nicht gehen kann: Du könntest in eine Race-Condition kommen, da du ja aus der Main-Table im Schritt nach dem befüllen beider Tabellen, ja wieder etwas aus der Main-Table löschst, also Vorsicht!
Eventuell könnte man einen zweiten Trigger auf die Maintable machen, welcher das DELETE übernimmt.
Müsste ich aber nochmal durch mein hirn laufen lassen.
Wenn du sagst es funktioniert jetzt alles, kommt es am Ende auf die 2-3 Statements mehr, welche du vom Frontend aus abschiesst auch nicht mehr an

EDIT2: Ok, mit Trigger würde es nicht gehen, da MySQL nur per-row-Trigger supported, nicht per-Statement, welches man für einen Bulk-Insert bräuchte. Da würde man definitiv in eine Race-Condition kommen.
Fazit: Lass es so wie es ist (alles aus dem Frontend)

EDIT3: Mir ist jetzt doch noch ne Variante eingefallen, mit welcher man einen Trigger verwenden kann:
1) AFTER INSERT-Trigger auf temp wie oben beschrieben.
2) Stored Procedure welche das DELETE und das Truncate übernimmt
Resultat: Man braucht 3 Anweisungen aus dem Frontend
a) Das erste Truncate auf Temp
b) Der Upload des XML in die Temp
c) Aufruf der SP

Mit PHP MySQL Datenbank bearbeiten

flosai

Grünschnabel

Yaslaw

alter Rempler

Zvoni

Erfahrenes Mitglied

flosai

Grünschnabel

Zvoni

Erfahrenes Mitglied

Yaslaw

alter Rempler

flosai

Grünschnabel

Yaslaw

alter Rempler

flosai

Grünschnabel

Zvoni

Erfahrenes Mitglied

Neue Beiträge