Announcement

Collapse
No announcement yet.

Run This Query To: version 3.0.x

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Run This Query To: version 3.0.x

    FAQ: What you need to know about running queries

    Create at least two working database backups before attemping to Executing Queries.
    • How to make a backup of vbulletin board database
    • Upload and place your vBulletin version behind .htaccess and play with it, test hacks and add your template modification.
    • Test all your Catagories, Forums, Threads, Posts, User Cp, Adminastrative / Moderator board functions.
    • List all your hacks on a piece of paper, then visit www.vbulletin.org and find those edit and make sure they are working.



    Backup tools if you do not have SHH access from your host.

    What is .htacces

    Use the Update Counters if ask to after Execute SQL Query is completed.


    So please ask lots and lot of question before executing these queries on your forums database.

    Queries posted here are from members, vBulletin.com and vBulletin.org Staff that posted in these forums and are not to be taken likely, as these can have irreversible affects.


    vBulletn version 3.5Updating Options En Masse with Queries
    vBulletn version 3.6Updating Options En Masse with Queries


    Please do not post question in this thread reguarding information posted here. You would be better severed if you create your own thread and point to post that you have a question about or like to know if a simular query can work.
    Jose Amaral Rego
    Senior Member
    Last edited by Jose Amaral Rego; Wed 2 May '07, 6:27pm.

  • #2
    Run Query to Reset Count to *

    Run Query to Reset Count to *
    Online total users to any X amount
    # replace vbb_ with your vB table prefix Example: [vbb_datastore] if non leave blank
    # replace X with new total
    Code:
    UPDATE datastore SET data = '[COLOR="Red"]X[/COLOR]' WHERE title = 'maxloggedin';
    Run Query to Reset Count to *
    Online total users to any X amount at certain date
    # replace vbb_ with your vB table prefix Example: [vbb_datastore] if non leave blank
    # replace X with new total
    # replace date with Unix timestamp Example: 1167991932 = 01-05-2007 10:12:12
    Code:
    UPDATE datastore SET data = 'a:2:{s:9:\"maxonline\";s:2:\"[COLOR="red"]X[/COLOR]\";s:13:\"maxonlinedate\";s10::\"[COLOR="blue"]date[/COLOR]\";}' WHERE title = 'maxloggedin';
    http://www.onlineconversion.com/unix_time.htm
    http://www.4webhelp.net/us/timestamp.php


    Run Query to Reset Count to *
    Members private message to X amount
    # replace vbb_ with your vB table prefix Example: [vbb_user] if non leave blank
    # replace X with new total
    Code:
    UPDATE user SET pmtotal = '[COLOR="red"]X[/COLOR]';
    Code:
    UPDATE user SET pmread = '[COLOR="red"]X[/COLOR]';
    Run Query to Reset Count to *
    Members reputation to X amount
    # replace vbb_ with your vB table prefix Example: [vbb_user] if non leave blank
    # replace X with new total
    Code:
    UPDATE user SET reputation ='[COLOR="Red"]X[/COLOR]';
    Run Query to Reset Count to *
    Members reputation of usergroup to X amount for selected usergroup identification number
    # replace vbb_ with your vB table prefix Example: [vbb_user] if non leave blank
    # replace X with new total
    # replace X with the new usergroup id
    Code:
    UPDATE user SET reputation = '[COLOR="red"]X[/COLOR]' WHERE usergroupid = '[COLOR="blue"]X[/COLOR]';
    Run Query to Reset Count to *
    Member(s) reputation to X amount for selected user identification(s) number
    # replace vbb_ with your vB table prefix Example: [vbb_user] if non leave blank
    # replace X with new total
    # replace X with the new user id(s)
    Code:
    UPDATE user SET reputation = '[COLOR="red"]X[/COLOR]' WHERE userid = '[COLOR="blue"]X[/COLOR]';
    Run Query to Reset Count to *
    Forum post replycount to X amount for selected forum identification number
    # replace vbb_ with your vB table prefix Example: [vbb_forum] if non leave blank
    # replace X with new total
    # replace X with the forum id
    Code:
    UPDATE forum SET replycount = '[COLOR="Red"]X[/COLOR]' WHERE forumid = '[COLOR="Blue"]X[/COLOR]';
    Run Query to Reset Count to *
    Thread views to X amount for selected thread identification number
    # replace vbb_ with your vB table prefix Example: [vbb_thread] if non leave blank
    # replace X with new total
    # replace X with the new thread id
    Code:
    UPDATE thread SET views = '[COLOR="red"]X[/COLOR]' WHERE threadid = '[COLOR="blue"]X[/COLOR]';
    Admin CP -> Import & Maintenance -> Update Counters -> Rebuild Thread Information

    Run Query to Reset Count to *
    All Thread views to X amount
    # replace vbb_ with your vB table prefix Example: [vbb_thread] if non leave blank
    # replace X with new total
    Code:
    UPDATE thread SET views = '[COLOR="red"]X[/COLOR]';
    Admin CP -> Import & Maintenance -> Update Counters -> Rebuild Thread Information
    Jose Amaral Rego
    Senior Member
    Last edited by Jose Amaral Rego; Thu 3 May '07, 1:34am. Reason: Reformating page layout

    Comment


    • #3
      Run Query to * Soft Deleted Posts

      Run Query to * Soft Deleted Posts
      Run this Query using phpMyAdmin
      Restore
      # replace vbb_ with your vB table prefix Example: [vbb_deletionlog] if non leave blank
      Code:
      DELETE FROM deletionlog 
      WHERE type = 'post'
      Code:
      UPDATE post
      SET visible = 1
      WHERE visible = 2
      Permantly Remove
      # replace vbb_ with your vB table prefix Example: [vbb_deletionlog] if non leave blank
      Code:
      DELETE FROM deletionlog
      WHERE type = 'post'
      and or

      Code:
      DELETE 
          post
      FROM
          post, deletionlog
      WHERE
          post.postid = deletionlog.primaryid
      Remove post after X days
      # replace vbb_ with your vB table prefix Example: [vbb_post] if non leave blank
      # replace X with numerical day
      Code:
      DELETE FROM post
      WHERE visible = 2
      AND dateline < UNIX_TIMESTAMP() - (60 * 60 * 24 * [COLOR="Red"]X[/COLOR])
      Permantly Remove by specific Moderator X identification
      # replace vbb_ with your vB table prefix Example: [vbb_post] if non leave blank
      # replace X with the user id
      Code:
      DELETE FROM post
      USING post AS s1, deletionlog AS s2
      WHERE s1.postid = s2.primaryid 
      AND s2.userid = [COLOR="red"]X[/COLOR]
      Code:
      DELETE FROM deletionlog
      WHERE userid = [COLOR="red"]X[/COLOR]
      Jose Amaral Rego
      Senior Member
      Last edited by Jose Amaral Rego; Wed 2 May '07, 1:01am. Reason: Reformat Post

      Comment


      • #4
        Run Query to Find Statistics for *

        Run Query to Find Statistics for *
        Run this Query using phpMyAdmin
        Most viewed threads and display X total threads
        # replace X with amount to display
        Code:
        SELECT 
            thread.title, 
            thread.replycount, 
            thread.views,
            forum.title AS forum 
        FROM 
            thread 
        LEFT JOIN
            forum ON thread.forumid = forum.forumid
        ORDER BY 
            thread.views DESC
        LIMIT [COLOR=red]X[/COLOR]

        Run Query to Find Statistics for *
        Memebers with post count X and with * in User CP
        = Equal
        <> or != Not Equal
        < Less Than
        > Greater Than
        <= Less Than or Equal To
        >= Greater Than or Equal To
        Code:
        SELECT username FROM user WHERE posts = [COLOR=red]X[/COLOR] AND homepage > '[COLOR=blue]http://[/COLOR]'
        Run Query to Find Statistics for *
        Member(s) total post count within selected time frame in descending order
        Change YYYY-MM-DD start to numerical values
        Change YYYY-MM-DD end to numerical values
        Code:
        SELECT COUNT(postid) AS count, user.username
        FROM post
        LEFT JOIN user ON (user.userid = post.userid)
        WHERE dateline > UNIX_TIMESTAMP('[COLOR=red]YYYY-MM-DD[/COLOR]') AND dateline < UNIX_TIMESTAMP('[COLOR=blue]YYYY-MM-DD[/COLOR]')
        GROUP BY post.username
        ORDER BY 'count' DESC
        Run Query to Find Statistics for *
        Member(s) total post count and exclude counts from selected forum identification within selected time frame in descending order
        Change YYYY-MM-DD start to numerical values
        Change YYYY-MM-DD end to numerical values
        Code:
        SELECT COUNT(postid) AS count, user.username
        FROM post
        LEFT JOIN user ON (user.userid = post.userid)
        LEFT JOIN thread ON (thread.threadid = post.threadid)
        WHERE post.dateline > UNIX_TIMESTAMP('[COLOR=red]YYYY-MM-DD[/COLOR]') AND post.dateline < UNIX_TIMESTAMP('[COLOR=blue]YYYY-MM-DD[/COLOR]')
        AND forumid NOT IN ([COLOR=green]X,Y,Z[/COLOR])
        GROUP BY post.username
        ORDER BY 'count' DESC
        Run Query to Find Statistics for *
        Memebers with option enable to Receive Email from Administrators in User CP
        Code:
        SELECT * FROM user WHERE (options & 16)
        Jose Amaral Rego
        Senior Member
        Last edited by Jose Amaral Rego; Thu 3 May '07, 1:30am.

        Comment


        • #5
          Future Post

          Future Post
          Jose Amaral Rego
          Senior Member
          Last edited by Jose Amaral Rego; Tue 1 May '07, 2:16am. Reason: Future Post

          Comment


          • #6
            Run this query to show new users signatures on old posts and threads

            Run this query to show new users signatures on old posts and threads:

            Code:
            UPDATE post SET showsignature = 1 WHERE userid = x;
            Jose Amaral Rego
            Senior Member
            Last edited by Jose Amaral Rego; Sun 5 Mar '06, 4:35pm.

            Comment


            • #7
              Run this query to reset all users signatures to empty field

              Run this query to reset all users signatures to empty field:

              Code:
              UPDATE usertextfield
              SET signature = ''
              Jose Amaral Rego
              Senior Member
              Last edited by Jose Amaral Rego; Sun 26 Feb '06, 6:00pm.

              Comment


              • #8
                Run this query to remove all guest post

                Run this query to remove all guest post:

                Code:
                DELETE FROM post
                WHERE userid = 0

                Comment


                • #9
                  Run this query to remove all guest threads

                  Run this query to remove all guest threads:

                  Code:
                  DELETE FROM thread
                  WHERE postuserid = 0

                  Comment


                  • #10
                    Run this query to mass update strings in posts

                    Run this query to mass update strings in posts:

                    Code:
                    UPDATE post
                    SET pagetext = REPLACE(pagetext,'ORIGINAL','NEW')

                    Comment


                    • #11
                      future post

                      future post
                      Jose Amaral Rego
                      Senior Member
                      Last edited by Jose Amaral Rego; Thu 3 May '07, 2:23am. Reason: Clean up

                      Comment


                      • #12
                        Future Post

                        Future Post
                        Jose Amaral Rego
                        Senior Member
                        Last edited by Jose Amaral Rego; Tue 1 May '07, 2:33pm.

                        Comment


                        • #13
                          Run this query to reset uesrs style choice

                          Run this query to reset uesrs style choice:
                          where X is the ID of the style (you can get this from the Admin CP -> Styles & Template -> Style Manager -> Edit Settings -> Style: (id=X).


                          Code:
                          UPDATE user 
                          SET styleid = [COLOR="red"]X[/COLOR]

                          Comment


                          • #14
                            Run these query to delete or reset users private message count

                            Run these query to delete all private message (pm) | private message receipt (pmreceipt) | private message text (pmtext)

                            Code:
                            DELETE FROM pm
                            Code:
                            DELETE FROM pmreceipt
                            Code:
                            DELETE FROM pmtext
                            Code:
                            UPDATE user SET pmtotal = 0, pmunread = 0


                            Run these query to reset users private message count:

                            Code:
                            UPDATE user SET pmtotal=0;
                            Code:
                            UPDATE user SET pmunread=0;
                            This should set ALL current PM counts to '0' whether they have PMs or not.
                            Jose Amaral Rego
                            Senior Member
                            Last edited by Jose Amaral Rego; Wed 1 Mar '06, 1:43pm.

                            Comment


                            • #15
                              Future Post

                              Future Post
                              Jose Amaral Rego
                              Senior Member
                              Last edited by Jose Amaral Rego; Tue 1 May '07, 2:40pm.

                              Comment

                              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                              Working...
                              X