Useful Sql queries for admins

[Overseer]

not sure if this is the best place to post this, or if its been posted before. i guess it depends on your posters etc.. on how relevant these are to you. but i'm sure many people out there will have written some SQL queries (or if they dont know SQL well like me would pick up some things or cuold just understand them enough to use them).

I think it would be good for yse if some simple queries were available as a resource for helping check up on things that the board sw doesnt do itself.

here are ones i've written in the last few days. i'll be making more for my own use as time goes on.

i always prefer to see newest -> older in the outupt (thats the ORDER BY posterTime DESC on the end)


all u have to do to use them is dump them in the 'sql' box in phpmyadmin, change the relevant parts (like your prefix wont be dubcc_ like mine) and hit go





dump all posts by ip

SELECT * FROM dubcc_messages WHERE posterIP LIKE '111.222.333.444' ORDER BY posterTime DESC

(would like to know how to do a partial match)

posts by user aaa and user bbb

SELECT * FROM dubcc_messages WHERE posterName LIKE 'Docky' OR posterName LIKE 'Tech' ORDER BY posterTime DESC

(can check if a user is posting from the same IP between accounts.. kinda useless for AOL users tho )



dump all messages sent to...

SELECT * FROM dubcc_instant_messages WHERE toName LIKE 'Overseer' ORDER BY msgtime DESC

can check if user really got sent a message by someone, or if they have read it

dump all messages sent by

SELECT * FROM dubcc_instant_messages WHERE fromName LIKE 'Overseer' ORDER BY msgtime DESC

can check if user really sent a message to someone, or if they have read it


dump all messages sent by or to..


SELECT * FROM dubcc_instant_messages WHERE toName LIKE 'Overseer' OR fromName LIKE 'Overseer'  ORDER BY msgtime DESC

basically a log of messages to and from a user in order... useful for seeing 'who started it'

[Spaceman-Spiff]

dump all posts by ip

SELECT * FROM dubcc_messages WHERE posterIP LIKE '111.222.333.444' ORDER BY posterTime DESC

(would like to know how to do a partial match)

SELECT * FROM {$db_prefix}messages WHERE posterIP LIKE '111.222.%' ORDER BY posterTime DESC
% is the wildcard for LIKE statement
for more info: http://www.mysql.com/doc/en/String_comparison_functions.html

[Overseer]

cool thanks

* Overseer fiddles

[Overseer]

so far they've all been non-modifying ones.

i'm gonna make a test dbase and learn some other ones - like tying posts from deleted/banned accounts up with new usernames for people i've let back.

[Overseer]

here's one [unknown] wrote for me when i queried a while back in this post...




it resets all karma on a board (i did this at new year)

UPDATE dubcc_members SET karmaGood = 0, karmaBad = 0;

[David]

* David nudges this back on topic by splitting it into a new thread

http://www.yabbse.org/community/index.php?board=1;action=display;threadid=20078