(PHP,MYSQL) Function to 'refit' indexes

[lexkymbeth]

Don't know the real name to this so I guess 'refit' works.

This php function fixes indexes after you remove an item, say you have 5 rows and you delete the 4th you now have (1,2,3,5)

This function will fix that so there are no missing indexes, (1,2,3,4) Its basic and there could be better ways but this is how I did it

Arguments of mysql as mysqli object, and table as string

```
function fixRows($mysqli, $table) {
$result = $mysqli->query("SELECT * FROM `$table` ORDER BY `Index`");
$count = 1;
while($row = mysqli_fetch_array($result)) {
if($row['Index'] != $count) {
$oldIndex = $row['Index'];
if(!$mysqli->query("UPDATE `$table` SET `Index`='$count' WHERE `Index`=$oldIndex")){
die('Error: ' . mysqli_error($mysqli));
}
}
$count++;
if(!$mysqli->query("ALTER TABLE `$table` AUTO_INCREMENT = $count")){
die('Error: ' . mysqli_error($mysqli));
}
}
}
```

[Lenton]

IDs in a database should never be changed, having gaps is absolutely fine and isn't something you should worry about.

[lexkymbeth]

> IDs in a database should never be changed, having gaps is absolutely fine and isn't something you should worry about.

Why should they never be changed? I do not see a problem with changing them.. Everything still works with my program and website after doing this. Although there is another way to get past gaps I found this useful for OCD :S (lol)

[deathtaker26]

Don't let chief see this post, he'll say you're working with the devil

[JeffSventora]

Unique ID's are pretty essential to data driven design via SQL. Here's an example:

We have a table laid out like this

Table = [dbo].[Names]

[IndexID INT] [Name VARCHAR(512)]

Let's add a few…

[0] [Jeff]

[1] [Holly]

[2] [Bob]

Now suppose we have another table that has a reference column to our Names table:

Table = [dbo].[PhoneNumbers]

[IndexID INT] [NameIndexID INT] [PhoneNumber VARCHAR(11)]

If we add a phone number for all of these people in Names it would look something like this:

[0] [0] ["15555555555"]

[1] [1] ["15555555555"]

[2] [2] ["15555555555"]

The second column references the Names table… so if we grab a phone number, we can use the NameIndexID to grab the owner's name from the Names table. So now let's remove Holly from the Names table like the way you're doing it:

[0] [Jeff]

[1] [Bob]

[0] [0] ["15555555555"]

[2] [2] ["15555555555"]

Now Bob is at index 1, but look at what happens when we try to map a phone number to a name. If we want to get Bob's name, it will look for a NameIndex of 2 in the Names table which does not exist.

Your only option at this point is to update EVERY SINGLE table that references this column to match… This is very very bad and why we want to keep these indices the way they are. This is what makes relational databases... relational. If you're wanting to keep ID's in order, I would recommend keeping a unique ID column (does not change) and adding another column to track the order (0-n) but even that is unnecessary.

[lexkymbeth]

Yes, this is not meant for something like that. I created it to fix gaps in tables that do not need "Unique ID's"

Its meant for things such as posts where they are deleted, not specific information

[Lenton]

> Yes, this is not meant for something like that. I created it to fix gaps in tables that do not need "Unique ID's"
> Its meant for things such as posts where they are deleted, not specific information

Posts _do_ need unique and static IDs.

You could have a `comments` or `likes` table which reference the post ID and as JeffSventora pointed out, it would break the relationship. Also, what is someone bookmarks the URL "website.com/blog/post/30"? If the post's ID changed then it would link to a different post or break.

[lexkymbeth]

[http://spartanvpc.com/post/view.php](http://spartanvpc.com/post/view.php)

There is no comments on this, or likes just a feed of news what I created it for, It does not need a static ID because nothing else references it