Sorteringsproblem

Hansen skrev:

Får du några felmeddelanden?

Nej, det id:t som skall flyttas upp/ner får lixom bara högre och högre orderid.
Om det te.x. bara finns fyra poster med parent_page_id=123, så skall värdet i orderid inte kunna överstiga 4.

Kan du posta schemat över tbl_links samt lite data?

Det finns en risk att föregående statement är fel, men jag kan inte veta det utan att se hur tbl_links ser ut.

Kommer här:

#
# Table structure for table 'site_links'
#

CREATE TABLE site_links (  sl_pageid int(10) unsigned NOT NULL auto_increment,
 sl_parent_pageid int(11) unsigned default NULL,
 sl_linktext varchar(250) default NULL,
  sl_orderid int(11) unsigned default NULL,
  sl_url varchar(255) default '#',
  sl_menuactive enum('True','False') default 'False',
  sl_sp_relid int(10) unsigned default NULL,
  PRIMARY KEY  (sl_pageid)) TYPE=MyISAM;

Och här:

#
# Dumping data for table 'site_links'
#

INSERT INTO site_links VALUES("10", "4", "Curse of Sherwood, the", "6", "javascript:void(0);", "True", NULL);
INSERT INTO site_links VALUES("2", "1", "Commadore 64", "6", "#", "True", NULL);
INSERT INTO site_links VALUES("3", "1", "Nintendo 8-bit", "5", "#", "True", NULL);
INSERT INTO site_links VALUES("4", "2", "Adventure", "6", "#", "True", NULL);
INSERT INTO site_links VALUES("15", "2", "Platform", "5", "#", "True", NULL);
INSERT INTO site_links VALUES("6", "2", "Racing", "5", "#", "True", NULL);
INSERT INTO site_links VALUES("7", "3", "Adventure", "5", "#", "True", NULL);
INSERT INTO site_links VALUES("8", "3", "Platforms", "5", "#", "True", NULL);
INSERT INTO site_links VALUES("9", "3", "Sports", "6", "#", "True", NULL);
INSERT INTO site_links VALUES("1", "0", "Games", "6", "javascript:void(0);", "True", NULL);
INSERT INTO site_links VALUES("11", "4", "Defender of the Crown", "5", "javascript:void(0);", "False", NULL);
INSERT INTO site_links VALUES("12", "4", "Last Ninja, the", "5", "javascript:void(0);", "False", NULL);
INSERT INTO site_links VALUES("13", "1", "Spektrum", "5", "#", "False", NULL);
INSERT INTO site_links VALUES("14", "2", "Go Kart", "9", "#", "True", NULL);

Om jag förstått det rätt, så vill du sortera sl_pageid under sl_parent_pageid med hjälp av sl_orderid, korrekt?

Kan jag anta att sl_orderid i den bifogade datan är fel och att sl_orderid startar på 1 och inte överstiger antal syskon/barn?

Ja, så här:

Games{1}(0)
    Commadore 64{1}(1)
        Sports{1}(2)
        Adventure{2}(2)
        Platforms{3}(2)
    Nintendo 8-bit{2}(1)

{ }= sl_orderid.
( )=sl_parentid

Nu ska det nog funka :)

/* "age" */

  SELECT t1.sl_orderid AS me, 
         COUNT(t2.sl_orderid) AS siblings
    FROM site_links AS t1,
         site_links AS t2 
   WHERE t1.sl_pageid = 15
     AND t2.sl_parent_pageid = t1.sl_parent_pageid
GROUP BY t1.sl_pageid

pseudo kod

if direction = "up" then
  
  if me > 1 then
    // execute move left/up statement
  end if

end if

if direction = "down" then
  
  if me < siblings then
    // execute move right/down statement
  end if
  
end if

/* move left/up */

UPDATE site_links AS t1,
       site_links AS t2
   SET t2.sl_orderid = 
         CASE
           WHEN t2.sl_orderid = t1.sl_orderid THEN t1.sl_orderid - 1
           ELSE t1.sl_orderid
         END
             
  WHERE t1.sl_pageid = 15
    AND t2.sl_parent_pageid = t1.sl_parent_pageid
    AND t2.sl_orderid BETWEEN t1.sl_orderid - 1 AND t1.sl_orderid

/* move right/down */

UPDATE site_links AS t1,
       site_links AS t2
   SET t2.sl_orderid = 
         CASE
           WHEN t2.sl_orderid = t1.sl_orderid THEN t1.sl_orderid + 1
           ELSE t1.sl_orderid
         END
             
  WHERE t1.sl_pageid = 15
    AND t2.sl_parent_pageid = t1.sl_parent_pageid
    AND t2.sl_orderid BETWEEN t1.sl_orderid AND t1.sl_orderid + 1