DB neu durchnumerieren

[chmee]

Ok, Du hast zumindest mal das praktische Beispiel genannt, eine Fotogalerie, wo Du mit der Nummerierung die Reihenfolge der Bilder bestimmst. Wie sieht es aus, wenn Du die Reihenfolge einfach nur per kommaseparierter Indexliste in einer Usertabelle speicherst?

Könnte so aussehen: 1,16,3,4,6,7,9,15,8

(Auch wenn es keine User-Tabelle gibt, mach einfach eine..) Diese Indexliste musst natürlich Du zusammenstellen. Eine jQuery-Anwendung, wo Du die Thumbnails in die richtige Reihenfolge schiebst, und dann eben jene Reihenfolge der Bild-Id's abspeicherst - damit erledigt sich das mühselige Umsortieren der Bilder in SQL. Diese Liste könnte bunt durcheinandergewürfelt sein, und entspricht tatsächlich mehr der Philosophie von SQL als Deine ursprüngliche Idee.

Abhängig von der Menge an Bildern (beschränkst Du sie oder sollen alle gezeigt werden?) könnte man auch ORDER BY CASE benutzen.

Links:
http://jqueryui.com/demos/draggable/#sortable
http://www.devx.com/tips/Tip/17288

mfg chmee

 

[Mr_Scotch]

So ich habe mal den SQL Code vom Napofis probiert.
Funktioniert genau so wie ich mir das vorgestellt habe.
Der Lösungsvorschlag von chmee ist für die Zukunft bestimmt die bessere Lösung aber da muss ich
zu viel anpassen. Aber ich werde es im Hinterkopf behalten.
Noch mal vielen Dank für Eure Hilfe.
Gruß Ingo

 

[SE]

Dann makiere den Thread bitte als ERLEDIGT wenn dein Problem gelöst ist.

 

[Mr_Scotch]

Ok leider ist das Thema doch noch nicht ganz durch.
Ich habe den SQL String an meine Tabelle angepasst und unter PHPMyAdmin getestet.

SET @orVar:=0; 
UPDATE `images` SET `sord_order` = (@orVar:=@orVar+1) 
WHERE group_id = 1 ORDER BY `sord_order` ASC;

Kein Fehler.
Wenn ich den selben String, allso noch nciht mal die Group_id per Variable in PHP schreibe,
bekomme ich diese Meldung.

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE `images` SET `sord_order` = (@orVar:=@orVar+1) WHERE group_i' at line 1

Es handelt sich beides mal um die selbe Datenbank (Xampp auf meinem Rechner)
Auch auf dem Webserver habe ich es getestet und bekomme auch die Fehlermeldung.

Komme einfach nicht dahinter. Habe auch eine extra Software für die Datenbank Administration.
Auch dort läuft es ohne Problem nur per PHP Script nicht.
Gruß Ingo

 

[Matthias Reitinger]

Wie sieht es aus, wenn Du die Reihenfolge einfach nur per kommaseparierter Indexliste in einer Usertabelle speicherst?

Könnte so aussehen: 1,16,3,4,6,7,9,15,8

Damit würde das Schema nicht mal mehr die 1. Normalform erfüllen, mit allen negativen Konsequenzen daraus. Das vom OP vorgeschlagene Schema ist schon in Ordnung.

Diese Liste könnte bunt durcheinandergewürfelt sein, und entspricht tatsächlich mehr der Philosophie von SQL als Deine ursprüngliche Idee.

Mit Verlaub, aber das ist Unsinn. Die „Philosophie von SQL“ sollte wohl mit einschließen, dass man auf dem Datenbankschema einfach SQL-Abfragen formulieren kann. Die Verletzung der 1. Normalform ist da nicht gerade hilfreich. Oder wie würdest du mit einer Indexliste sämtliche Bilder in der richtigen Reihenfolge aus der Datenbank holen?

@Mr_Scotch: [phpf]mysql_query[/phpf] kann pro Aufruf nur eine einzige Abfrage verarbeiten. Du müsstest also pro Abfrage einen Aufruf machen. Besser wäre es allerdings, wenn du solche Lücken erst gar nicht auftreten lässt. Beim Löschen ginge das z.B. so:

Angenommen das Bild mit der id 21 soll gelöscht werden. Zuerst holst du dir die Gruppe und die Sortiernummer:

SELECT `group_id`, `sort_order` FROM `images` WHERE `id` = 21

Dann passt du die Sortiernummer der dahinter liegenden Bilder an:

UPDATE `images`
SET `sort_order` = `sort_order` - 1
WHERE `group_id` = $group_id AND `sort_order` > $sort_order

(Platzhalter für die Ergebnisse aus der ersten Abfrage entsprechend ersetzen).
Dann kannst du das Bild schließlich löschen und hast keine Lücken:

DELETE FROM `images` WHERE `id` = 21

Alles noch in eine Transaktion packen und fertig.

Grüße,
Matthias

 

[Mr_Scotch]

Hallo Martin,
vielen Dank für Dein Beispiel. Ich werde es ausprobieren. Vom Lesen her hört es sich sehr gut an
und denke das ich damit in Zukunft Arbeiten werde.

Mich würde aber denn noch interessieren warum ich bei Napofis Beispiel in PHP eine Fehlermeldung
bekomme und im SQL Toll (sei es PHPMyAdmin oder sonstige Anwendung) es ohne Fehler Funktioniert.

Gruß Ingo

 

[chmee]

Damit würde das Schema nicht mal mehr die 1. Normalform erfüllen, .. Mit Verlaub, aber das ist Unsinn. Die „Philosophie von SQL“ sollte wohl mit einschließen, dass man auf dem Datenbankschema einfach SQL-Abfragen formulieren kann. Die Verletzung der 1. Normalform ist da nicht gerade hilfreich. Oder wie würdest du mit einer Indexliste sämtliche Bilder in der richtigen Reihenfolge aus der Datenbank holen?

Du hast völlig recht. Die 1.Normalform gehört schon zum kleinen 1x1.. - und da mache ich schon mal Ausnahmen denn es funktioniert trotzdem und mir sind die Nachteile (in kleinen Projekten) nicht bewusst.

Ja, ich hab auch nochmal überlegt, wie ich mittels Indexliste und einfacher SQL-Query die richtige Reihenfolge bekomme. Das Ergebnis wären letztlich zwei einfache Queries (mit der Sortierung in php)

$sql_query=mysql_query("SELECT indexlist FROM userlist where userid='1'");
while($row = mysql_fetch_array($result))
  {
  $indexlist=$row['indexlist'];
  }

# wir gehen davon aus, dass alle Bilder gezeigt werden sollen
# ergo die Bilder der indexlist ins töpfchen
# die unbestimmten bilder ins kröpfchen
# (nicht ganz, sie werden einfach angehangen)

$id=0;
$toepfchen="";
$kroepfchen="";

$sql_query=mysql_query("SELECT * FROM piclist");
while($row = mysql_fetch_array($result))
  {
   $bilder[]=$row;
   }

# toepfchen
foreach($indexlist as $picid)
  {
  $toepfchen=."<div class='picdiv'><img src='".$bilder[$picid]['picurl']."'/></div>";
  }

# kroepfchen
foreach($bilder as $bild)
  {
  if(!in_array($id++,$indexlist)
    {
       $kroepfchen=."<div class='picdiv'><img src='".$bild['picurl']."'/></div>";
    }
  }
echo $toepfchen.$kroepfchen;

Das ist zugegeben nicht wirklich schöner Code, aber sollte funktionieren.. Meine Frage wäre jetzt natürlich, wie würde man die Ausgabereihenfolge per Hilfstabelle lösen? Der Königsweg wäre ja, die Ausgabe-id direkt in der Bildertabelle zu verewigen, weil dann ein einfaches ORDER BY reichen täte. Matthias, Du hast den Weg zur Löschung genannt, würdest Du den gleichen Weg zum Reinschieben/Neusortieren benutzen?

mfg chmee

 

[Matthias Reitinger]

Du hast völlig recht. Die 1.Normalform gehört schon zum kleinen 1x1.. - und da mache ich schon mal Ausnahmen denn es funktioniert trotzdem und mir sind die Nachteile (in kleinen Projekten) nicht bewusst.

Kleine Projekte haben manchmal die Eigenschaft zu mittleren Projekten heranzuwachsen. Spätestens dann ist man froh, wenn man es von Anfang an richtig gemacht hat

Ja, ich hab auch nochmal überlegt, wie ich mittels Indexliste und einfacher SQL-Query die richtige Reihenfolge bekomme. Das Ergebnis wären letztlich zwei einfache Queries (mit der Sortierung in php)

Klar, man kann auch selber im Client mit den Daten herumjonglieren (in diesem Fall: sortieren). Aber da ist es eigentlich fast immer besser, das der Komponente zu überlassen, die dafür gemacht ist – der Datenbank.

Das ist zugegeben nicht wirklich schöner Code, aber sollte funktionieren..

Besser wäre es wohl, wenn du die Bilder über den Primärschlüssel referenzierst und nicht über die Reihenfolge in der sie die Datenbank liefert. Die ist nämlich per se (ohne ORDER BY-Klausel) nicht definiert.

Meine Frage wäre jetzt natürlich, wie würde man die Ausgabereihenfolge per Hilfstabelle lösen? Der Königsweg wäre ja, die Ausgabe-id direkt in der Bildertabelle zu verewigen, weil dann ein einfaches ORDER BY reichen täte.

Das wäre wohl die schönste Lösung und so macht es ja der OP bisher auch schon.

Matthias, Du hast den Weg zur Löschung genannt, würdest Du den gleichen Weg zum Reinschieben/Neusortieren benutzen?

Reinschieben funktioniert ganz ähnlich wie Löschen: angenommen man kennt die Gruppen-Nummer $group_id und die einzufügende Stelle $sort_order. Dann verschiebt man zunächst alle Bilder hinter der Einfügestelle um eins nach hinten:

UPDATE `images`
SET `sort_order` = `sort_order` + 1
WHERE `group_id` = $group_id AND `sort_order` >= $sort_order

Und fügt schließlich das neue Bild ein:

INSERT INTO `images`(`group_id`, `sort_order`, …)
VALUES ($group_id, $sort_order, …)

Will man die Position eines einzelnen Bildes verändern, so macht man dasselbe wie bei einer Löschung und anschließendem Einfügen, nur dass man DELETE + INSERT durch ein UPDATE ersetzt. Genauso kann man eine zusammenhängende Kette von Bildern an einen neuer Ort verschieben. Wie da die Abfragen auszusehen haben, kann man sich leicht auf einem Blatt Papier überlegen

Grüße,
Matthias

 

[chmee]

Danke für die umfassende Aufklärung. Mit ein paar mehr Minuten hätt ich auch eher zum Primärschlüssel gegriffen Unglücklich war letztlich nur meine Aussage, dass meine kommaseparierte Liste mehr SQL ist, denn das stimmt nicht

mfg chmee

 

[Napofis]

Angenommen das Bild mit der id 21 soll gelöscht werden. Zuerst holst du dir die Gruppe und die Sortiernummer:

SELECT `group_id`, `sort_order` FROM `images` WHERE `id` = 21

Dann passt du die Sortiernummer der dahinter liegenden Bilder an:

UPDATE `images`
SET `sort_order` = `sort_order` - 1
WHERE `group_id` = $group_id AND `sort_order` > $sort_order

(Platzhalter für die Ergebnisse aus der ersten Abfrage entsprechend ersetzen).
Dann kannst du das Bild schließlich löschen und hast keine Lücken:

DELETE FROM `images` WHERE `id` = 21

Alles noch in eine Transaktion packen und fertig.

Die Variablen kann man eigentlich gleich beim SQL-Server lassen, dann spart man sich das fetchen und String zusammenkleben.

SELECT `id`, `group_id`, `sort_order`
INTO @id, @group_id, @sort_order 
FROM `images`
WHERE `id` = 21;

UPDATE `images`
SET `sort_order` = `sort_order` - 1
WHERE `group_id` = @group_id 
    AND `sort_order` > @sort_order;

DELETE FROM `images`
WHERE `id` = @id;