SitePoint Sponsor |
|
User Tag List
Results 1 to 16 of 16
Thread: Help With Update Query
-
Feb 1, 2005, 23:32 #1
- 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
-
Feb 2, 2005, 02:25 #2
- Join Date
- Dec 2004
- Location
- Bulgaria
- Posts
- 5
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
what is type of dataline field
-
Feb 2, 2005, 02:44 #3
- 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
-
Feb 2, 2005, 10:32 #4
- 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
-
Feb 2, 2005, 11:05 #5
- 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();
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'; ;
bertrand Gugger toggg.com linux, PHP, Auvergne/France open source
-
Feb 2, 2005, 12:28 #6
- 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
Thanks!
Rebecca
-
Feb 2, 2005, 13:10 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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 )
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 )
-
Feb 2, 2005, 20:20 #8
- 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
-
Feb 2, 2005, 20:33 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
tell your host that 4.1 went officially stable (production status) last october
-
Feb 3, 2005, 03:18 #10
- 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() ) );
à+bertrand Gugger toggg.com linux, PHP, Auvergne/France open source
-
Feb 3, 2005, 05:44 #11
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Feb 3, 2005, 07:58 #12
- Join Date
- Jan 2005
- Location
- Auvergne/France
- Posts
- 253
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Hi r397,
Originally Posted by r937
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(),
you must convert dateline because it's an integer,
you have 5 > but it should be 5 <=
then that should be a left outer join to get those not having anything too.
and you forgot to change usertitle
How could that be done with a temporary table ?
à+bertrand Gugger toggg.com linux, PHP, Auvergne/France open source
-
Feb 3, 2005, 08:07 #13
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
ooops, you are right, i got the count wrong, it is less than 5
-
Feb 3, 2005, 12:41 #14
- 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";
He, r937, (I must remember it's as Boeingstill 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
-
Feb 3, 2005, 15:15 #15
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
i am sorry, i did not understand any of your most recent post
i do not use php
-
Feb 3, 2005, 16:35 #16
- 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