I have a problem I cannot work out, Im running a shout box and when some uses the delete function "DELETE FROM chat WHERE id=$id" and then someone posts again, the new post (added with INSERT) replaces the row that the previously deleted row existed, regardless of ID and it being Primary Key.
This causes chat to be all over the place, has anyone incountered this before and am I doing something wrong?
When a record (table row) is deleted, SQL will try to fill the 'hole' in the underlying table file.
Now when you display the records, without any order by, they will be displayed in a somewhat random order.
The solution, assuming you are using 'auto-increment' for your id, is to 'order by' id.
The problem is not how the code is showing the rows on the chat.
The problem is that (i've used phpmyadmin to confirm) that the rows are being placed there in the DB also.
Which is what I don't understand, basically after deleting a row, mysql doesn't add the next INSERT to the end of the table... but where the deleted row was regardless of id/primary key or auto_increment.
that's normal behavior. tables don't really have a "front" or "end" it's kind of wherever they end up. the default sort order, as i just mentioned in another thread, is based on several factors. what does the PK of your table look like?
Reply With Quote
Bookmarks