deleting entry at an ID# in table, but also deleting ID#

[DiamondLil]

Hi all.
I'm trying to delete an entry according to an auto-increment id in a MySQL db table, and it does delete the record, but it doesn't automatically fix the id #'s so that the next record would take over the deleted one's ID #. I didn't want any break in the series of ID#'s after deleting the record.

here is the link:

http://www.mc2ads.com/prologi/edit2.php

this is what I'm using :
$query="DELETE FROM prologiloads WHERE id=$deleteid";
mysql_query($query);

What should I be using...?

Be gentle with me, I'm new....

 

[danielhozac]

This is just the way that MySQL is built. Why do you want to remove the IDs? //Daniel

 

[sleipnir214]

You're not doing anything wrong. That's the way auto_increment works. And not just in MySQL -- as far as I know, all database servers work that way.

In a simple single-table database schema, it would be easy to change id numbering. But if you have a schema that contains multiple tables which relate additional information to the records in your main table by matching that id number, there is no way for the server to deterministically know which other tables' numbering it should also change.
______________________________________________________________________
TANSTAAFL!

 

[DiamondLil]

danielhozac & sleipnir214, thanks, guys - the two of you are never too far apart.

I didn't want to remove them per se, rather renumber so that there would be no break in the sequence. I suppose I thought that since it was auto-increment, it would also auto-subtract if you removed the corresponding ID's row data(so much for Lillian-logic). But, as you indicate, it would probably wreak havoc on anything that depended on that # to identify something in the first table.

Mind you I haven't graduated to more than one table - yet....

 

[najjar]

Hi,
You can read the value of the ID no and save it in a different variable (ID2) then delete using ur statement
and update the table set the last ID in the table to the one that u saved in the new variable (ID2)

(This is IF u do not care about the positions of ur record)

 

[asunpraise]

I had a similar problem. I had to delete records and display them in sequence without break, like 1,2,3,4,5,6,7.....continuously. Can I make up an arry, so that every time when

while ($row=mysql_fetch_array($queryresult))
echo &quot; <td width=10% align=center height=46>&quot;.$row[&quot;pid&quot;].&quot;</td>\n&quot;;

executes, I can create a line of numbers without break and display them in the table?

I don't know how though. Thanks a lot.

 

[LittleHavoc]

Normally you would create references to those rows in other tables, so it is inadvisable to mess around with it.

I have done a little research into re-ordering a MySql table to save doing some administration, in case the table becomes full. BUT there is nothing in the manual about this. The only way I can think of is to read the data into a second temporary table, drop the original table, re-create it an copy back the information. Obviously you would only need to do this once and a while if your index size is quite big. And you must prevent people from accessing the site while your doing it.
If you do plan on doing this you can query the table and get the structure information for recreating it, rather than hard coding it. This way if you change the table you will get to keep those changes.