[SOLVED] How to remain mysql colume id (INT)?

[phpocean]

Hello people,

 

Two of my row (10 and 50) has same data, thus I wanted to delete row 50 and have row 51 become 50 and so on.

 

Is there a way for me to re-number (rename) my column id (INT) so that there are no mission row?

 

Better yet, is there a way for me to pull data from the next and previous row instead of having to pull the next and previous row by calling +1 and -1?

 

Thank for all the helps.

[Ken2k7]

Why does it matter that row 51 has to be row 50? I'm guessing you're talking about the ID AUTO_INCREMENT number right? I just don't see the point. Just delete it. row 50 is gone, but that's okay.

[fenway]

Yeah, don't.

[phpocean]

Why does it matter that row 51 has to be row 50? I'm guessing you're talking about the ID AUTO_INCREMENT number right? I just don't see the point. Just delete it. row 50 is gone, but that's okay.

 

I have not tested this yet but are you saying that when I am on row 49 and used the +1 code to get the next row, it would get row 51 instead of row 50 since there isn't a row 50 in my table? Correct?

 

ps. Hi Ken.

[Ken2k7]

Hi phpocean! I hope everything's well.

 

Um, well when you select your rows and loop through them, it'll work out fine. If you really want the number to output correctly, then just use a counter in the while loop. For example, let's say you have 50 rows from a query - SELECT * FROM tablename LIMIT 50 - but you know you deleted row 10. So row 10 is gone, and that SQL will select rows 1- 9 and 11 - 51 (making it 50 rows). Of course, you can have a WHERE clause to select rows less than or equal to 50 - SELECT * FROM tablename WHERE id <= 50 - and that would return 49 rows.

 

Then when you loop through it, you'll use a counter if you want to display something in item format, or you can use an ordered list. Using the first SQL example - SELECT * FROM tablename LIMIT 50 - we can use a while loop like this so that users don't see what row is deleted -

 

$query = mysql_query('SELECT * FROM tablename LIMIT 50;');
$counter = 1;
while ($row = mysql_fetch_assoc($query)) {
     echo $counter . ' - ' . $row['something'] . '<br />';
     $counter++;
}

 

So when that displays, it looks fine. You don't really need the ID reference for output. You can if you want.

 

I hope this helps!

[phpocean]

Not exactly what I am looking for but I will keep this code for future use as I know I will come across it.

 

By any chance do you know a way to re-auto_increment the primary id using PHP code?

What I have: Missing rows in table (1000+ rows with missing rows id).

What I want to achieve: Rename (AUTO_INTREMENT)  id (PRIMARY INT) so there is no missing rows in table.

 

 

[Ken2k7]

Well, there's no easy way of doing it. But you can reset the AUTO_INCREMENT value each time. It's a bit tedious and unnecessary though.

 

May I ask what you're trying to do that requires you do have a table without missing rows or the auto_increment field?

[phpocean]

No particular reason, just thought it would be better if there were no missing rows. I have decided that missing rows is no big deal. Thank for all the help and time.