Help With Update Query

[Rebecca217]

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

[flashxxx]

what is type of dataline field

[toggg]

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';

[Rebecca217]

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

[toggg]

Then that's a timestamp counting seconds since epoch in 1970
so could become

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

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.

[Rebecca217]

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

[r937]

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 --

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 --

update userid
   set usergroupid = 2
     , usertitle = 'member'
 where usergroupid = 14   
   and userid in
       ( list of userids  )

[Rebecca217]

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

[r937]

tell your host that 4.1 went officially stable (production status) last october

[toggg]

Hi,
Again r937 you are the best.
is it not possible to use a subquery on post ?

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.
à+

[r937]

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

[toggg]

Hi r397,

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 ?
à+

[r937]

ooops, you are right, i got the count wrong, it is less than 5

[toggg]

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

$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 ?
à+

[r937]

i am sorry, i did not understand any of your most recent post

i do not use php

[toggg]

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.
à+