Quantcast

Jump to content

» «
Photo

[MySQL] Linear Primary Keys

7 replies to this topic
Johnno
  • Johnno

    WD&P Ogre

  • Members
  • Joined: 15 Sep 2002

#1

Posted 30 May 2005 - 11:10 AM

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:
CODE
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?

Svip
  • Svip

    I eat babies

  • The Connection
  • Joined: 12 Nov 2001
  • None

#2

Posted 30 May 2005 - 11:29 AM Edited by Svip, 30 May 2005 - 01:57 PM.

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;

CODE
$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.

Sarin
  • Sarin

    AUM Shinrikyo

  • Members
  • Joined: 04 Jul 2002

#3

Posted 30 May 2005 - 11:33 AM

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

Svip
  • Svip

    I eat babies

  • The Connection
  • Joined: 12 Nov 2001
  • None

#4

Posted 30 May 2005 - 11:36 AM

QUOTE (Sarin @ May 30 2005, 13:33)
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.

andibomb
  • andibomb

    Beau

  • Leone Family Mafia
  • Joined: 09 Jul 2002

#5

Posted 30 May 2005 - 12:00 PM Edited by andibomb, 30 May 2005 - 12:10 PM.

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

Using:

CODE
ALTER TABLE `photos` DROP `id`;


To drop the column and then:

CODE
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.

Johnno
  • Johnno

    WD&P Ogre

  • Members
  • Joined: 15 Sep 2002

#6

Posted 30 May 2005 - 12:01 PM Edited by Johnno, 30 May 2005 - 12:09 PM.

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


Sarin
  • Sarin

    AUM Shinrikyo

  • Members
  • Joined: 04 Jul 2002

#7

Posted 30 May 2005 - 12:53 PM

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:
SQL
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:
SQL
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]

Luke
  • Luke

    suckmyrocket

  • Moderator
  • Joined: 01 Dec 2003
  • None

#8

Posted 30 May 2005 - 01:59 PM

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

Smithers goes to recover





1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users