# DB neu durchnumerieren



## Mr_Scotch (17. September 2011)

Hallo,
ich habe eine DB auf meiner Webseite in der ich Einträge per Schaltfläche hoch oder runter schieben (sortieren) kann.
Für die Sortierung habe ich in der DB ein Feld "sortorder" vom Type Int.
Wenn ich jetzt einen Eintrag lösche, entsteht in der sortorder ein Lücke.
z.b.
vorher 
1
2
3
nach dem löschen
1
3

wie kann ich die Lücke vermeiden bzw. das Feld sordorder aktualisieren das keine Lücke 
entsteht. 
Ich hoffe ich habe mich nicht zu kompliziert ausgedrückt.
Ich habe schon an eine Hilfstabelle gedacht aber das kommt mir sehr aufwendig vor.
Gruß Ingo


----------



## chmee (17. September 2011)

Die Frage ist, ob es überhaupt notwendig ist, die Nummerierung in der DB abzuspeichern. Speicher die DB-Query in einem weiteren Array, schon hast Du eine "Nummerierung ($array[*0*]['name']), die auch nachrückt. In der DB jedenfalls sehe ich eigentlich keinen Sinn, so eine banale Nummernreihenfolge zu speichern, geschweige denn nachzubessern.

mfg chmee


----------



## SE (18. September 2011)

@chmee
Ich denke das TO hier weniger einen KEY oder INDEX meint als das er eine Reihenfolge haben will unabhängig von der Reihenfolge wie die Datensätze in der DB stehen oder manipuliert werden.

@TO
Ich denke das du das innerhalb der DB nicht machen können wirst ... es für sowas also nur sehr schwer wird ein entsprechendes Query zusammenzubasteln ... falls es überhaupt geht.
Du wirst schon nach dem DELETE ein weiters SELECT machen und dann in welcher Sprache auch immer dort mit einer for()-Schleife deine SortOrder korrigieren müssen. Das es eine Datenbank gibt die das innerhalb des DELETE-Query machen kann bezweifel ich da die DB ja erst nach dem vollständigen DELETE weis wie viele Datensätze freigeworden sind musst du so oder so ein zweites Query absetzen ... und um das ganze relativ einfach zu halten und nicht mit überlangen Queries Probleme zu bekommen kannst du es dann auch gleich in deinem Client machen.


----------



## chmee (18. September 2011)

@SPiKEe
Es klingt aber verdächtig nach nem AutoIncrement. Letztlich gibt es in einer SQL-DB keine Reihenfolge, weil sich jene mit der Query ändert. Deswegen mein Rat, sich mal Gedanken zu machen, die Nummerierung außerhalb von SQL zu machen.

mfg chmee


----------



## SE (18. September 2011)

Naja klar ... versteh ich doch ... das die Reihenfolge des Result vom Query abhängt. Soweit ist das ja klar. Aber was würde TO eine Speicherung irgendeines Index *egal ob jetzt als primary auto_increment oder für sonstwas* *außerhalb* der DB bringen ? Und wie würdest du sowas umsetzen und dann noch in sync mit den Datensätzen ?


----------



## Mr_Scotch (18. September 2011)

Moin,
erst mal danke für Eure Antworten.
Als es hat nichts mit einem Key oder Index zu tun.
Das ganze ist für eine Fotogalerie. Und um dort die Bilder in gewünschter reihen folge anzuzeigen,
habe ich die sortorder in der DB zusammen mit der Gruppen Zuordnung und er Namen und Kommentare 
zu den Fotos. Wenn jetzt ein Foto hinzu kommt welches ich an einer Bestimmten Stelle haben will kann
ich dies über die Sortorder verschieben und bei bedarf auch löschen.
Und auf das Löschen bezieht sich mein oben gestellt frage.
Gruß Ingo


----------



## Napofis (18. September 2011)

@Spikee: Man kann es ganz einfach innerhalb der Datenbank über Trigger machen.

Hier mal ein Beispiel wie man es machen könnte, inwiefern es einen Sinn ergibt lass ich mal weg.

```
SET @orVar:=0;
UPDATE `Tabelle`
SET `ordering` = (@orVar:=@orVar+1)
ORDER BY `ordering` ASC
```


----------



## SE (18. September 2011)

Mr_Scotch hat gesagt.:


> Moin,
> erst mal danke für Eure Antworten.
> Als es hat nichts mit einem Key oder Index zu tun.
> Das ganze ist für eine Fotogalerie. Und um dort die Bilder in gewünschter reihen folge anzuzeigen,
> ...



Sorry ... aber ich sehe deine Frage nicht ...
Kommt denn irgendwas durch ein ander wenn du einen Datensatz löschst oder entsteht lediglich dieses "Loch".
Bei dem Loch : ja gut ... und ? Einfach die Galerie so programmieren das das Loch ignoriert wird. Weil wie "verschiebst" du denn ein Bild ? In dem du außerhalb der DB in deiner Galerie diese SortOrder neu berechnest ... und wo ist es jetzt das Problem dieses mit einem Loch zu tun ?
Wie gesagt : ich sehe deine Frage nicht.


----------



## Mr_Scotch (18. September 2011)

Das Problem ist wenn ich ein neues Bild zu einer Gruppe hochlade.
Wenn die Reihenfolge Stimmt, kann ich per Cout abfragen wie viele Bilder der Gruppe angehören und 
dem neuen Bild +1 in der Sortorder am Ende anhängen.
Wenn das Loch ist habe ich unter Umständen das Bild an einer falschen Position.
z. B.
1
2
5
7
beim zählen der Bilder würde das neue Bild auch die Sortorder 5 bekommen und nicht 8 um als letztes
Bild zu erscheinen
Gruß
Ingo


----------



## Mr_Scotch (18. September 2011)

Napofis hat gesagt.:


> @Spikee: Man kann es ganz einfach innerhalb der Datenbank über Trigger machen.
> 
> Hier mal ein Beispiel wie man es machen könnte, inwiefern es einen Sinn ergibt lass ich mal weg.
> 
> ...


 Hm schlau werde ich daraus nicht unbedingt aber ich werde es mal testen.
Trotzdem Danke für das Beispiel.
Gruß Ingo


----------



## chmee (18. September 2011)

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 (18. September 2011)

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 (18. September 2011)

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


----------



## Mr_Scotch (19. September 2011)

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 (19. September 2011)

chmee hat gesagt.:


> 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.



chmee hat gesagt.:


> 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 (19. September 2011)

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 (19. September 2011)

Matthias Reitinger hat gesagt.:


> 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 (20. September 2011)

chmee hat gesagt.:


> 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 



chmee hat gesagt.:


> 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.



chmee hat gesagt.:


> 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.



chmee hat gesagt.:


> 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.



chmee hat gesagt.:


> 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 (20. September 2011)

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 (21. September 2011)

Matthias Reitinger hat gesagt.:


> 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
> ...


 
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;
```


----------

