How do I find the missing rows?

discostudio

I have a table in the DB called 'Products' and each row has an auto increment column that has a value of 4 digits, like each line is

1001 - Sony Plasma
1145 - Sony Hifi
2321 - Kenwood Car Stereo

There are gaps because some products have been deleted in the past.

How do I get a list of index id's that are missing, so I can re-add them to the database and re-use them as we are now at 9032 and I am going to run out of product id's soon as they have to be 4 digits.

Anyone help?

devinemke

read this thread and then ask yourself if it is worth the trouble to do what you are asking.

discostudio

When ever a product is deleted att references to it are deleted also. I need to do this as the program I use for accounts requires product Id's that are four didgits and soon I will hit 9999 and then I will run out of Id's unless I can start re-using the old ones.

devinemke

discostudio wrote:
When ever a product is deleted att references to it are deleted also. I need to do this as the program I use for accounts requires product Id's that are four didgits and soon I will hit 9999 and then I will run out of Id's unless I can start re-using the old ones.

the product ID's have to be 4 digits? why? what happens then when you have more than 9999 products?

discostudio

They can't, they have to be between 1001 and 9999, so I created a table with the first auto index as 1001 and that was 4 years ago and now I have loads of gaps and I am at product Id 9966 and I need to start using the deleted ones.

I export the table data into my accounts program and the Id's have to be four digits.

bogu

Why don't u let the auto increment like he's suppose to be and use another field like product number that is 4 digits and play with that...

Is not good to play with auto increment like u play ...

Auto increment do what is suppose to do, for this stuff use another field ...

leatherback

Read bogu's post, and do what he has suggested.

You can then say, create a table with values 1001-9999 and do an -i think- innerjoin, and select all records where the record ID in product table == NULL., sort ASC by productID, and you should have a list of values till open to be used

discostudio

leatherback wrote:
Read bogu's post, and do what he has suggested.

You can then say, create a table with values 1001-9999 and do an -i think- innerjoin, and select all records where the record ID in product table == NULL., sort ASC by productID, and you should have a list of values till open to be used

J.

I don't think I understand, could you write me a sample code?

discostudio

No you guys are getting the wrong end of the stick.

I need to simply find out the missing IDs in a auto increment table after rows have been deleted.

There must be a simply mysql "SELECT" that can extract this data.

Installer

$query = "SELECT product_id FROM my_table";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
    $numbers[] = $row['product_id'];
]
for ($i = 1001; $i < 10000; $i++) {
    if (!in_array($i, $numbers)) {
        echo $i . ' is missing.<br />';
    } else {
        echo $i . ' is not missing.<br />';
    }
}

That's to help you do what you want to do. The other guys are trying to help you be better at what you do.