Re-Ordering Menu Structure

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Re-Ordering Menu Structure

Post by kendall »

Hey guys,

I've been working on this for days now but im close yet far away from a solution
Im working on a database driven dynamic menu structure

Code: Select all

INSERT INTO `cms_menu_test` (`MenuID`, `MenuName`, `MenuParent`, `MenuLink`, `ContentID`, `MenuGroup`, `MenuLevel`, `MenuDepth`, `NavOrder`, `SiteID`, `Status`) VALUES
  (4000,'About Us',0,'index.php/about/',NULL,'about',1,0,12,1,1),
  (4001,'Who We Are',4000,'index.php/about/4001',NULL,'about',2,1,13,1,1),
  (4002,'Strategic Partners',4000,'index.php/about/4002',NULL,'about',2,1,14,1,1),
  (4003,'Contact Us',4000,'index.php/about/4003',NULL,'about',2,1,16,1,1),
  (7000,'Services',0,'index.php/services',NULL,'services',1,0,2,1,1),
  (43981,'Client Success',0,'index.php/success/F2C3E0/43981','F2C3E0','success',1,0,1,1,1),
  (71041,'Graphic Design',7000,'none',NULL,'services',2,1,8,1,1),
  (96411,'Print',71041,'none',NULL,'services',2,2,9,1,1),
  (41213,'Press',96411,'none',NULL,'services',2,3,11,1,1),
  (14119,'Call Cards',96411,'none',NULL,'services',2,3,10,1,1),
  (44316,'Web Development',7000,'none',NULL,'services',2,1,3,1,1),
  (16175,'Internet Programming',44316,'none',NULL,'services',2,2,5,1,1),
  (71416,'PHP',16175,'none',NULL,'services',2,3,7,1,1),
  (37341,'MySQL',16175,'none',NULL,'services',2,3,6,1,1),
  (94633,'Flash Development',44316,'none',NULL,'services',2,2,4,1,1);
and i am trying to impliment a menu re-ordering method for menus within their levels

Code: Select all

// check if any submenus need to be moved
		$query = "SELECT COUNT(submenus.NavOrder) AS `SubMenus` FROM cms_menu_test AS menu LEFT JOIN cms_menu_test AS submenus ON submenus.MenuGroup = menu.MenuGroup AND submenus.NavOrder BETWEEN menu.NavOrder+1 AND (SELECT NavOrder FROM cms_menu_test WHERE NavOrder > $navOrder AND MenuDepth = $menuDepth ORDER BY NavOrder LIMIT 1)-1 AND submenus.MenuDepth > menu.MenuDepth WHERE menu.NavOrder = $navOrder";
		$results = $sqlConn->query($query);
		// if the count is more than 0 set max order to move to
		if(($submenus = $sqlConn->getResult("SubMenus")) > 0)
			$maxNavOrder = $navOrder + $submenus;
		else $maxNavOrder = $navOrder;
		// set the menus to negative
		$query = "UPDATE cms_menu_test SET NavOrder = 0-NavOrder WHERE NavOrder BETWEEN $navOrder AND $maxNavOrder";
		$results = $sqlConn->query($query);
		// check for no menus to be moved up
		$query = "SELECT COUNT(submenus.NavOrder) AS `Position` FROM cms_menu_test AS menu LEFT JOIN cms_menu_test AS submenus ON submenus.MenuGroup = menu.MenuGroup AND submenus.NavOrder BETWEEN menu.NavOrder+1 AND (SELECT NavOrder FROM cms_menu_test WHERE NavOrder > $position AND MenuDepth = menu.menuDepth ORDER BY NavOrder LIMIT 1)-1 AND submenus.MenuDepth > menu.MenuDepth WHERE menu.MenuID = '$moveID' ORDER BY menu.NavOrder";
		$results = $sqlConn->query($query);
		// if the count is more than 0 set max order to move to
		if(($newposition = $sqlConn->getResult("Position")) > 0)
			$maxPosition = $position + $newposition;
		else $maxPosition = $position;
		// shift menus between current and proposed order
		$sqlConn->query("SET @Pos = 0"); // set variable
		$query = "UPDATE cms_menu_test SET NavOrder = ($position+$submenus) + (SELECT @Pos:=@Pos+1) WHERE NavOrder BETWEEN $position AND $maxPosition ORDER BY NavOrder ASC";
		$results = $sqlConn->query($query);
		// update menu
		$query = "UPDATE cms_menu_test SET NavOrder = $position WHERE MenuID = '$menuID'";
		$results = $sqlConn->query($query);
		// update menu's submenus if any
		if($submenus >0){
			$sqlConn->query("SET @Pos = 0"); // set variable
			$query = "UPDATE cms_menu_test SET NavOrder = $position + (SELECT @Pos:=@Pos+1) WHERE NavOrder < 1 AND MenuGroup = '$menuGroup' ORDER BY MenuDepth, NavOrder DESC";
			$results = $sqlConn->query($query);	
			// exit;
		}
But its so not working in some aspects...so im hoping i can get some insight as to what im doing wrong or right here

Kendall
Post Reply