SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help With Update Query

    I'm trying to write a MySQL query which does the following:

    Checks to see that each userid in usergroupid 14 has five (or more) new entries in the post table within the last week. If not, changes their usergroupid to 2, and their usertitle to "member."

    I'm working with two tables:

    table: user
    userid, usergroupid, usertitle

    table: post
    userid, dateline

    MySQL version - 4.0.22-standard

    Can anyone please tell me how to do this correctly?

    Rebecca

  2. #2
    SitePoint Member flashxxx's Avatar
    Join Date
    Dec 2004
    Location
    Bulgaria
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what is type of dataline field

  3. #3
    SitePoint Addict toggg's Avatar
    Join Date
    Jan 2005
    Location
    Auvergne/France
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    UPDATE user, post
    SET user.usergroupid='2'
    WHERE usergroupid='14'
    AND count(post.*) < 5
    AND post.userid = user.userid
    AND post.dateline BETWEEN 'datestart' AND 'dateend';

    Explain what is dateline so to build 'datestart' and 'dateend';
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source

  4. #4
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow! Thanks so much for the quick responses!

    The dateline field contains the time when the post was made.

    In PhpMyAdmin, dateline is listed like this:

    Field: dateline
    Type: int(10)
    Attributes: UNSIGNED
    Null: No
    Default: 0
    Extra:

    An example of a dateline entry looks like this: 1107359346

    How would this change the query?

    Rebecca

  5. #5
    SitePoint Addict toggg's Avatar
    Join Date
    Jan 2005
    Location
    Auvergne/France
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Then that's a timestamp counting seconds since epoch in 1970
    so could become
    Code:
    UPDATE user, post
    SET user.usergroupid='2'
    WHERE usergroupid='14'
    AND count(post.*) < 5
    AND post.userid = user.userid
    AND post.dateline
    BETWEEN DATE_ADD(CURDATE(),INTERVAL -1 WEEK)
    AND CURDATE();
    when you want for last week from now.
    Thus you could try
    Code:
    UPDATE user, post
    SET user.usergroupid='2'
    WHERE usergroupid='14'
    AND count(post.*) < 5
    AND post.userid = user.userid
    AND WEEK(post.dateline) = '14';
    ;
    to make the week 14.
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source

  6. #6
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again for your reply!

    It seems my version of MySQL (4.0.22-standard) is choking on this somehow. I'm getting the following error message with both versions of the code (through PhpMyAdmin):

    MySQL said:

    #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '*) < 5 AND post.userid = user.userid AND post.dateline BETWEEN
    Any suggestions?

    Thanks!

    Rebecca

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i've never managed to get a multi-table update query that contains both a join and an aggregate function to work

    what you want is this --
    Code:
    update userid
       set usergroupid = 2
         , usertitle = 'member'
     where usergroupid = 14   
       and userid in
           ( select userid
               from post
              where from_unixtime(dateline)
                    between date_add(current_date
                               , interval -1 week)
                        and current_date       
             group by userid
             having count(*) >= 5 )
    but this is not supported prior to version 4.1

    in the meantime, you could run the subquery by itself, it will return a list of userids that you then insert into the outer query and run it second, like this --
    Code:
    update userid
       set usergroupid = 2
         , usertitle = 'member'
     where usergroupid = 14   
       and userid in
           ( list of userids  )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much! My host said they couldn't upgrade to 4.1 until it was officially "stable," so I'm going to try playing around with the two query method you mentioned.

    Again, thank you all for your time, and for pointing me in the right direction. You're the best!

    Rebecca

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    tell your host that 4.1 went officially stable (production status) last october

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict toggg's Avatar
    Join Date
    Jan 2005
    Location
    Auvergne/France
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    Again r937 you are the best.
    is it not possible to use a subquery on post ?
    Code:
    update user set usergroupid = 2
     where usergroupid = 14   
       and (5 > ( select count(*) from post
                      where post.userid = user.userid
                         and dateline between
                              date_add( current_date(), interval -7 day)
                        and current_date()
                    )
             );
    By 4.0.22 could work.
    +
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    toggg i don't understand your question, subqueries of any kind are not supported until 4.1

    also, sorry, your query won't work -- the function is called current_date, not current_date(), you must convert dateline because it's an integer, you have 5 > but it should be 5 <=, and you forgot to change usertitle

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict toggg's Avatar
    Join Date
    Jan 2005
    Location
    Auvergne/France
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r397,
    Quote Originally Posted by r937
    toggg i don't understand your question, subqueries of any kind are not supported until 4.1
    I'm not that kind of specialist from mysql versions and capabilities.
    In fact, I would never recommand mysql.

    also, sorry, your query won't work -- the function is called current_date, not current_date(),
    Actually, it's a function so () should be required. I runned the subquery with () without difficulty.

    you must convert dateline because it's an integer,
    Ooops missed Rebecca's reply so from_unixtime() is to be used.

    you have 5 > but it should be 5 <=
    It's those NOT having 5 or more to be updated, I'm afraid I'm right.
    then that should be a left outer join to get those not having anything too.
    and you forgot to change usertitle
    Yes.

    How could that be done with a temporary table ?
    +
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    ooops, you are right, i got the count wrong, it is less than 5

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict toggg's Avatar
    Join Date
    Jan 2005
    Location
    Auvergne/France
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Give a chance to PHP

    Hi make a cross back to PHP
    I you access PHP do that so;
    I mean if you have a command line
    and you may say:
    # php myscript.php
    PHP Code:
    $now mktime();
    $then $now - (7*24*60*60);

    /* Connecting, selecting database */
    mysql_connect("localhost""postdb_user""postdb_passwd")
        or die(
    "Could not connect : " mysql_error());

    mysql_select_db("postdb") or die("Could not select database");

    $query "SELECT userid FROM post
              WHERE dateline BETWEEN 
    {$then} AND {$now}
              GROUP BY userid  
              HAVING (COUNT(*) > 4);"
    ;
    $result mysql_query($query) or die("Query post failed : " mysql_error());

    $update 'UPDATE user
               SET usergroupid = 2, usertitle = "blah"
               WHERE usergroupid = 14
               AND userid NOT IN ('
    ;
    // put users having at least 5
    while ($postid mysql_fetch_row($result) ) {
        
    $update .= " '{$postid[0]}' , ";
    }
    // end with an empty not existing user, case list empty
    $update .= " '' );";
    $resupd mysql_query($update) or die("Update user failed : " mysql_error());
    $nb mysql_affected_rows ();
    echo 
    "\n $nb users updated\n"
    It's tested out with ancient mysql versions
    He, r937, (I must remember it's as Boeing still don't love PHP ?
    +
    Last edited by toggg; Feb 3, 2005 at 12:58. Reason: filp > and < again!
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i am sorry, i did not understand any of your most recent post

    i do not use php
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Addict toggg's Avatar
    Join Date
    Jan 2005
    Location
    Auvergne/France
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry for you, read the script even if you don't php.
    The first query searchs all userid who posted 5 or more
    The second updates all user with userid=14 not in the list from first query.
    Somebody there understanding ?
    Sure you use php,
    say you don't script it.
    +
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •