I have two tables for a message board; one showing the subjects
(forum_subjects), and another showing the posts (forum_posts).

Every once in awhile, someone makes a post that I want to remove
quickly, and update forum_subjects back to before they posted. Here's
what I usually use:

DELETE FROM forum_posts WHERE id=12345 AND postdate > 20101103054049;

UPDATE forum_subjects SET lastname="whatever" WHERE id=12345;
UPDATE forum_subjects SET lastmodified=20101103054049 WHERE id=12345;
UPDATE forum_subjects SET size=size-1 WHERE id=12345;


The problem is that it requires me to manually plug in the last
username and postdate, so it takes a few seconds for me to get that
data. Not usually a big deal, but then today I had to update about 40
threads like that so it ended up taking awhile.

Is there a better way to do this? The first line seems pretty
necessary, of course, but after it's done, is there an automated way
to:

1. select the posts from forum_posts, then sort them by postdate;

2. grab the "username" and "postdate" fields of the last entry from
forum_posts where id=12345, then plug them in to "lastname" and
"lastmodified" (respectively) of forum_subjects where id=12345; and

3. count the results from forum_posts where id=12345, and plug it in
to the "size" field of forum_subjects?

On 11/30/2010 9:22 PM, jwcarlton wrote:
Write a program in your favorite language to do it. Shouldn't be too hard.

You probably could do it in a stored procedure, also, but I wouldn't bother.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Dec 1, 2:22*am, jwcarlton <jwcarl... (AT) gmail (DOT) com> wrote:
Well the Strawberry query will get you the data you require from the
forum_posts table and after that you just need to JOIN it to the
forum_subjects UPDATE query (dunno why you're using 3 UPDATE queries
where one would do).

On Dec 1, 2:22*am, jwcarlton <jwcarl... (AT) gmail (DOT) com> wrote:
Further on this, I have written a single query that appears to do what
you want, but there are a few aspects of your schema that I do not
understand:

1) I assume that the id number (e.g. 12345) represents the user who
made the post. This being the case, wouldn't there be a 1:1
relationship between id and username and thus username would not be a
field in forum_posts?
2) you talk about gabbing the username, but you do not say what you
want to do with it?
3) what type of field is postdate/lasmodified? I would expect it to be
a TIMESTAMP or DATETIME but in that case I would not expect to see the
format that you have posted.
4) do you have a PRIMARY key on forum_posts?
5) where does lastname come from and why would the lastname associated
with an id change?

If you can answer these I will be able to help you further.

On Dec 1, 7:09*am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:
Sure, Cap. I'll respond by number:

1. No, the ID is an auto_increment for the forum_subjects. The list of
subjects being shown is sorted by "lastmodified" in forum_subjects,
and then links to the thread by the ID; eg, www.mydomain.com/forum/view.php?msg=12345.
Then, view.php loads forum_posts, selects where id=12345, and sorts by
"postdate".

2. The "username" field in forum_posts refers to the user that made
the post. I would only want to grab the username of the last post in
forum_posts, and plug it in to the "lastname" field of forum_subjects.

3. I just have it as a varchar(14), but if there's a smarter choice
then I'm open. I originally wrote this program in 1995 and used a flat
text file instead of a database, and I've just kept that same
timestamp format over the years.

4. No, I have a PRIMARY key (Unique) on the ID field of
forum_subjects, but under forum_posts it's just an INDEX.

5. The "lastname" field just refers to the username of the last post.
If I'm removing the last post, then naturally, the lastname field
would need to change to reflect the "new" lastname.


Maybe I can make it more clear. Let's say there's a thread, id=12345,
and the last post was 11/22/10 @ 4:15:12pm by Captain Paralytic, who
also started the thread. This is in forum_subjects as:

ID=12345
LASTMODIFIED = 20101122161512
SUBJECT = "I'm cool"
FIRSTNAME = "Captain Paralytic"
LASTNAME = "Captain Paralytic"
SIZE = 1

Then, on 11/29/10 @ 2:19:26pm, the new user, say "Tad", makes a post
that says "I'm stupid". Now, forum_subjects says:

ID=12345
LASTMODIFIED = 20101129141926
SUBJECT = "I'm cool"
FIRSTNAME = "Captain Paralytic"
LASTNAME = "Tad"
SIZE = 2

I want to remove Tad's post, and revert forum_subjects back to how it
was. Currently, I'm sending in a query to delete everything above the
first timestamp, followed by 3 queries to change LASTMODIFIED,
LASTNAME, and SIZE, but I'm having to manually plug in the data. And
yes, I agree that this method is very inefficient! LOL

On Nov 30, 10:37*pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:
That's kind of what I was thinking, too, Jerry. I can write it in PHP
or Perl pretty easily, but I was hoping that there might be an easy /
fast solution in just modifying what I'm already doing :-)

On Dec 2, 12:47*am, jwcarlton <jwcarl... (AT) gmail (DOT) com> wrote:
Personally I would still have some sort of primary key on the
forum_posts table.

Anyway, hopefully this update query should do pretty much what you
want (once you have executed the delete). Incidentally, if your
intention is to just delete the latest post for a thread, then you can
use a similar approach in the DELETE query, thus not having to worry
about the postdate at all.


UPDATE forum_subjects fs
JOIN forum_posts fp1 ON fs.id = fp1.id
LEFT JOIN forum_posts fp2 ON fp1.id = fp2.id AND fp1.lastmodified <
fp2.lastmodified
SET
fs.lastname=fp1.username,
fs.lastmodified=fp1.lastmodified,
fs.size=fs.size-1
WHERE fs.id=12345 AND fp2.id IS NULL

On Dec 2, 3:59*am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:
Thanks a lot, Capt! I'll play with it on a test db, but it definitely
reads like it's on the right track.