SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard holmescreek's Avatar
    Join Date
    Mar 2001
    Location
    Northwest Florida
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Advanced Sorting in MySQL - Help?

    I've been working on a content management system that includes the ability for users to create and manage their own navigation menu. This includes specifying the order in which the main menus and sub-menus that belong to these main menus appear.

    This is a simplified example of two tables. The first, maimenus would contain the names of main categories such as DOGS and CATS. The second table, submenus, may contain the names of sub-categories that belong to DOGS and CATS, for example Dog Toys, Dog Treats, Cat Food, Cat Litter

    mainmenus ---- table 1

    id
    title
    sortorder


    submenus ---- table 2

    id
    mainmenuid
    title
    sortorder



    Lets look at some sample data for Table 1, mainmenus :

    ID TITLE SORTORDER
    19 DOGS 2
    24 CATS 1


    Table 2, submenus, would look something like :

    ID MAINMENUID TITLE SORTORDER
    1 19 Dog Toys 1
    2 19 Dog Treats 3
    3 19 Dog Houses 2
    4 24 Cat Food 1
    5 24 Cat Litter 2


    Typically I would do something like "select id,title,sortorder from submenus where MAINMENUID=19 order by sortorder";

    This would produce :

    ID Title SORTORDER
    1 Dog Toys 1
    3 Dog Houses 2
    2 Dog Treats 3


    Ok, now let me get the the meat of my question. If I delete Dog Houses, the sortorder being 2, is their a way to have mysql renumber the sortorder field only for the items that have only a value of 19 (DOGS) for the mainmenus field? The end result being if I delete Dog Houses the sortorder for Dog Toys would be 1, the sortorder for Dog Treats would become 2.

    I was wondering if I could do this purely in MySql?

    Note: I found an effective and easy way of achieving this with PHP. But, was thinking that if I could have MySql do it that it would be faster.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    to answer the question you asked, no

    there is a way with standard sql to renumber column values, but it is exceedingly complex, and mysql couldn't handle it because it can't do subqueries

    i have exactly the same mainnav/subnav structure as you, including a sequence column, but with two differences --

    1. both levels are in the same table

    this means i can go to three levels easily if i want -- right now two levels of navigation is perfect, but i don't have to modify the database if i want three

    2. sequence numbers are assigned 10, 20, 30, etc.

    now i can easily insert between existing entries or at the beginning, or move entries around, without renumbering

    eventually, a renumbering might be necessary, but only if you didn't choose big enough sequence number intervals to start with

    so keep your php routine handy, and hope you never have to use it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could you not just run 3 queries when deleting the submenu?

    Code:
    //NB: I don't know if this syntax can be used with MySql
    
    //Read these values into PHP variables and use in last sql statement
    SELECT	mainmenuid,
    	sortorder
    FROM	submenus
    WHERE	submenuid = [submenuid to delete]
    
    DELETE
    FROM	submenus
    WHERE	submenuid = [submenuid to delete]
    
    UPDATE	submenus
    SET	sortorder = (SM.sortorder - 1)
    FROM	submenus SM
    WHERE	SM.sortorder > [deleted submenu sortorder]
    AND	SM.mainmenuid = [deleted submenu mainmenuid]
    It's convoluted and you'd need to make sure that you always used the same function for deleting submenus. Also, as the sortorder just determines the order is there really any need to update the sortorder as the menus will still be ordered correctly.

    (Hasn't MySql got Stored Procedures or Triggers yet?)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •