Page 1 of 1
Deleting a single row
Posted: Mon Jan 02, 2006 2:13 am
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.
Posted: Mon Jan 02, 2006 2:20 am
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.
Posted: Mon Jan 02, 2006 2:22 am
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?
Posted: Mon Jan 02, 2006 3:30 am
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.
Posted: Mon Jan 02, 2006 4:05 am
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?
Posted: Mon Jan 02, 2006 4:07 am
by mickd
posting it will do no harm

never know, one day someone might search up this thread and find their answer.
Posted: Mon Jan 02, 2006 2:03 pm
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)
Posted: Mon Jan 02, 2006 2:49 pm
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

Posted: Wed Jan 04, 2006 2:32 pm
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.
Posted: Wed Jan 04, 2006 4:11 pm
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;
Posted: Thu Jan 05, 2006 1:00 am
by pilau
Could you explain that please timvw? I'm not such a master with SQL. I need to get a book

Posted: Thu Jan 05, 2006 5:35 am
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...
Posted: Thu Jan 05, 2006 8:43 am
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.
Posted: Thu Jan 05, 2006 9:26 am
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...
Posted: Thu Jan 05, 2006 11:48 am
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".