[Resolved] No Holes in Mysql ids?

Black_Hobbit

Hi I've got a couple quick question. Ok, here goes,

I've got a simple script that alows someone to add, delete, and modify entries in a database.--That's working. But for some reason, if the prosess of adding, and deleting entries, I get a Table that looks like this:

| id | field 1 | field 2 |
| 1 | abc | abcde |
| 2 | blah | yabba |
| 3 | abc | abcde |
| 5 | cba | edfgh |

| 6 | abc | abcde |

Does anyone know why this is happening?
Is there a way to fix that?

bastien

There is no way to maintain sequential IDs using autnumber. The only way is to store the number and increase it at the application level. So you could either create a table with a single entry, the current value of the ID and then use code to increment it or to store the value in a filesystem file and do the same

Code43210

I can thing of several methods that would alllow you to maintain an unbroken sequence of numbers. It would help if you explained why you need this?

Sxooter

Originally posted by Black Hobbit

Does anyone know why this is happening?
Is there a way to fix that?

It's no more broken than an engine is for firing it's cylinders in the order 1342. The engine fires cylinders in an odd order to maintain balance, your database has gaps to maintain integrity. Since autoincs are generally (primary) keys, and (primary) keys should never change, it is not broken to have holes in the sequence.

Attempting to fill those holes may well break your data integrity.

Is there some genuinely useful reason you want to fill in the holes, or do you just want it to be pretty.

If it's for pretty's sake, imagine how well your car's engine would run if you rewired the spark plugs so they fired 1234 without redisigning the engine to work that way. The wiring order would be very pretty. The engine would not work.

Black_Hobbit

Because I'm using a for() loop to print all of my things. And I had another reason, but i can't remember it. It was too long ago.

Sxooter

Oh, ok. Well, instead of using a for loop, you can step up to a while loop:

while ($row = mysql_fetch_row($res)){
print $row['field1'];
// more stuff goes here...
}

and you can walk right through your results.

Throw off the shackles of the for loop! (hehe, only kidding 🙂

If you want a for loop, you can do it like this:

$count = mysql_num_rows($res);
for ($i=0;$i<$count;$i++){
$row = mysql_fetch_row($res,$i);
// do more stuff here.
}

But take my word for it, trying to keep your ids in order is a recipe for disaster. Imagine if the airline reservation system went offline for 2 hours every night to do something similar? People would freak. And while your system today may not have that much load, planning on always being able to lock everyone out to reorder old ids just to make your code happy is a bad idea.

Black_Hobbit

Actually this is for a Content Management System. For example updating the news etc...

What does $res stand for?

Sxooter

$res is the name of the var I generally use to hold a handle to result sets, like so:

$res = mysql_query("select * from table");

OK, you're writing a content management system. You get about 100 documents loaded into it, and at the top of every hour it locks the table and reorders your ids. This only takes a second, so no big deal. Then you get up to 100,000 documents, and it takes 30 seconds or a minute. the problem with reordering id numbers is that it has to basically happen while everyone is locked out or an error could occur (i.e. user checks out document 1234, you reorder, his old document is now stored under 1233, his web page form stores it as 1234, which overwrites the file that used to be at 1235.) Get the idea?

Black_Hobbit

Ahh, ok I was unsure about what you ment by it. And I see your point.

Code43210

You are not by any chance using a for loop to do one query per item are you?

In any case please show me your loop, I am curious.

Black_Hobbit

for ($rownum = 0; $rownum < $MaxRowNum; $rownum++) { 

   //print event name       
   echo ("<b>");   //starts bold, this makes the event name bold! Classy Huh?
   printf("%s<br>\n", 
   mysql_result($result,$rownum,"eventname"));
   echo ("</b><br>");  //ends bold and makes a break, this makes things stop being bold :p

   //print event discription
   printf("<b>Description:</b> %s<br>\n", 
   mysql_result($result,$rownum,"eventdis")); 

   //print event date
   printf("<b>Date:</b> %s<br>\n", 
   mysql_result($result,$rownum,"eventdate"));
   echo ("<br><br>");

   }

Yeah, well, this was from some of my older code but it's ecentually the same.

Code43210

Looks like you are creating a lot of extra work for yourself. 🙂

To get your method to work you need to use count instead of max.

I suggest you just follow Sxooter's advice and switch to the while ... mysql_fetch_row syntax.

mysql_fetch_row($result) will keep returning rows until the end is reached then, then it returns false ending the loop.

Black_Hobbit

Cool. Thanks!