Quantcast
Jump to content
Search In
  • More options...
Find results that contain...
Find results in...
    1. Welcome to GTAForums!

    1. GTANet.com

    1. GTA Online

      1. The Cayo Perico Heist
      2. Find Lobbies & Players
      3. Guides & Strategies
      4. Vehicles
      5. Content Creator
      6. Help & Support
    2. Red Dead Online

      1. Frontier Pursuits
      2. Find Lobbies & Outlaws
      3. Help & Support
    3. Crews

    1. Red Dead Redemption 2

      1. PC
      2. Help & Support
    2. Red Dead Redemption

    1. Grand Theft Auto Series

      1. St. Andrews Cathedral
    2. GTA VI

    3. GTA V

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

      1. The Lost and Damned
      2. The Ballad of Gay Tony
      3. Guides & Strategies
      4. Help & Support
    5. GTA San Andreas

      1. Guides & Strategies
      2. Help & Support
    6. GTA Vice City

      1. Guides & Strategies
      2. Help & Support
    7. GTA III

      1. Guides & Strategies
      2. Help & Support
    8. Portable Games

      1. GTA Chinatown Wars
      2. GTA Vice City Stories
      3. GTA Liberty City Stories
    9. Top-Down Games

      1. GTA Advance
      2. GTA 2
      3. GTA
    1. GTA Mods

      1. GTA V
      2. GTA IV
      3. GTA III, VC & SA
      4. Tutorials
    2. Red Dead Mods

      1. Documentation
    3. Mod Showroom

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

      1. Design Your Own Mission
      2. OpenIV
      3. GTA: Underground
      4. GTA: Liberty City
      5. GTA: State of Liberty
    1. Rockstar Games

    2. Rockstar Collectors

    1. Off-Topic

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

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

      1. GTANet 20th Anniversary
    2. Support

      1. Court House
    3. Suggestions

[MySQL] Linear Primary Keys


Johnno

Recommended Posts

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?

Link to post
Share on other sites

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
Link to post
Share on other sites
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.

Link to post
Share on other sites

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
Link to post
Share on other sites

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
Link to post
Share on other sites

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]

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
  • 2 Users Currently Viewing
    0 members, 0 Anonymous, 2 Guests

×
×
  • Create New...

Important Information

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