Announcement

Collapse
No announcement yet.

Manual Update Help Needed

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Manual Update Help Needed

    So I recently set up a new installation (vb) of an old forum (discus) and it went off without a hitch. All data expected was captured. However, some things need to be updated manually and I am not that great with raw SQL. Could somebody tell me what query / queries I need to run to accomplish the following?

    Using table.fields as listed:

    user.userid
    user.joindate
    user.lastvisit
    user.lastactivity
    user.lastpost
    user.lastpostid
    post.postid
    post.userid
    post.dateline

    Update joindate, lastvisit, lastactivity, and lastpost based on values in post. Post has accurate value in the fields above, but user does not.

    Joindate should be equal to that users earliest post.dateline - 24 hous.
    If they haven't posted, use ealiest overall post.dateline - 24 hours.

    Lastvisit and Lastactivity should be similar, but use latest post.dateline and no time adjust.

    If somebody can get me start on this, I think I can figure out the rest of what I need on my own.

    Thanks in advance.
    House of Night Forums - An online House of Night roleplaying community,

  • #2
    MySQL version ?

    Personally I'd likely do that in a PHP script with an iterator opposed to one massive SQL statement. i.e.

    Code:
    while (get user id)
    {
    	get the post details;
    	update the user;
    }
    kind of thing.
    I wrote ImpEx.

    Blog | Me

    Comment


    • #3
      Thanks for replying, and that's exactly what I wound up doing. I got a master recordset using the following...

      select u.userid, min(p.dateline) as lower, max(p.dateline) as upper from user as u left join post as p on p.userid = u.userid group by u.userid

      ...then looped through and called updates on things as I saw fit. The data all makes sense now, even if it isn't perfect. Given some time, and member usage, the sysem will update itself with more correct values.
      House of Night Forums - An online House of Night roleplaying community,

      Comment

      widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
      Working...
      X