Remove null values from database

[RonnieCosta50]

Hi.  I was curious if anyone had any other methods of removing those NULL rows in a database table.  If you don't use auto increment primary key for your database table then you don't have this problem.  But if you do use auto increment primary key then when a row is deleted for whatever reason, sql will delete the row values of every column except the the primary key column.  The primary key will remain and take up room in your database.  I have heard that other developers export the database and create a new table and then import the database back in.  That's how they solve this problem.  I prefer using php code.  But my question...
Question:  Is there another way to get rid of those NULL rows when using auto incrementing primary key in database table?

PS:  There is nothing wrong with the code below.  It works.

<?php
// DATABASE PRIMARY KEY GAP FILLER
// Developer: Ronnie Costa
// Purpose: Cleans up the database table from all the NULL rows.
// Explained: A database table with auto incrementing numbers as the primary key never deletes any primary keys.  Not even if you delete a row from the table. You will just have row 5 for example and no other data will be present because it was deleted for whatever reason. This rearranges the rows to fill those empty gaps.
// Directions: Modify the variable names to match the criteria of your database in includes dbConnect.php and dbTableName.php
// Name this file dbPKGapFiller.php
// Call this file from other php files like this <?php include 'dbPKGapFiller.php';?>
include 'dbConnect.php'; // Must be deleted if already called by another php file that initiated dbConnect.php.
include 'dbTableName.php';  // Must be deleted if already called by another php file that initiated dbTableName.
// _____________________________
// DO NOT EDIT BELOW THIS LINE
// _____________________________ 
$countFunction = "SELECT COUNT('$dbColumn1') AS num FROM $dbTableName"; // figures out how many rows there are in the database table.
$data = mysql_query($countFunction) or die(mysql_error());
$row = mysql_fetch_assoc($data);
$numRows = $row['num'];
if ( $numRows > 1 ) // If there is more than one row in the database table,
    {
    $currentRow = 1; // Counter that is used to keep track of what row it left off on after it finishes updating a row.
    $PKIDvalue = $currentRow; // Counter that is used to update the current row in the database table.
    while ($currentRow <= $numRows) // While the counter hasn't reached the number of rows in the table,
        {
        $rowIDquery = mysql_query("SELECT * FROM $dbTableName WHERE $dbColumn1 = $PKIDvalue"); // Select the primary key($dbColumn1) that matches the number on the counter($PKIDvalue).
        $rowID = mysql_num_rows($rowIDquery);
        while ($rowID < 1) // While there is a NULL row in the database table,
            {
            $PKIDvalue = $PKIDvalue + 1;
            $rowIDquery = mysql_query("SELECT * FROM $dbTableName WHERE $dbColumn1 = $PKIDvalue");
            $rowID = mysql_num_rows($rowIDquery);
            if ($rowID != 0) // If there is no primary key in the database table that matches the current counter number($PKIDValue),
                {
                mysql_query("UPDATE $dbTableName SET $dbColumn1=$currentRow WHERE $dbColumn1 = $PKIDvalue");
                }
            }
        $currentRow = $currentRow + 1; // Increases the counter($currentRow) so that it can check the next row of the database table.
        $PKIDvalue = $currentRow; // Resets the counter($PKIDvalue) that is used to update the row in the database table.
        }
    }
$reset = "ALTER TABLE $dbTableName AUTO_INCREMENT = 1";
mysql_query($reset); // Resets the primary key auto incrementor so that when it inputs a new row, it starts off in the new last row rather than the old one before it rearranged the rows.
?>

[Psycho]

Either you've received some bad information or I am totally missing something. When you delete the record - it is deleted. The DB does not maintain an empty record to hold the primary key. In fact, you can verify that by trying to create a new record with that ID. If the ID wasn't removed the INSERT would fail. But, if you deleted the record it can be reused.

 

The Database maintains an internal "counter" to specify the NEXT auto-increment ID. It doesn't maintain a history of all the ones that are used.

[RonnieCosta50]

Woops!  I'm an idiot.  I said that wrong.  What I ment to say is that it deletes a row but sql doesn't automatically insert the next row where there is a gap between auto incrementing primary keys.  For example 123567.  The number 4 was deleted from the database table.  The next time you insert a row, it will become number 8.  So my question...

Question:  Is there another way to get rid of those gaps in the database table.

[mikosiko]

PS:  There is nothing wrong with the code below.  It works.

 

 

and could cause a big mess with your database (if you are not careful) , breaking all the referential integrity of your database model (if any) creating most likely many orphan records. 

 

I will say that is no good reason to re-number your auto-increment PK's at all.

[Psycho]

and could cause a big mess with your database (if you are not careful) , breaking all the referential integrity of your database model (if any) creating most likely many orphan records. 

 

I will say that is no good reason to re-number your auto-increment PK's at all.

 

Exactly! There really is no good reason to make sure the primary keys are absolutely sequential. You can always get the count if you need that values. But, what you are trying to do is ill advised. If it is a PK, then you must be using that value somewhere else and you would have to update the values there was well. All this does is open you up to database corruption.

[RonnieCosta50]

Exactly! There really is no good reason to make sure the primary keys are absolutely sequential. You can always get the count if you need that values. But, what you are trying to do is ill advised. If it is a PK, then you must be using that value somewhere else and you would have to update the values there was well. All this does is open you up to database corruption.

I understand that if I use the primary key for anything at all wether it be linking tables together or using php code to manage tables by primary key that it would currupt the database.  But if the primary key is not being used for any other reason besides to provide  unique value to the database to work with, is there any problem with it?  But yeah now that you say that there is no good reason to do this and I thought about it, I don't know why i spent 3 weeks developing this.  I'm going to have to ponder a reason and get back to you if I come up with one.