|
Select which part of the site you would like to view by clicking on the links below.
The pictures are currently offline as they have become too old.
If you wish to email me, please use the contact form.
|
 |
MySQL queries that can help vBulletin Forum Administrators |
 |
This is a collection of 10 MySQL queries that vBulletin Forum Administrators will find helpful.
Table prefixes are not used in these examples, so if your database uses them, simply insert your prefix
before any table name you may see. Without a prefix:
SELECT userid FROM user WHERE username = 'Lats'
With a prefix of vb3_ :
SELECT userid FROM vb3_user WHERE username = 'Lats'
|
| Promote by referrals | Will update the user's usergroupid to 13 if the user has 5 or more referrals credited to his name.
User's who are already in the main usergroups (1,3,4,5,6,7,8) are ignored.
Useful as the current version (3.6.x) doesn't support promotions based on referrals. | UPDATE
user,
(SELECT
referrerid
FROM
user
GROUP BY
referrerid
HAVING
count(referrerid) > 4
AND
referrerid > 0)
AS
user2
SET
user.usergroupid = 13
WHERE
user2.referrerid = user.userid
AND
usergroupid
NOT IN
(1,3,4,5,6,7,8) |
|
| Update posts without signatures | Turns on show signatures on old posts where the user didn't select to show signatures.
Change userid = 1 to the userid of the user effected. | UPDATE
post
SET
showsignature = 1
WHERE
userid = 1
AND
showsignture = 0 |
|
| Select users in an age range | Useful for finding all users who fall within a specified age range.
This example lists users who are between 15 and 18, which limits it to the 16 and 17 year olds. | SELECT
username,
usergroupid,
birthday_search,
year( current_date ) - year( birthday_search ) AS age
FROM
user
WHERE
year( current_date ) - year( birthday_search ) <18
AND
year( current_date ) - year( birthday_search ) >15
ORDER BY
birthday_search DESC |
|
| Replace old domain with new | Replace any outdated information from within posts with new information.
Particularly useful should
you ever want to start over on a new domain name and posts contain references to your old domain name. | UPDATE
post
SET
pagetext = replace(pagetext,'olddomainname', 'newdomainname')
WHERE
pagetext LIKE '%olddomainname%' |
|
| Most popular forums | | Lists all your popular forums based on the number of posts. | SELECT
title,
replycount AS posts
FROM
forum
WHERE
parentid > 0
ORDER BY
replycount DESC
|
|
| Who referred who | | Displays a list of users and who they referred to your forums. | SELECT
user.username AS referred,
referrer.username AS referrer
FROM
user
JOIN
user AS referrer ON referrer.userid = user.referrerid
ORDER BY
referrer |
|
| Top posters in forums | | Useful for finding out who are the top 5 most active posters in selected forums (2,3,4). | SELECT
post.username,
count( post.postid ) AS postcount
FROM
post
LEFT JOIN
thread ON thread.threadid = post.threadid
WHERE
thread.forumid IN (2,3,4)
GROUP BY
post.username
ORDER BY
postcount DESC
LIMIT
5 |
|
| Who voted and when | | Find out who voted and when on poll 3. | SELECT
pollvote.pollid,
user.username,
FROM_UNIXTIME(votedate,'%D %M %x %h:%i:%s') AS whenvoted
FROM
pollvote
LEFT JOIN
user
ON
user.userid = pollvote.userid
WHERE
pollid = 3
ORDER BY
votedate |
|
| Who didn't vote | | Find out who didn't vote on poll 3, complete with email address. | SELECT
user.username, user.email
FROM
user
LEFT JOIN
pollvote
ON
pollvote.pollid = 3
AND
user.userid = pollvote.userid
WHERE
pollvote.userid IS NULL
AND
user.usergroupid = 2
ORDER BY
user.username |
|
| Average age | | Find out the average age of your users. Ignores users who didn't put a valid year in. | SELECT
avg((year(current_date) - year(birthday_search))) AS The_Average
FROM
user
WHERE
birthday_search > '1800-00-00' |
|
Please note: The queries are shown in a easy to read format and may not
work directly within the Admin Control Panel without removing the vertical spacing. They do however work
well within phpMyAdmin without modification.
|
