another mysql question because i'm stupid

Discussion in 'OT Technology' started by crotchfruit, Jul 27, 2003.

  1. crotchfruit

    crotchfruit Guest

    ok, just wondering is this is supported by mysql:

    let's say i have table "blah" with a column named "id". id is unique/auto_increment/not null.

    so let's say i add 5 rows. the id of the five rows will be 1, 2, 3, 4, 5. if i delete row 3, the remaining ids are 1, 2, 4, 5. is there a SQL command that will compress this column data down to 1, 2, 3, 4 preserving the original order (1, 2, 4, 5)?
     
  2. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    No. And doing so would compromise a properly designed database. Example:

    In your database you store sandwiches and what goes on them:

    Table Sandwich (first column is auto ID):
    1 hamburger
    2 grilled cheese
    3 Peanutbutter & Jelly

    Table Ingrediants (first column is auto ID):
    1 hamburger patty
    2 mustard
    3 pickles
    4 cheese
    5 peanutbutter
    6 jelly
    ...

    Table BuildSandwich (1st column = Sandwich ID, 2nd column = Ingrediant ID)
    1 1
    1 2
    1 3
    1 4
    2 4
    3 5
    3 6

    Now let's apply the rule of deleting an auto-increment ID and then shuffling all the IDs. Lets delete pickles:

    Table Ingrediants (first column is auto ID):
    1 hamburger patty
    2 mustard
    3 cheese
    4 peanutbutter
    5 jelly
    ...

    Now we have problems. Our hamburger is defined to now have:
    Hamburger patty
    mustard
    cheese
    peanutbutter (thats not right!)

    The grilled cheese now has peanutbutter and the PBJ sandwich now only has jelly. The PBJ sandwich now has a somewhat serious issue: There's a record with an ID of 6, but there's no associated record. This means you broke the database integrity (with foreign key relationships, you can prevent this from happening).

    In a long and round about way, thats why autoID doesn't work like you'd like.

    If you REALLY must have this feature, then you will have to implement it on your own (disable auto-increment), but your code will have to keep track to what the next ID is as well as shuffle all the IDs when you delete one (just figuring out the ID isn't so easy because what happens if two people submit data to that table at exactly the same time?).

    Why do you want to try implementing a column that behaves like this? There might be an easier way...
     
  3. GotVtec

    GotVtec 8th World Wonder

    Joined:
    Mar 14, 2000
    Messages:
    575
    Likes Received:
    0
    Location:
    Omaha, NE
    yup, what he said.
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Yeah Autonumbering is for linking, but if you need small ID numbers to hand out, it's best to keep them in a separate field which has no effect on any other tables. That way if you "forget" to clean up your mess when you delete entries, it doesn't affect your db. I know in a program I use, I'm up to maintenance order number 25000 or so after only a few years. Do you really want to continue to refer to the order number as that big honking number? Not really, but I also don't want to lose the old data either.
     
  5. crotchfruit

    crotchfruit Guest

    i probably shouldn't have asked with a basic example like that, since obviously it's not a good idea to change around the "id" column. here's what i was aiming for:

    basically, i have a table of things - and i want the sort to be user-defined. that is, i have a column called "display_index". the user is then allowed to modify the display_index of each row so that when the rows are sorted by this index they are displayed in the order of the user's liking.

    right now, out of simplicity for me, i force the user to manually enter this index. but in the future i would like to give the user simple "move up list" and "move down list" buttons. i just thought it would be nice if i could compress this column down so that values were 1, 2, ..n where n = # of rows - so that it would make moving rows by this index easier.
     
  6. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Ah the infamous user sort... there's really no easy way of doing this. You're on the right track with having a "dispay index" and what I do is (assume moving up):

    - initialize the display sort field for all records
    - check to see if it's already 1, if so, ignore
    - if not then update the row I want moved up by -1 in the display index, remember the unique ID of said row and the new sort number
    - then do an update query on the recordset to add 1 to the display field to all records with that have the display index >= the new sort number you remembered in the previous step, excluding the unique row.

    A similar process is used to move them down. You can also do it with a group of entries at once, just takes a bit more code.
     
  7. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    Again, your mileage may vary...

    I've got some code up on axtime.com that allows my club managers to build their own registration forms. They can shuffle form fields in whatever order they like (and each form fields has about 5 pieces of data).

    I tackled this problem from the cross reference table side. In your case, this will be the same exact cross reference table you are using to link the user ID to the preference ID (forgive me if I forgot the names of your data).

    Create an order column in your cross reference table (you probably figured this out). The trick is how do you update it? (I think this was your original question)

    For axtime.com, I built the form on the fly. The user would click move up/down and I would refresh the page (DHTML would be a ton faster and easier on the server, but my concern was browser support). I let PHP validate the move to make sure its within bounds.

    When the user clicks on one of the move links, I pass the ID of that item back to PHP and I let PHP know which direction the user wants it to go in.

    PHP then grabs all the form fields and sorts them in order. It then builds an array of all the fields and it tags the one the user wants to move. Then I perform an array item swap (classic computer science move). In theory, the master array is in order, so its a matter of deleting all records from the DB that I'm playing with and then re-inserting them (using a for(), I can rebuild the sort order very easily).

    With this approach, I can ensure the move is validated and keep all the data in order without much fuss. This approach may not work well if you have heavy loads (a lot of users + a lot of records). For my application, I'll only have about 1 user in there at a time shuffling about 4-10 records.

    Example:
    PHP:
    function MoveFields($formID$fproID$Action)
    {
        
    $list '';
        
    $direction 1;
        if(
    $Action == 'MoveUp')
            
    $direction = -1;

        
    $query 'SELECT formID, fproID, fxreOrder FROM ax_formxref WHERE formID = ' $formID ' ORDER BY fxreOrder';
        
    $result Query($query);
        if(
    $result)    
            {
            
    $total mysql_num_rows($result);
            if(
    $total 2)
                return;                                                    
    // nothing to move

            
    $a 0;
            while(
    $row mysql_fetch_object($result))
                {
                
    $list[$a] = $row->fproID;
                if(
    $row->fproID == $fproID)
                    
    $target $a;                                        // we found the one we want to move
                
    $a++;
                }  
    // end while($row = mysql_fetch_object($row))
            
            
    if(($target $direction) < || ($target $direction) > $total)    
                return;                                                    
    // either at the end or at the top    
            
            
    $hold $list[$target $direction];
            
    $list[$target $direction] = $list[$target];
            
    $list[$target] = $hold;

            
    $query 'DELETE FROM ax_formxref WHERE formID = ' $formID;
            
    Query($query);

            
    // now reinsert
            
    for($a 0$a count($list); $a++)
                {
                
    $query 'INSERT INTO ax_formxref (formID, fproID, fxreOrder) VALUES (' $formID ', ' $list[$a] . ', ' $a ')';
                
    Query($query);
                }  
    // end foreach($list as $key => $value)
            
    }  // end if($result)
    }  // end function MoveFields($form['formID'], $form['fproID'], $Action)
    This code directly will not work with what you have, but it shouldn't be too far off. If you end up actually using any of it, let me know - I'd be curious to know it has actually helped someone.
     
    Last edited: Jul 29, 2003
  8. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    That sounds like a lot more work than my approach - what's the benefit?
     
  9. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    This is just the approach I took - its not the only way nor the best way - just an approach. I reread your post and I think I see what you're saying... I might give it a whirl since it looks like it might be possible to reduce the number of query hits.
     
  10. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA

Share This Page