Position update after delete

[Gadziu]

Hey, I have problem with update the position of record in DB. Delete from DB is going fine, but not the update querry... For example. I have number of photos: 1,2,3,4,5, next I remove 2, so every higher position should change about -1, but they don't. I tried many combination of UPDATE querry, but no good repsonse.

 

usun_a.php

<?php 
    $plik = $_POST['zdjecie']; 
    $id = $_POST['numer']; 
    $test = file_exists($plik); 
    $pozycja = $_POST['a_pozycja']; 
    include "connection.php"; 
    connection(); 
     
    echo $pozycja; 
    if (!$test) 
    { 
        echo "Brak takiego zdjęcia na serwerze<br />"; 
        mysql_query('UPDATE  `mulino_cms`.`apartament` SET  `apartament_pozycja_obraz` =  apartament_pozycja_obraz-1 WHERE  `apartament`.`apartament_pozycja_obraz` > $pozycja DESC'); 
        $usun = mysql_query("DELETE FROM mulino_cms.apartament WHERE apartament.apartament_id=$id") 
        or die(mysql_error()); 
        if ($usun) 
        { 
             
            echo "<b><span style=\"color: #00FF00\">Zdjęcie usunięte z bazy</span></b> <br />"; 
        } 
        else 
        { 
            echo "<b><span style=\"color: #FF0000\">Nie można usunąć zdjęcia z bazy</span></b>";         
        } 
    } 
    else 
    { 
        $usuns = unlink($plik); 
        mysql_query('UPDATE  `mulino_cms`.`apartament` SET  `apartament_pozycja_obraz` =  apartament_pozycja_obraz-1 WHERE  `apartament`.`apartament_pozycja_obraz` > $pozycja DESC'); 
        $usun = mysql_query("DELETE FROM mulino_cms.apartament WHERE apartament.apartament_id=$id") 
        or die(mysql_error()); 
        if ($usun && $usuns) 
        { 
            echo "<b><span style=\"color: #00FF00\">Zdjęcie usunięte</span></b>"; 
        } 
        else 
        { 
            if(!$usuns) 
            { 
                echo "<b><span style=\"color: #FF0000\">Nie można usunąć zdjęcia z serwera</span></b>"; 
            } 
            else if(!$usun) 
            { 
                echo "<b><span style=\"color: #FF0000\">Nie można usunąć zdjęcia z bazy</span></b>"; 
            } 
            else 
            { 
                echo "<span style=\"color: #FF0000\"><b>Nie można usunąć zdjęcia</b></span>";     
            } 
                 
        }     
    } 
     
     
?>

DB structure

-- phpMyAdmin SQL Dump
-- version 3.4.3.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Czas wygenerowania: 06 Lip 2013, 20:59
-- Wersja serwera: 5.1.68
-- Wersja PHP: 5.2.17

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Baza danych: `mulino_cms`
--

-- --------------------------------------------------------

--
-- Struktura tabeli dla  `apartament`
--

CREATE TABLE IF NOT EXISTS `apartament` (
  `apartament_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `apartament_pozycja_obraz` int(10) NOT NULL,
  `apartament_adres` varchar(400) COLLATE utf8_general_mysql500_ci NOT NULL,
  `apartament_opis` text COLLATE utf8_general_mysql500_ci NOT NULL,
  PRIMARY KEY (`apartament_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci AUTO_INCREMENT=32 ;

--
-- Zrzut danych tabeli `apartament`
--

INSERT INTO `apartament` (`apartament_id`, `apartament_pozycja_obraz`, `apartament_adres`, `apartament_opis`) VALUES
(29, 1, '../images/apartament/images.jpg', 'sd'),
(31, 3, '../images/apartament/f-one-bandit-vi.jpg', '545');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

[Barand]

 

 

next I remove 2, so every higher position should change about -1

 

No, they shouldn't. An id is a unique identifier for a record, not a sequence number, and should not change for the life of that record. Nor should the ids of deleted records be reallocated to new records. If you need to maintain the sequence that that the records were added use something else, like a timestamp.