Updating previous status as dead when new update is inserted.

Question

I have a simple insert query on my social network that allows the user to enter text in a text area and have it output on their profile page. They have the option to delete and it marks the status as dead,as intended, and erases the update. The problem is, if they just type in a new one, without deleting what is already there (which I want them to be able to do) Technically they have not deleted the previous submission, and it reloads the page with their old post.

I want to keep it to where they have the option to delete and it marks it dead in db (as it is now) but when they write a new one,without deleting, it automatically marks the previous submission as dead.

I have this on my delete script:

$query = "UPDATE `UserUpdates` SET `status` = 'dead' WHERE `id` = '".$UserUpdates."'";
$request = mysql_query($query,$connection);

I am a newbie,but understand the logic of what I need to do, just am not sure of the required syntax/function

Answer 1

Can't you just keep all of the status records and just pull off the latest one (the record with highest ID) as their current status? you don't really need to mark them as dead when a new one is added as you would only ever need to pull of the latest record anyway.

Answer 2

Why not run the deletion script for the current status after you insert the new status? I'll assume that you know that current status ID (let's call it $c_status_id).

INSERT INTO statuses VALUES ( ... )
DELETE FROM statuses WHERE id = "$c_status_id"

That way, you only have the most recent status update.

Answer 3

It sounds like you are going to have to run 2 queries:

1. update UserUpdates to mark the existing one as dead
2. insert the new row

If you are using Innodb or another transactional engine, you can wrap these with "BEGIN TRANSACTION" and "COMMIT" so that the 2 updates are executed as a single atomic operation.

To simplify your application, a stored procedure can be written so that all your application has to do is call a single stored procedure.