www.webdeveloper.com
Results 1 to 5 of 5

Thread: Insert replaces previous Delete

  1. #1
    Join Date
    Jun 2006
    Location
    England, UK
    Posts
    71

    Insert replaces previous Delete

    Hiya,

    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?

    Thanks in advance.

  2. #2
    Join Date
    Dec 2002
    Location
    Pleasanton, CA
    Posts
    2,132
    A bit of a guess...

    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.

  3. #3
    Join Date
    Jun 2006
    Location
    England, UK
    Posts
    71
    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.

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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?

  5. #5
    Join Date
    Jun 2006
    Location
    England, UK
    Posts
    71
    PHP Code:
    `idint(11NOT NULL auto_increment

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles