PHP-Login / MySQL-Statement

[heinetz]

Hallo Forum,

um ein MySQL-Update auszuführen, habe ich eine PHP-Logik gebaut.
Da ich das heute mehr oder minder unelegant gelöst finde, würde
ich gerne versuchen, die PHP-Logik zu erssetzen und alle in
einem MySQL-Statement abzubilden. Vielleicht mag mir jemand dabei
helfen.

Zwei Tabellen. Die erste heisst 'content':

site_id |    title |
----------|----------|
        1 |  string1 |
----------|----------|
        2 |  string2 |
----------|----------|
        3 |  string3 |
----------|----------|
        4 |  string4 |
----------|----------|
        5 |  string5 |
----------|----------|
        6 |  string6 |

Die zweite heisst 'structure':

site_id | d0 | d1 | d2 | d3 |                          titles |
--------|----|----|----|----|---------------------------------|
      1 |  1 |  0 |  0 |  0 |                        string1/ |
--------|----|----|----|----|---------------------------------|
      2 |  1 |  1 |  0 |  0 |                 string1/string2/|
--------|----|----|----|----|---------------------------------|
      3 |  1 |  1 |  1 |  0 |        string1/string2/string3/ |
--------|----|----|----|----|---------------------------------|
      4 |  1 |  1 |  1 |  1 | string1/string2/string3/string4 |
--------|----|----|----|----|---------------------------------|
      5 |  2 |  0 |  0 |  0 |                         string5/|
--------|----|----|----|----|---------------------------------|
      6 |  2 |  1 |  0 |  0 |                  string5/string6|

Das Feld structure.titles wird in der gesamten Tabelle
structure upgedatet. Schritt für Schritt in PHP sieht
das so aus:

$SelStatment1 = '  
SELECT * FROM `structure`';  
  
$SelResult1   = mysql_query($SelStatment1);  
while ($row1 = mysql_fetch_assoc($SelResult1))  
     {  
      $SelStatment2 = '  
      SELECT  
      content.title FROM content  
      JOIN structure  
      ON content.site_id = structure.site_id  
      WHERE  
      (  
       (  
        structure.d0 = '.$row1['d0'].' AND  
        structure.d1 = 0 AND  
        structure.d2 = 0 AND  
        structure.d3 = 0  
       )  
       OR  
       (  
        structure.d0 = '.$row1['d0'].' AND  
        structure.d1 = '.$row1['d1'].' AND  
        structure.d2 = 0 AND  
        structure.d3 = 0  
       )  
       OR  
       (  
        structure.d0 = '.$row1['d0'].' AND  
        structure.d1 = '.$row1['d1'].' AND  
        structure.d2 = '.$row1['d2'].' AND  
        structure.d3 = 0  
       )  
       OR  
       (  
        structure.d0 = '.$row1['d0'].' AND  
        structure.d1 = '.$row1['d1'].' AND  
        structure.d2 = '.$row1['d2'].' AND  
        structure.d3 = '.$row1['d3'].'  
       )  
      )  
      ORDER BY  
      structure.d0,  
      structure.d1,  
      structure.d2,  
      structure.d3';  
  
      $SelResult2   = mysql_query($SelStatment2);  
      $titles = '';  
      while ($row2 = mysql_fetch_assoc($SelResult2))  
           {  
            $titles.= $row2['title']."/";  
           }  
      $UpdStatment3 = '  
      UPDATE structure  
      SET titles = "'.$titles.'"  
      WHERE site_id = '.$row1['site_id'];  
  
      $SelResult3   = mysql_query($UpdStatment3);  
     }

Ich hoffe aus dem PHP-Code und den MySQL-Tabellen wird die
Logik einigermassen klar. Das PHP ist ohne Rücksicht auf
Performace programmiert. In den beiden Tabellen gibt es
mittlerweile mehr als 1000 Datensätze. Das heisst, dass
meine Schleife 1000 Mal durchläuft, was 2000 SQL-Statements
bedeutet.

Ich kann mir vorstellen, dass ich das genauso mit einem
entsprechend geschachtelten MySQL-Statement hinbekommen
kann, bin aber gestern erstmal mit foldendem ersten Versuch
gescheitert:

UPDATE structure SET `titles` = (  
SELECT title  
FROM content  
WHERE (  
SELECT "X"  
FROM structure  
)  
)  

Das "X" ist natürlich Quatsch, aber scheitern tut das nicht
daran sondern daran, dass in der Bedingung die selbe Tabelle
verwenden will, die ich update.

#1093 - You can't specify target table 'structure' for update in FROM clause

Geht das, was ich hier vorhabe?

danke für eine Einschätzung und

beste gruesse,
heinetz

[Vinzenz Mai]

Hallo,

Ich hoffe aus dem PHP-Code und den MySQL-Tabellen wird die
Logik einigermassen klar.

nein, natürlich nicht. Du solltest Dein Ziel erläutern. Netterweise gibts noch nicht mal irgendeinen Kommentar, der beschreibt, was Dein Code bewirken soll.

*Du* willst Hilfe. Mache es potentiellen Helfern so leicht wie möglich.

Freundliche Grüße

Vinzenz

[heinetz]

Hallo,

nein, natürlich nicht. Du solltest Dein Ziel erläutern. Netterweise gibts noch nicht mal irgendeinen Kommentar, der beschreibt, was Dein Code bewirken soll.

*Du* willst Hilfe. Mache es potentiellen Helfern so leicht wie möglich.

genau das habe ich natürlich versucht und bin selbverständlich
davon ausgegangen, dass das eindeutig ist. Ist es nicht. OK,
wenn ich mir das so ansehe, sehe ich es ein und versuche mal
zu erklären:

Die beiden Tabellen lassen über das Feld 'site_id' miteinander
verknüpfen. Das über dieses Feld verknüpfte nenne ich mal 'site'.
Die 'site' mit der id 1 hat folgende wesentlichen Eigenschaften:

site_id           = 1
structure.d0      = 1
structure.d1      = 0
structure.d2      = 0
structure.d3      = 0
content.title     = 'string1'

Der Wert im Feld structure.titles soll über die ganze Tabelle
upgedatet werden. Was darin steht ist also das Ergebnis des
Unterfangens.

Die 'site' mit der id 2 hat über beide Tabellen hinweg folgende
Eigenschaften:

site_id           = 1
structure.d0      = 1
structure.d1      = 1
structure.d2      = 0
structure.d3      = 0
content.title     = 'string2'

Nach dem Update steht in dem Feld structure.titles bei dieser
'site' der Wert 'string1/string2/'. Der ergibt sich wie folgt:

site_id(1), d0(1), d1(0), d2(0), d3(0), title(string1)
site_id(2), d0(1), d1(1), d2(0), d3(0), title(string2)

site_id(1) ist quasi der Vater von site_id(2), weil d0 in
beiden Fällen den selben Wert hat. d1 unterscheidet sich
und für site_id(1) steht in dem Feld der Wert 0, was die
oberste Ebene kennzeichnet.

Wird das so klar?

beste gruesse,
heinetz

[Vinzenz Mai]

Hallo,

nein, natürlich nicht. Du solltest Dein Ziel erläutern. Netterweise gibts noch nicht mal irgendeinen Kommentar, der beschreibt, was Dein Code bewirken soll.

also aus Deinen bisherigen beiden Beiträgen habe ich entnommen, dass die zweite Tabelle aus Deinem ersten Beitrag *nicht* so gefüllt ist, wie es angegeben ist, sondern dass sie so gefüllt werden soll.

Die beiden Tabellen lassen über das Feld 'site_id' miteinander
verknüpfen. Das über dieses Feld verknüpfte nenne ich mal 'site'.
Die 'site' mit der id 1 hat folgende wesentlichen Eigenschaften:

Die 'site' mit der id 2 hat über beide Tabellen hinweg folgende
Eigenschaften:

site_id           = 1

muss ich das verstehen?

In der Tabelle, in der die 'Sites' definiert werden steht nichts von einer site_id, die den Wert "1" hat, da steht, dass die site_id den Wert "2" hat. Nur dadurch, dass der Eintrag von site_id in Spalte d0 (ein selten schlechter Spaltenname, der überhaupt nichts aussagt) den gleichen Wert besitzt, kann ich mir eine Verknüpfung zum

site_id(1) ist quasi der Vater von site_id(2), weil d0 in
beiden Fällen den selben Wert hat.

Elterndatensatz vorstellen. Sinn und Zweck dieses komplizierten Konstrukts erschließt sich mir nicht, vermutlich kann man es besser und einfacher machen.

Kann ein Elterndatensatz mehrere Kinder haben oder genau eines? Derzeit zeigst Du stets nur ein Kind?

Um hierarchische Beziehungen in einer relationalen Datenbank darzustellen, eignet sich in vielen Fällen das Nested-Set-Muster.

Freundliche Grüße

Vinzenz

[heinetz]

Hallo,

es ist mir vollkommen klar, dass das ein ziemlich und ausserdem
unnötig kompliziertes Konstrukt ist. Und ich wüsste, dass und
wie ich es heute anders machen würde. Es steht zur Zeit aber
nicht zur Debatte, dieses Kontrukt an sich zu ändern. Denn dafür
gibt es gerade weder Zeit noch ein Budget. Trotzdem danke für
den Hinweis.

Obwohl ich mittlerweile dafon ausgehe, dass mich die Diskussion
hier in meinem Vorhaben nicht weiterbringen wird, weil ich mein

• unnötig kompliziertes - Konstrukt und das, was ich eigentlich
  vorhabe nicht erklären können werde, will ich es noch einmal
  versuchen:

Wenn man sich die Tabelle ansieht, sollten drei Dinge auffallen:

1. Das Feld 'site_id' taucht in beiden Tabellen auf. Die
   Anzahl der Datensätze ist in beiden Tabellen gleich.

2. Zwischen dem Feld 'structure'.'titles' und 'content'.'titles'
   kann man sich aufgrund derer Inhalte einen Zusammenhang
   denken.

3. Die Felder 'structure'.'d0' - 'structure'.'d3' scheinen
   irgendeine Matrix abzubilden.

Was an Informationen aus den Beispielen nicht hervorgeht:

Das mit dem Elternelement hatte ich versucht zu erläutern.
Der Wert 0 in einem der Felder 'd0'-'d3' zeichnet einen
Datensatz als Elternelement aus. Der in der Tabelle 'structure'
erste Datensatz ist das Elternelement des zweiten und der
zweite Elternelement des ersten. Was aus meinem Beispiel
tatsächlich nicht hervorgeht, ist ob ein Elternelement
mehrere Kinder haben kann. Ja. Folgender Datensatz ist
auch denkbar:

site_id | d0 | d1 | d2 | d3 |                          titles |
--------|----|----|----|----|---------------------------------|
      7 |  2 |  2 |  0 |  0 |                  string5/string7|

Ich denke, dass ich mit JOINS ein Resultset hinbekomme, dass
so aussieht:

site_id | d0 | d1 | d2 | d3 | title_0 | title_1 | title_2 | title_3 |
--------|----|----|----|----|-----------------------------|---------|
      1 |  1 |  0 |  0 |  0 | string1 |    NULL |    NULL |    NULL |
--------|----|----|----|----|---------|---------|---------|---------|
      2 |  1 |  1 |  0 |  0 | string1 | string2 |    NULL |    NULL |
--------|----|----|----|----|-----------------------------|---------|
      2 |  1 |  1 |  0 |  0 | string1 | string2 | string3 |    NULL |
--------|----|----|----|----|-----------------------------|---------|
      2 |  1 |  1 |  0 |  0 | string1 | string2 | string3 | string4 |
--------|----|----|----|----|-----------------------------|---------|

... aber nicht ob das als Subselect für ein Update gehen
würde, weil ich in dem Subselect auf die selbe Tabelle
zugreife, die ich updaten will. Buw. wie ich anders da
heran gehen sollte.

beste gruesse,
heinetz

[Vinzenz Mai]

Hallo,

es ist mir vollkommen klar, dass das ein ziemlich und ausserdem
unnötig kompliziertes Konstrukt ist. Und ich wüsste, dass und
wie ich es heute anders machen würde. Es steht zur Zeit aber
nicht zur Debatte, dieses Kontrukt an sich zu ändern.

konnte ich das wissen? Nein, Du hattest es vorher nicht gesagt.

Obwohl ich mittlerweile dafon ausgehe, dass mich die Diskussion
hier in meinem Vorhaben nicht weiterbringen wird, weil ich mein

• unnötig kompliziertes - Konstrukt und das, was ich eigentlich
  vorhabe nicht erklären können werde, will ich es noch einmal
  versuchen:

Das mit dem Elternelement hatte ich versucht zu erläutern.
Der Wert 0 in einem der Felder 'd0'-'d3' zeichnet einen
Datensatz als Elternelement aus. Der in der Tabelle 'structure'
erste Datensatz ist das Elternelement des zweiten und der
zweite Elternelement des ersten.

in Tabellen gibt es keine "Ordnung", es sei denn Du sortierst nach einem Sortierkriterium.

site_id	d0	d1	d2	d3	titles
7	2	2	0	0	string5/string7

Ich denke, dass ich mit JOINS ein Resultset hinbekomme, dass
so aussieht:

mit Joins und 'ner UNION

site_id | d0 | d1 | d2 | d3 | title_0 | title_1 | title_2 | title_3 |
--------|----|----|----|----|-----------------------------|---------|

1	1	0	0	0	string1	NULL	NULL	NULL
2	1	1	0	0	string1	string2	NULL	NULL
--------	----	----	----	----	-----------------------------	---------
2	1	1	0	0	string1	string2	string3	NULL
--------	----	----	----	----	-----------------------------	---------
2	1	1	0	0	string1	string2	string3	string4
--------	----	----	----	----	-----------------------------	---------

und noch ein bisschen besser. Um aber wirklich zu sehen, wie's richtig funktioniert, wären noch ein paar Datensätze zusätzlich sinnvoll :-)

OK, fangen wir an:

1. Abfrage: ermittle alle Einträge nullter Ebene
Bedingung: d0 <> 0, d1 bis d3 alle gleich 0

SELECT  
    s.site_id,  
    s.d0,  
    s.d1,  
    s.d2,  
    s.d3,  
    c.title title_0,  
    NULL title_1,  
    NULL title_2,  
    NULL title_3  
    c.title titles  
FROM  
    structure s  
INNER JOIN  
    content c  
ON  
    s.site_id = c.site_id  
WHERE  
    s.d0 <> 0 AND s.d1 = 0 AND s.d2 = 0 AND s.d3 = 0

Ergebnis:
site_id  d0  d1  d2  d3  title_0  title_1  title_2  title_3  titles
-------- --- --- --- --- -------- -------- -------- -------- ----------
1        1   0   0   0   string1  NULL     NULL     NULL     string1
5        2   0   0   0   string5  NULL     NULL     NULL     string5

2. Abfrage: Ermittle alle Einträge der ersten Ebene
Einträge der ersten Ebene haben in d2 und d3 die Werte 0
Das Elternelement bekomme ich zum Beispiel über einen Selfjoin der structure-Tabelle mit sich selbst und Gleichheit in der Spalte d0. Zusätzlich gilt, dass das Elternelement in den Spalten d1, d2 und d3 den Wert 0 hat. Die Titel bekommen wir jeweils durch einen Join mit der content-Tabelle:

SELECT  
    s1.site_id,  
    s1.d0,  
    s1.d1,  
    s1.d2,  
    s1.d3,  
    c0.title title_0,  
    c1.title title_1,  
    NULL title_2,  
    NULL title_3,  
    CONCAT(c0.title, '/', c1.title) titles  
FROM  
    structure s1  
INNER JOIN  
    structure s0  
ON  
    s1.d0 = s0.d0 AND s0.d1 = 0 AND s0.d2 = 0 AND s0.d3 = 0  
INNER JOIN  
    content c0  
ON  
    s0.site_id = c0.site_id  
INNER JOIN  
    content c1  
ON  
    s1.site_id = c1.site_id  
WHERE  
    s1.d0 <> 0 AND s1.d1 <> 0 AND s1.d2 = 0 AND s1.d3 = 0;

Ergebnis:

site_id  d0  d1  d2  d3  title_0  title_1  title_2  title_3  titles
-------- --- --- --- --- -------- -------- -------- -------- -------
2        1   1   0   0   string1  string2  NULL     NULL     string1/string2
6        2   1   0   0   string5  string6  NULL     NULL     string5/string6
7        2   2   0   0   string5  string7  NULL     NULL     string5/string7

3. Abfrage: Ermittle alle Einträge der zweiten Ebene
Einträge der zweiten Ebene haben in d0, d1 und d2 Inhalte ungleich 0, in d3 den Wert 0 (WHERE-Klausel).
Das Elternlement aus der ersten Ebene bekommen wir durch einen Selfjoin mit der Bedingung, dass d0 und d1 gleiche Inhalte haben und der Elterndatensatz in den Spalten d2 und d3 den Wert 0 aufweist.
Das Elternelement der nullten Ebene bekommen wir durch einen weiteren Selfjoin genau wie in der zweiten Abfrage.
Danach joinen wir dreimal mit der content-Tabelle und bekommen somit die Inhalte für title_0 bis title_2, title_3 ist bei Einträgen der zweiten Ebene NULL.

SELECT  
    s2.site_id,  
    s2.d0,  
    s2.d1,  
    s2.d2,  
    s2.d3,  
    c0.title title_0,  
    c1.title title_1,  
    c2.title title_2,  
    NULL title_3,  
    CONCAT(c0.title, '/', c1.title, '/', c2.title) titles  
FROM  
    structure s2  
INNER JOIN  
    structure s1  
ON  
    s2.d0 = s1.d0 AND s2.d1 = s1.d1 AND s1.d2 = 0 AND s1.d3 = 0  
INNER JOIN  
    structure s0  
ON  
    s1.d0 = s0.d0 AND s0.d1 = 0 AND s0.d2 = 0 AND s0.d3 = 0  
INNER JOIN  
    content c0  
ON  
    s0.site_id = c0.site_id  
INNER JOIN  
    content c1  
ON  
    s1.site_id = c1.site_id  
INNER JOIN  
    content c2  
ON  
    s2.site_id = c2.site_id  
WHERE  
    s2.d0 <> 0 AND s2.d1 <> 0 AND s2.d2 <> 0 AND s2.d3 = 0;

Ergebnis:

site_id d0 d1 d2 d3 title_0 title_1 title_2 title_3 titles
------- -- -- -- -- ------- ------- ------- ------- -------
3        1  1  1  0 string1 string2 string3 NULL    string1/string2/string3

Noch grausamer wird es für Ebene 3: Dreifacher Selfjoin der structure-Tabellen, um die Elternbeziehung vom Blatt bis zur Wurzel aufzulösen und vierfache Verjoinung mit den content-Tabellen, aber ganz analog zum bisherigen Vorgehen.

SQL und Ergebnis weggelassen :-)

Nun können wir die vier Abfragen per UNION verbinden und bekommen in titles die gewünschten Werte mittels einer einzigen (und ganz sicher nicht besonders performanten Abfrage). Möglicherweise kann man über ähnlich komplizierte verschachtelte Subselects das ganze auch hinbekommen und sich von EXPLAIN erklären lassen, was weniger unperformant ist.

Es bleibt das Problem, dass MySQL es jedoch nicht zulässt, was Du festgestellt hast.

... aber nicht ob das als Subselect für ein Update gehen
würde, weil ich in dem Subselect auf die selbe Tabelle
zugreife, die ich updaten will. Buw. wie ich anders da
heran gehen sollte.

Diese Problem kannst Du zum Beispiel dadurch lösen, dass Du das Ergebnis in eine temporäre Tabelle schreibst und anschließend das Update der structure-Tabelle mit Hilfe der temporären Tabelle vornimmst. Zusätzlich musst Du sicherstellen, dass keine Änderungen zwischen diesen beiden Schritten vorgenommen werden, zum Beispiel durch explizites Sperren der beteiligten Tabellen. (Das versäumt übrigens Dein Beispiel-PHP-Code).

Es sollte allerdings nur einmal erforderlich sein, die ganze Tabelle upzudaten. Bei Änderungen in Tabelle content sollten nur die betroffenen Datensätze geändert werden, was in erträglicher Geschwindigkeit erfolgen könnte.

Freundliche Grüße

Vinzenz

[heinetz]

Hi,

konnte ich das wissen? Nein, Du hattest es vorher nicht gesagt.

Ja, das ist scheinbar mein grundsätzliches Kommunikationsproblem,
dass ich in diesem Forum immer habe. Ich stehe vor einer
gewissen Aufgabe, die ich hier formuliere und die Diskussion führt
dann dazu, dass die Aufgabe an sich diskutiert wird, was mich der
Lösung nicht näher bringt. Dafür führt es aber hin und wieder zu
neuen Erkenntnissen, wie z.B. Deinen durchaus wertvollen Hinweis
auf nested sets.

mit Joins und 'ner UNION

UNION muss ich mir ansehen.

und noch ein bisschen besser. Um aber wirklich zu sehen, wie's richtig funktioniert, wären noch ein paar Datensätze zusätzlich sinnvoll :-)

Ich habe die, die mir schwieriger erschienen weggelassen.
Aber hier kommen sie ;)

site_id | d0 | d1 | d2 | d3 |                          titles |
--------|----|----|----|----|---------------------------------|
      8 |  0 |  0 |  0 |  0 |                               / |
--------|----|----|----|----|---------------------------------|
      9 |  0 |  1 |  0 |  0 |                        /string9/|

Diese Problem kannst Du zum Beispiel dadurch lösen, dass Du das Ergebnis in eine temporäre Tabelle schreibst und anschließend das Update der structure-Tabelle mit Hilfe der temporären Tabelle vornimmst. Zusätzlich musst Du sicherstellen, dass keine Änderungen zwischen diesen beiden Schritten vorgenommen werden, zum Beispiel durch explizites Sperren der beteiligten Tabellen. (Das versäumt übrigens Dein Beispiel-PHP-Code).

Ist mir klar, dass ich das versäume, das mit der temporären
Tabelle und dem sperren von Tabellen muss ich mir auch ansehen.

Es sollte allerdings nur einmal erforderlich sein, die ganze Tabelle upzudaten. Bei Änderungen in Tabelle content sollten nur die betroffenen Datensätze geändert werden, was in erträglicher Geschwindigkeit erfolgen könnte.

Ich habe bisher immer sämtliche Datensätze geändert, weil ich
nicht mit Gewissheit sagen konnte, welche die betroffenen sind.
Und ich denke, ich werde anhand Deiner Lösung erstmal
ausprobieren alle Datensätze upzudaten und hinterher darüber
nachdenken, welche die betroffenen Datansätze sind.

danke bis hierher und

beste gruesse,
heinetz

[Vinzenz Mai]

Hallo,

Ich habe die, die mir schwieriger erschienen weggelassen.
Aber hier kommen sie ;)

site_id	d0	d1	d2	d3	titles
8	0	0	0	0	/
--------	----	----	----	----	---------------------------------
9	0	1	0	0	/string9/

kann ich das so verstehen, dass die Datensätze, die in d0 den Wert 0 aufweisen, mit einem Slash enden - und Datensätze, die in d0 *nicht* den Wert 0 aufweisen, nicht auf einen Slash enden?

Gilt weiter, dass es nur eine Ebene führender Nullen geben darf?
Wenn ja, sollte das kein großes Problem darstellen.

Freundliche Grüße

Vinzenz

[heinetz]

Hallo,

kann ich das so verstehen, dass die Datensätze, die in d0 den Wert 0 aufweisen, mit einem Slash enden - und Datensätze, die in d0 *nicht* den Wert 0 aufweisen, nicht auf einen Slash enden?

nein, das lässt sich leider nur mit meiner Nachlässigkeit,
was die Beispiel-Daten angeht begründen. Entschuldigung!

Ich denke, ich komme Dein Lösungsvorschlag ist geht schon
so sehr in's Detail, dass ich die Aufgabe darauf basierend
umsetzen kann. Weiter in's Detail zu gehen macht an dieser
Stelle keinen Sinn mehr, weil jetzt Ausnahmen und Sonderfälle
dazukommen und ich sicher beim Abstahieren dieser in Beispiel-
daten Fehler machen werde.

tausend Dank für Deine Mühe
bis hierher und

beste gruesse,
heinetz

[heinetz]

Hi,

Um hierarchische Beziehungen in einer relationalen Datenbank darzustellen, eignet sich in vielen Fällen das Nested-Set-Muster.

auch wenn ich das in dieem Moment, wie beschrieben,
nicht verfolgen kann nochmal danke für diesen sehr
guten Hinweis!

beste gruesse,
heinetz