Quantcast
Jump to content
Search In
  • More options...
Find results that contain...
Find results in...
    1. Welcome to GTAForums!   (84,878 visits to this link)

    2. News

    1. GTA Online

      1. Find Lobbies & Players
      2. Guides & Strategies
      3. Vehicles
      4. Content Creator
      5. Help & Support
    2. Crews

      1. Events
      2. Recruitment
    1. Grand Theft Auto Series

    2. GTA Next

    3. GTA V

      1. PC
      2. Guides & Strategies
      3. Help & Support
    4. GTA IV

      1. Episodes from Liberty City
      2. Multiplayer
      3. Guides & Strategies
      4. Help & Support
      5. GTA Mods
    5. GTA Chinatown Wars

    6. GTA Vice City Stories

    7. GTA Liberty City Stories

    8. GTA San Andreas

      1. Guides & Strategies
      2. Help & Support
      3. GTA Mods
    9. GTA Vice City

      1. Guides & Strategies
      2. Help & Support
      3. GTA Mods
    10. GTA III

      1. Guides & Strategies
      2. Help & Support
      3. GTA Mods
    11. Top Down Games

      1. GTA Advance
      2. GTA 2
      3. GTA
    12. Wiki

      1. Merchandising
    1. GTA Modding

      1. GTA V
      2. GTA IV
      3. GTA III, VC & SA
      4. Tutorials
    2. Mod Showroom

      1. Scripts & Plugins
      2. Maps
      3. Total Conversions
      4. Vehicles
      5. Textures
      6. Characters
      7. Tools
      8. Other
      9. Workshop
    3. Featured Mods

      1. DYOM
      2. OpenIV
      3. GTA: Underground
      4. GTA: Liberty City
      5. GTA: State of Liberty
    1. Red Dead Redemption 2

    2. Red Dead Redemption

    3. Rockstar Games

    1. Off-Topic

      1. General Chat
      2. Gaming
      3. Technology
      4. Programming
      5. Movies & TV
      6. Music
      7. Sports
      8. Vehicles
    2. Expression

      1. Graphics / Visual Arts
      2. GFX Requests & Tutorials
      3. Writers' Discussion
      4. Debates & Discussion
    1. Forum Support

    2. Site Suggestions

Sign in to follow this  
Johnno

[MySQL] Linear Primary Keys

Recommended Posts

Johnno

Not a very helpful title, so i'll just explain.

 

Most content intensive websites I develop, rely use MySQL tables with a standard ID for the primary key.

I've came across a problem with this a few times before, but recently I found it in a table for Bentley Bear:

 

CREATE TABLE photos (id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,gallery VARCHAR(3) NOT NULL,title VARCHAR(20) NOT NULL,description TEXT NOT NULL,PRIMARY KEY (id));

 

The problem comes in the admin panel: When the owner deletes a record, the ID of that deleted record then becomes depreciated. I need the column ID to be linear (1, 2, 3, etc), with no number jumps.

 

Is there a way this can be accomplished?

Share this post


Link to post
Share on other sites
Svip

Yes.

 

But it will be a real tough one to beat.

 

I'd suggest you do some PHP coding on it. A PHP code that moves the table rows around and set the ids in a linar function, deletes the old ones, inserts the new ones and such, and changes the AUTO_INCREMENT value ( which sets what the next id value is ).

 

That will be more of a debug thing. But I think I can write it.

 

It would be something like;

 

 

$query = mysql_query("SELECT * FROM $table");if (@mysql_num_rows($query)) { $ttable = array(array()); $i = 1; while ($result = mysql_fetch_assoc($query)) {   $ttable[$i][gallery] = $result['gallery'];   //then you insert all the other fields...   $max = $i;   $i++; } mysql_query("DELETE * FROM $table"); for ($i = 1; $i <= $max; $i++) {   mysql_query("INSERT INTO $table SET id = $i, gallery = '{$ttable[ $i ]['gallery']}', ..."); }//And here the query that sets the AUTO_INCREMENT to $max}

 

 

Should fix it. I might have made some errors and such, but I haven't tested it. But it looks to work. Could be made a function.

Edited by Svip

Share this post


Link to post
Share on other sites
Sarin

Not that I know of. Why do you need it to be linear?

Share this post


Link to post
Share on other sites
Svip
Not that I know of. Why do you need it to be linear?

Perhaps he needs them to be listed or something.

 

I don't know, I don't think them being linear should help you much.

 

As I showed I can pick them all up in order, without them being linear.

Share this post


Link to post
Share on other sites
andibomb

If I understand your problem correctly, you could drop and re-add the id column which would reset the ids.

 

Using:

 

 

ALTER TABLE `photos` DROP `id`;

 

 

To drop the column and then:

 

 

ALTER TABLE `photos` ADD `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

 

 

To add the column back at the beginning of the table and the id values will all be reset.

Edited by andibomb

Share this post


Link to post
Share on other sites
Johnno

That's a good idea Svip, I may end up using something along those lines. I'd prefer it to be a more 'on the fly' method though (that one would need to rebuild the table every time a record is deleted confused.gif ).

 

I need it so the navigation between items on the website would work smoothly.

For that, I could build a little class that just checks if the specific record is actually there... but it would still be good if I could make the ID linear, for future projects.

 

edit

Andy, it worked... genius!

smile.gif

 

Edited by Johnno

Share this post


Link to post
Share on other sites
Sarin

That won't work very well if you're joining tables though, or referencing the IDs in any other table/field. And it's overkill. You shouldn't need to do that. Are you doing it to save on a database query or something? I mean, you could do something like:

 

SELECT id, gallery, title, description FROM photos WHERE id > [CurrentID] ORDER BY id ASC LIMIT 0, 1

 

Which is cleaner. Does require a query but is a lot more efficient, and means that links to specific [photos] will always be valid, and will be a lot less database intensive if there's 00's of records.

 

[edit]

Or, for the photo before:

 

SELECT id, gallery, title, description FROM photos WHERE id < [CurrentID] ORDER BY id ASC LIMIT 0, 1

 

 

For either, if no results are returned, you know you're on the last/first photo.[/edit]

Share this post


Link to post
Share on other sites
Luke

Much better now me and Jevon have spent about 2 hours explaining what indents are to you Svip. smile.gif

 

 

Smithers goes to recover

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using GTAForums.com, you agree to our Terms of Use and Privacy Policy.