mysql conundrum

Anon

Hi i have a auto increment files as an id.
what i would like to do is list the ones what have been deleted
so say i have 10 records in the table
and id-3,5 have been deleted is their a query i can do.
or some php code to work out the missing numbers?

Regards

Neil

Anon

Seems to me that if the records have been deleted, there will be no way to do a query that will find them.

You might try creating another table and do an insert into this table the files from the table that you delete from?

Then query the new table based on the missing id #s???

This is interesting, let me know what you come up with.

Anon

Thanks for that.
What im hoping is possible is that Ii can create an array with the last id number in the table, along will all the other ID's and php can work out the missing numbers.

The only problem is how?

Anon

I think you may need to write a function that does 3 things: 1) select the records you want to delete; 2) archive these in a new table; 3) delete records from original table. I did not test this, so beware, but this should get you started. Let me know if I'm all wet. Once you do this, then all you have to do is ("SELECT * FROM archive WHERE whatever"); to list your deletions. Don't worry about ID#s, eh?
function archive_and_delete() {

mysql_connect("$Host","$User","$Pass") or die ("The database server is temporarily down for maintenance");

// select rows for deletion based on the variable '$address'
$result=mysql("$Name","SELECT entry_time FROM your_table WHERE address='$address'");
while ($row = mysql_fetch_row($result)) {
$tim=$row[0];
}
// put selected records into table named 'archive' before deleting
$result=mysql("$Name","INSERT '$tim' INTO archive WHERE address='$address'");

// now delete the original selection from 'your_table'
$result=mysql("$Name","DELETE '$tim' FROM your_table WHERE address='$address'");
}