SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Thread: Sequencing

  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2005
    Posts
    527
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sequencing

    Hello I have a dynamic menu that read data from a table like this :

    Code:
    Menu table 
    ---ID---|---Name--|---link---|
        1   |   A     |    AL    |
        2   |   B     |    BL    |
        3   |   C     |    CL    |
    and it show a code like this :

    Code:
    <a href="AL">A</a>
    <a href="AB">B</a>
    <a href="AC">C</a>
    now I want user could move the items up and down
    for example change it like this :

    Code:
    <a href="AL">A</a>
    <a href="AC">C</a>
    <a href="AB">B</a>
    How can I do it ?

  2. #2
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    you'd need to swap the id's in the mysql table, so they are in the order you want.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  3. #3
    SitePoint Evangelist
    Join Date
    Dec 2005
    Posts
    527
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by arkinstall View Post
    you'd need to swap the id's in the mysql table, so they are in the order you want.
    Can you help more ?
    How can I do it ?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you need to remove the id column altogether

    a code table such as this is the last place you'd want to use a numeric id -- just use the code!!

    however, to allow your user to sequence the codes into the desired sequence, you need to add a sequence column

    i would recommend using either a FLOAT for the sequence column, or an integer which increments by 100 or something similar

    that way, to "move" a row in between two other rows, you simply average their sequence numbers

    with integers, you might need eventually to resequence "local" numbers (if you've inserted more than 50 numbers in between two existing numbers), but with floats, you will never need to resequence
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,104
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I am working on a similar thing for cms links and have a column pos (position) and use a-z. (26 positions and easy to understand for the end user)

    this way you still have id for any kind of lookup you may need.

    in the "change link" form I have something like this which changes the link name destination and position.

    [g] [linkname] [dest] [submit]

    I struggled to find this solution and is a bit different than r937's answer. I use the suckerfish dropdowns and allow the user to create the main links and dropdown links under the main categories. I just got the "change main links" working and having the dropdown links staying with them.

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Is there anything useful to you in this demo ?

  7. #7
    SitePoint Member
    Join Date
    Oct 2007
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can insert one more integer column in the table to represent the sequence. Then allow the user to change the sequence. When you query the database, sort it by the "sequence" column instead of the id.

  8. #8
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Welcome to the forums, scnsp.

    I did something similar with a client who wanted to be able to rearrange his menu. I just swapped the Order_ID of the two swapped items.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  9. #9
    SitePoint Evangelist
    Join Date
    Dec 2005
    Posts
    527
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by scnsp View Post
    You can insert one more integer column in the table to represent the sequence. Then allow the user to change the sequence. When you query the database, sort it by the "sequence" column instead of the id.
    How can I move items up and down ? (shift)

  10. #10
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    it depends on if you are shifting up or down.

    The Order_ID of the item to be moved will either add or subtract 1, and the one it's replacing will have to do the opposite.

    This is the code I used for a client. It shows a form, which they can make press "up" or "down" (you'll have to make the graphics) and it'll change the order.

    PHP Code:
    if(isset($_GET['id'])){
        
    $co mysql_query("SELECT `order` FROM `menu` WHERE `id` = '".$_GET['id']."'");
        
    $currorder mysql_result($co0);
        if(isset(
    $_GET['down'])){
            
    $nor mysql_query("SELECT * FROM `menu` WHERE `order` = '".($currorder+1)."'");
            if(
    mysql_num_rows($nor)){
                
    $neworder mysql_result($nor0'id');
                
    mysql_query("UPDATE `menu` SET `order` = '".($currorder+1)."' WHERE `id` = '".$_GET['id']."'");
                
    mysql_query("UPDATE `menu` SET `order` = '".$currorder."' WHERE `id` = '".$neworder."'");
            }
        }else if(isset(
    $_GET['up'])){
            
    $nwod mysql_query("SELECT `id` FROM `menu` WHERE `order` = '".($currorder-1)."'");
            if(
    mysql_num_rows($nwod)){
                
    $neworder mysql_result($nwod0);
                
    mysql_query("UPDATE `menu` SET `order` = '".($currorder-1)."' WHERE `id` = '".$_GET['id']."'");
                
    mysql_query("UPDATE `menu` SET `order` = '".$currorder."' WHERE `id` = '".$neworder."'");
            }
        }
    }
    $q mysql_query("SELECT * FROM `menu` WHERE `show` = '1' ORDER BY `order` ASC");
    if(!
    $q){
        echo 
    "No menu in database";
    }else{
        echo 
    "<table>";
        
    $e 0;
        while(
    $row mysql_fetch_array($q)){
            echo 
    "<tr><form action=\"".$_SERVER['PHP_SELF']."\" method=\"get\">";
            echo 
    "<input type=\"hidden\" name=\"id\" value=\"".$row['id']."\"><td>";
            if(
    $e != 0){
                echo 
    "<input type=\"submit\" name=\"up\" style=\"background: url('images/arrow_up.gif'); margin: 0; width: 23px; height: 32px; border: 0;\" value=\"\">";
            }
            echo 
    "</td><td>".$row['text']."</td>";
            if(
    $e != (mysql_num_rows($q)-1)){
                echo 
    "<td><input type=\"submit\" style=\"background: url('images/arrow_down.gif'); margin: 0; width: 23px; height: 32px; border: 0;\" name=\"down\" value=\"\"></td>";
            }
            echo 
    "</form>";
            
    $e++;
        }
    }
    ?></table> 
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  11. #11
    SitePoint Member
    Join Date
    Oct 2007
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bakhanbeigi View Post
    How can I move items up and down ? (shift)
    A simple solution is to manually edit the database and rank the row in the order you want to show up.

    For example,

    Code:
    Menu table 
    ---ID---|--Order ID --|---Name--|---link---|
        1   |   1         | A       |    AL    |
        2   |   3         | B       |    BL    |
        3   |   2         | C       |    CL    |
    This is probably only practical if the change is infrequent.

    If the order is changed very often, then the more elegant solution is to use the code by arkinstall.

  12. #12
    SitePoint Evangelist
    Join Date
    Dec 2005
    Posts
    527
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What do you think about this :

    Code:
    function move_to_up($id, $priority)
    {
    	--$priority;
    	$q = "UPDATE table SET priority=priority+1 WHERE priority = '$priority'";
    	mysql_query($q);
    	$q = "UPDATE table SET priority=priority-1 WHERE id = '$id'";
    	mysql_query($q);
    }

  13. #13
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    that's practically the same as the code i gave you, however my code includes the form for you to change it with
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  14. #14
    SitePoint Evangelist
    Join Date
    Dec 2005
    Posts
    527
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How it fill and get value ?
    I cant use auto increment because my ID is ...
    How I can find the last and add new order_id ? (last+1)??

  15. #15
    SitePoint Evangelist
    Join Date
    Dec 2005
    Posts
    527
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How can I generate value of my order_id s ?

  16. #16
    SitePoint Member
    Join Date
    Feb 2007
    Location
    Amsterdam, Netherlands
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $num = query( 'SELECT order_id FROM table ORDER BY order_id DESC LIMIT 1' ) + 1;

    $num now holds the highest position in your table + 1

    (where query is your preferred method of getting data from MySQL)
    Patrick Kanne


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
  •