Webseiten Planen Entwickeln und Pflegen

Ordnen der Tabelle

Ziel ist es Datensätze einer Tabelle in der Reihenfolge zu ändern. Ich habe hier ein PHP Script gepostet mit dem das relativ einfach möglich ist.

Zusätzlich ist in der MySQL Datenbank ein Feld, hier mit den Namen “position” notwendig.  Per “Up” und “Down” Button kann man dann den Datensatz in beide Richtungen verschieben.

Die Anordnung wird dann wieder in die Datenbank zurückgeschrieben.

include('config.php');

if($art == ''){ $art = 'liste';};

switch($art){

case 'liste':
print "<h3>Tabelle</h3>";
$liste = mysql_query("SELECT * FROM test ORDER BY position ASC");
print "<table border='1'>";
print "<tr><td>id</td><td>name</td><td>position</td><td>up</td><td>down</td></tr>";
while($feld = mysql_fetch_object($liste)){
print "<tr><td>".$feld->id."</td><td>".$feld->name."</td><td>".$feld->position."</td><td><a href='".$PHP_SELF."?art=order&id=".$feld->id."&action=up'>up</a></td><td><a href='".$PHP_SELF."?art=order&id=".$feld->id."&action=dn'>down</a></td></tr>";
};
print "</table>";

break;

case 'order':

// $_GET['action'] will indicate up/dn
$action = isset($_GET['action']) ? $_GET['action'] : false; // condition input and set default
// $_GET['id'] is the id to move up/dn
$id = isset($_GET['id']) ? (int)$_GET['id'] : false; // condition input and set default
if(!$id){
die("No id supplied");
}
if($action <> "up" && $action <> "dn"){
die("Invalid action selected");
}

// get the current position of the id
$query = "SELECT position FROM test WHERE id = $id";
$result = mysql_query($query) or die("Query failed: $query<br />Mysql error: " . mysql_error());
if(mysql_num_rows($result) <> 1){
echo "There is no id: $id<br />";
} else {
list($get_position) = mysql_fetch_row($result); // get the position of the requested id
if($action == "up"){
// get the id and position of that and the next lower position
$query = "SELECT id, position FROM test WHERE position <= $get_position ORDER BY position DESC LIMIT 2";
} else {
// get the id and position of that and the next higher position
$query = "SELECT id, position FROM test WHERE position >= $get_position ORDER BY position LIMIT 2";
}
$result = mysql_query($query) or die("Query failed: $query<br />Mysql error: " . mysql_error());
// test how many rows (if less than 2, there is no locaiton to move to)
if(mysql_num_rows($result) < 2){
// echo "You are already at the limit<br />";
} else {
// move up/dn one by swapping the position values
$row1 = mysql_fetch_assoc($result); // row1
$row2 = mysql_fetch_assoc($result); // row2
// update row1 (with row2's position value)
$query = "UPDATE test SET position = {$row2['position']} WHERE id = {$row1['id']}";
$result = mysql_query($query) or die("Query failed: $query<br />Mysql error: " . mysql_error());
// update row2 (with row1's position value)
$query = "UPDATE test SET position = {$row1['position']} WHERE id = {$row2['id']}";
$result = mysql_query($query) or die("Query failed: $query<br />Mysql error: " . mysql_error());
}
}

header('Location: http://'.getenv('HTTP_HOST').$PHP_SELF."?art=liste");
exit;

break;

};