SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    Tulsa, OK
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Resorting a row that is auto increment

    From my searches on the forum, I'm not sure if this can be done.

    Basically, I have an interface to view, add, and delete records(rows). When one is deleted and then another is added it does what it is suppose to I imagine and auto increments to the next number, so I get a list like this:

    1
    2
    3
    5
    10

    This column is the Primary Id. I could just not show this on the page when the records are viewed, but it would be nice to have a relavant item number for each row. Is there anyway to resort/reorder without dropping the table?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    don't mess with the primary key

    leave the gaps alone, they can't hurt you

    just don't show them

    or, if you want to show something, show a sequential number that you generate in your script

    but leave the keys alone

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As r937 says, leave them alone!

    Primary keys should not have *meaning* - they should be used only as unique row identifiers within your code/DB.
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  4. #4
    With More ! for your $ maxor's Avatar
    Join Date
    Feb 2004
    Location
    Scottsdale, Arizona
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you're using PHP you could do something like this..

    PHP Code:

    $counter 
    1;
    while(
    $row mysql_fetch_array($result)){

    echo 
    $counter;
    echo 
    $row['var1'];
    echo 
    $row['var2'];
    echo 
    $row['var3'];
    echo 
    $row['var4'];

    $counter++;

    This would just put a number next to the results for each row that would look nice and sequential.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    If you're using ColdFusion you could do something like this..
    Code:
    #CurrentRow#
    This would just put a number next to the results for each row that would look nice and sequential.

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    Tulsa, OK
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi thanks, thanks for the warning. I knew it was bad to mess with the primary, I just have things set up right now so an entry is deleted by entering it's row number in a text field and submitting that. I think I'll have to add a more generic way to delete (or edit) an entry. Do you all have any suggestions for adding something like this? Maybe something like having check boxes at the beginning of each row, that if checked would be deleted? or a link at the end of each row that would delete/edit the entry?

    I'm using PHP so maxor suggested may come in really handy too. Thanks for the help!

    -gOatLiP

  7. #7
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by goatlip
    Maybe something like having check boxes at the beginning of each row, that if checked would be deleted?
    Precisely what I suggest. Then just loop through the submitted values in your PHP script, deleting each row, or even better, delete them in one fell swoop by using the values as a delimited list (which you can easily generate from the field value array) in conjunction with an IN clause:
    Code:
    DELETE FROM tablename WHERE pk IN (1,37,98,220)
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!


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
  •