Deleting a single row

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pilau
Forum Regular
Posts: 594
Joined: Sat Jul 09, 2005 10:22 am
Location: Israel

Deleting a single row

Post by pilau »

Yo.
I am working on a shout-box module, and I started from scratch.
I generated a table with 15 rows, each row has ID, name, and message fields.
I didn't had any trouble with reading the messages, nor with inserting more messages into the shoutbox table, but I had problems with moving on to the next level. I want to delete the oldest message around before inserting the newest message. Problem is, I did this by first deleting the first row:

Code: Select all

mysql_query("DELETE FROM shoutbox LIMIT 1;");
And then I inserted the the new message:

Code: Select all

mysql_query("INSERT INTO shoutbox (name, message) VALUES (\"".$_POST['name']."\", \"".$_POST['message']."\");");
What actually happens is the MySQL inserts the next row at the same location the last row was removed from, i.e. if I deleted the first row, it will insert it into the first row's location, means that I will delete the new message every time I insert a new one.
Any way to go around this? Thanks.
Last edited by pilau on Mon Jan 02, 2006 2:23 am, edited 1 time in total.
Gambler
Forum Contributor
Posts: 246
Joined: Thu Dec 08, 2005 7:10 pm

Post by Gambler »

You don't need to use database for storing 15 messages. Make an array, serialize it, use file locking to prevent simultaneous access by reader and writer code. Then just append messages, and array_shift that array, in case it's bigger than 15 items.
pilau
Forum Regular
Posts: 594
Joined: Sat Jul 09, 2005 10:22 am
Location: Israel

Post by pilau »

Gambler wrote:You don't need to use database for storing 15 messages. Make an array, serialize it, use file locking to prevent simultaneous access by reader and writer code. Then just append messages, and array_unshift that array, in case it's bigger than 15 items.
But I want to use a DB.
Can you help me with that?
mickd
Forum Contributor
Posts: 397
Joined: Tue Jun 21, 2005 9:05 am
Location: Australia

Post by mickd »

give each row a time field, insert time() into it when you create the row. then when deleting it, order by time desc limit 1.

that should do it.
pilau
Forum Regular
Posts: 594
Joined: Sat Jul 09, 2005 10:22 am
Location: Israel

Post by pilau »

Thanks mickd, though I have already solved it on my own ay before I saw your post.

Anybody interested in seeing the solution?
mickd
Forum Contributor
Posts: 397
Joined: Tue Jun 21, 2005 9:05 am
Location: Australia

Post by mickd »

posting it will do no harm ;) never know, one day someone might search up this thread and find their answer.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Problem is with using time, that you might have two messages with the same time...

A better solution would be use to an artificial primary key (autoincrement / sequence that is augmented for each row that is added). Now you only have to delete the row with MIN(primary_key)
pilau
Forum Regular
Posts: 594
Joined: Sat Jul 09, 2005 10:22 am
Location: Israel

Post by pilau »

timvw wrote:Problem is with using time, that you might have two messages with the same time...

A better solution would be use to an artificial primary key (autoincrement / sequence that is augmented for each row that is added). Now you only have to delete the row with MIN(primary_key)
God that is exactly what I needed! That is the time-saving solution I had in mind but I didn't know whether there was an SQL function that could get me the smallest index..
Anyway, I made a similar process.
mickd wrote:posting it will do no harm ;) never know, one day someone might search up this thread and find their answer.
I will do that, don't worry :)
Last edited by pilau on Wed Jan 04, 2006 2:32 pm, edited 1 time in total.
pilau
Forum Regular
Posts: 594
Joined: Sat Jul 09, 2005 10:22 am
Location: Israel

Post by pilau »

Ok, and now - as promised! The shoutbox I programmed! *Drum roll*

Kay. Now, I used two pages, one to display the messages and one to add a new message to the MySQL table.
Here's the easy part, printing out the table:

Code: Select all

$result = mysql_query("SELECT * FROM shoutbox ORDER BY `id` DESC") or die(mysql_error());

    if ($result && mysql_num_rows($result)) {
        $numrows = mysql_num_rows($result);
        while ($row = mysql_fetch_assoc($result)) {
            print $row['name'].": ".$row['message'];
            print "<BR />";
        }
    }
As you can see this is juat a raw read and output procedure; Nothing more than that.

Moving on, to the more sophisticated part. I decided that a round 15 is the optimal amount for messages to be stored and viewed for a shout-box. Of course you might think differently. Anyway, I see no point in keeping those messages who are beyond that 15-tops limit. Then what we (that is, I) need to do, actually, is delete the oldest message in the table every time a new message is added to the shout-box. This is done this way:

Code: Select all

mysql_query("DELETE FROM shoutbox WHERE `id` = 1") or die(mysql_error());

for($i=2; $i<=15; $i++)
{
    $temp=$i-1;
	mysql_query("UPDATE `shoutbox` SET `id` = \"{$temp}\" WHERE `id` =\"".$i."\" LIMIT 1;") or die(mysql_error());
}

mysql_query("INSERT INTO shoutbox (id, name, message) VALUES (15, \"".$_POST['name']."\", \"".$_POST['message']."\");") or die(mysql_error());
As you can see, the first query deletes the row with id=1, i.e. the oldest message.

Code: Select all

DELETE FROM shoutbox WHERE `id` = 1
Next thing I do, is decrease all the IDs of the 14 rows still in the table by 1. (for loop)

The final step is to add the new message - and give it the ID of 15! This way it gets to the back of the line.

Code: Select all

INSERT INTO shoutbox (id, name, message) VALUES (15, \"".$_POST['name']."\", \"".$_POST['message']."\");
As you can see this shout-box algorithm works according to the principle of FiFo - First in First out.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Code: Select all

START TRANSACTION;
DELETE FROM messages WHERE message_id = (SELECT MIN(message_id)FROM messages)
UPDATE messages SET message_id = message_id -1;
COMMIT;
pilau
Forum Regular
Posts: 594
Joined: Sat Jul 09, 2005 10:22 am
Location: Israel

Post by pilau »

Could you explain that please timvw? I'm not such a master with SQL. I need to get a book :)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

operation1: delete the row with the smallest id
operation2: update all the (remaining rows), decrease their id with 1

I've wrapped these two operations in a transaction, because they should be considerd as one. I don't want the first to happen if the second can't be completed.

I once had a "ladder game", and it was possible that such an update script ran for multiple players at the same time. And once in a whille (without the use of transactions) the ladder seemed to break...
pilau
Forum Regular
Posts: 594
Joined: Sat Jul 09, 2005 10:22 am
Location: Israel

Post by pilau »

That's great. I'll update my shout box code.
Mean while, you can take a look at that shout box itself, at http://www.wormiverse.com/rewclan/scroller/
(The bottom menu don't seem to show on FireFox for some reason, still working on it.)

Oh and don't expect for anything special on the page itself except for the checkbox, because this is a page I used to practice layout making on.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

btw, there is not really a need to update the ids.. I prefer to use AUTOINCREMENT an delete the smallest one..

It starts from 0 to 14 and after a while i end up with 30 to 44.. And then i have to moderator something, eg: 37
so i end up with 30 to 36 and 38 to 45...
pilau
Forum Regular
Posts: 594
Joined: Sat Jul 09, 2005 10:22 am
Location: Israel

Post by pilau »

timvw wrote:btw, there is not really a need to update the ids.. I prefer to use AUTOINCREMENT an delete the smallest one..

It starts from 0 to 14 and after a while i end up with 30 to 44.. And then i have to moderator something, eg: 37
so i end up with 30 to 36 and 38 to 45...
That was my first idea, but I prefare (on this case) to do update the IDs "manually".
Post Reply