UBB.Developers Forums Community Development Programming MySQL and Server Configuration Syntax question

Help.... heh heh

What would the mysql syntax be to select from the user base all users registered with the system prior to one month ago, and not a member of a specific group?

Ie. Registered over 30 days ago, and a member of the non-registered group.

Actually what I need to do here is everyone that logged onto the system prior to 30 days ago and has not paid their membership fees for the year need to be re-assigned from the member group to the expired group.

so whatever group(s) they're in say 3,5,7, needs to be changed to 3,7,8, type of thing.

Any one got any ideas?

This query displays users who registered more than 30 days ago and are in group 5:

SELECT U_Username,FROM_UNIXTIME(U_Registered) AS Registered,(UNIX_TIMESTAMP()-U_Registered)/(3600*24) AS "How many days ago",U_Groups
FROM w3t_Users
WHERE ((UNIX_TIMESTAMP()-U_Registered)/(3600*24) > 30)
AND U_Groups LIKE "%-5-%"

This query changes the group memberships of the same users to 3, 7 and 8:

UPDATE w3t_Users
SET U_Groups="-3-7-8-"
WHERE ((UNIX_TIMESTAMP()-U_Registered)/(3600*24) > 30)
AND U_Groups LIKE "%-5-%"

Don't run that UPDATE query unless you're sure that's exactly what you want to do. Usual disclaimer: backup the database first.

Does this answer your question?

Yes it did, MUCH cleaner than what I came up with last night in fact. Actually it's like light years ahead of what I did. heh heh...


I'd used:

UPDATE w3t_Users
SET U_Groups = '-3-5-8-'
Where U_Registered < "1025098268"
AND U_Groups ="-3-5-7-"

It was the unix time stamp that made it difficult as I had to find someone registered in that time period, edit their account via phpmyadmin, and use their registered date/time as the criteria.

THANKS Dave!!