Printable View
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
what is type of dataline field
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';
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
Then that's a timestamp counting seconds since epoch in 1970
so could become
when you want for last week from now.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();
Thus you could try
to make the week 14.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';
;
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):
Any suggestions?Quote:
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
Thanks!
Rebecca
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 --but this is not supported prior to version 4.1Code: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 )
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 )
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! :D
Rebecca
tell your host that 4.1 went officially stable (production status) last october
;)
Hi,
Again r937 you are the best.
is it not possible to use a subquery on post ?
By 4.0.22 could work.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()
)
);
à+
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
:)
Hi r397,
:rolleyes: I'm not that kind of specialist from mysql versions and capabilities.Quote:
Originally Posted by r937
In fact, I would never recommand mysql.
Actually, it's a function so () should be required. I runned the subquery with () without difficulty.Quote:
also, sorry, your query won't work -- the function is called current_date, not current_date(),
Ooops missed Rebecca's reply so from_unixtime() is to be used.Quote:
you must convert dateline because it's an integer,
It's those NOT having 5 or more to be updated, I'm afraid I'm right.Quote:
you have 5 > but it should be 5 <=
then that should be a left outer join to get those not having anything too.
Yes.Quote:
and you forgot to change usertitle
How could that be done with a temporary table ?
à+
ooops, you are right, i got the count wrong, it is less than 5
;)
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
It's tested out with ancient mysql versions ;)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";
He, r937, (I must remember it's as Boeing :) still don't love PHP ?
à+
i am sorry, i did not understand any of your most recent post
i do not use php
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.
à+