Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,778
    Thanks
    19
    Thanked 155 Times in 146 Posts

    recreating and replacing database column values

    Here's my question:

    If I have a MySQL database column that reads:

    index_no

    1

    2

    3

    4

    5
    and I delete one of the rows, let's say the one where index_no='2'....so now the column reads:

    index_no

    1

    3

    4

    5
    How do I recreate and replace the existing column values so that the column reads:

    index_no

    1

    2

    3

    4
    ????

    This database is ****, so I don;t have any other columns to cross-reference this column with....is there a way to simply recreate and replace the column values so they start at '1' (at the top) and (in increments of 1) go on to infinity (without cross-referencing any other columns, hopefully)?

    Thanks again.
    Last edited by chump2877; 06-27-2005 at 02:01 AM.
    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!

  • #2
    Regular Coder
    Join Date
    May 2005
    Posts
    563
    Thanks
    0
    Thanked 3 Times in 3 Posts
    This is one way to do it, I don't know if there is a more efficient way but this works.

    PHP Code:
    $query "ALTER TABLE `table` DROP `index_no`;"//delete id column
    mysql_query($query); //run the query
    $query2 "ALTER TABLE `table` ADD `index_no` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;"//add id column
    mysql_query($query2); //run the query 

  • #3
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,778
    Thanks
    19
    Thanked 155 Times in 146 Posts
    Efficient enough for me, plus it works...thanks!

    Also, methinks my SQL is not so good.....Do you mind explaining to me how this query works:

    Code:
    ALTER TABLE `table` ADD `index_no` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
    thanks again...
    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!

  • #4
    Regular Coder
    Join Date
    May 2005
    Posts
    563
    Thanks
    0
    Thanked 3 Times in 3 Posts
    PHP Code:
    "ALTER TABLE `table`" //edit the table's structure (like SELECT but for editing)
    "ADD `index_no`" //edit it by adding this column name
    "INT" //column type (integer)
    "NOT NULL" //doesn't allow it to be NULL (must have a value)
    "AUTO_INCREMENT" //makes it an auto incrementing field (so when you add a column it makes it 1 higher than last entry
    "PRIMARY KEY" //makes it the primary key for this table
    "FIRST" //column position (so it's the first column because ids usually are 
    So basically the code i gave u gets rid of the column then places it back in and since it is an auto increment column it inserts new ids starting at 1 and incrementing by 1 each time.

  • #5
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,778
    Thanks
    19
    Thanked 155 Times in 146 Posts
    Thanks, good to know what all that means....
    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!


  •  

    Posting Permissions

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