Well I recently moved from PHPbb3 and transferred all my forums information to vBulletin. The thing is the BBcode. I want to change for example '[center:0089]' BBCode to '[center]' globally. I can do this by executing a SQL Query via ACP. Can someone help me?
6 replies to this topic
#1
Posted 31 December 2010 - 07:40 AM
|
|
|
#2
Posted 31 December 2010 - 11:13 PM
You can use LIKE operator accompanied by a wild card. So it goes something like this (note that I don't know for sure the exact table and field name, but I hope you get the idea):
UPDATE table1 SET field1='[center]' WHERE field1 LIKE '[center%]'
#3
Posted 06 January 2011 - 09:20 AM
Sorry I'm a beginner and I really don't understand 'Wildcard', etc. I tried inputting in the SQL query replacing the center, etc and I got this error:
An error occurred while attempting to execute your query. The following information was returned.
error number: 1146
error desc: Table 'runetips.table1' doesn't exist
An error occurred while attempting to execute your query. The following information was returned.
error number: 1146
error desc: Table 'runetips.table1' doesn't exist
#4
Posted 06 January 2011 - 02:17 PM
Hi, table1 was just an example, you need to change table1 to the table you wish to update. You also need to change field1 twice to whichever field that holds "[center:xxx]"
Be sure to read the updated FAQ! || Health is achieved through the same 10,000 steps.
If a suggested code/method fails, informing us is less important than telling us why or what errors occurred.
If a suggested code/method fails, informing us is less important than telling us why or what errors occurred.
#5
Posted 06 January 2011 - 07:51 PM
Alexander beats me to it :)
Like he'd explained, you have to find the correct table that holds the BBCodes and the correct field name. Although I have a phpBB forum, but I am not familiar with its internals (just ordinary end user in this case).
On wild card. Wild cards are symbols that represent any value in respect with the type a given wild card associated to. In SQL, % is to represent a group of characters and _ to represent a single character. In my example, "WHERE field1 LIKE '[center%]'" can be read as: "when field1 containing any value started with [center and ended with ]".
Like he'd explained, you have to find the correct table that holds the BBCodes and the correct field name. Although I have a phpBB forum, but I am not familiar with its internals (just ordinary end user in this case).
On wild card. Wild cards are symbols that represent any value in respect with the type a given wild card associated to. In SQL, % is to represent a group of characters and _ to represent a single character. In my example, "WHERE field1 LIKE '[center%]'" can be read as: "when field1 containing any value started with [center and ended with ]".
#6
Posted 26 February 2011 - 07:33 AM
Still didn't work :
I think this person has had the same problem as me but the SQL query didn't work for me? very messy BBcode cleanup, need cleaner help
I think this person has had the same problem as me but the SQL query didn't work for me? very messy BBcode cleanup, need cleaner help
#7
Posted 26 February 2011 - 08:13 AM
Alright, there is something called cleaner.php which can help me but I don't know what codes to input :
<?php
if (function_exists('set_time_limit') AND get_cfg_var('safe_mode')==0)
{
@set_time_limit(0);
}
ignore_user_abort(true);
error_reporting(E_ALL & ~E_NOTICE);
/*
* @package ImpEx.tools
* @version $Revision: 1826 $
* @author Jerry Hutchings <jerry.hutchings@vbulletin.com>
* @checkedout $Name$
* @date $Date: 2007-08-21 14:03:19 -0700 (Tue, 21 Aug 2007) $
* @copyright http://www.vbulletin.com/license.html
*
*/
// BACK UP YOUR DATABASE
// Cleaner
// Swaps X for Y in the database handy for smilies that have been wrongly parsed
// BACK UP YOUR DATABASE
//
########################################
#
# Set to true / false
# $do_posts = ALL POSTS
# $do_sigs = ALL SIGNATURES
# $do_thread_titles = ALL THREAD TITLES
# $do_pm_text = ALL PM TEXTS;
# $do_pm_text_title = ALL PM TITLES;
########################################
// BACK UP YOUR DATABASE
// Set to true to run, and false when done, or remove this script (and all of impex when finished).
$active = true;
// Set true or false as to the data you want to clean
$do_posts = true;
$do_sigs = false;
$do_thread_titles = false;
$do_pm_text = false;
$do_pm_text_title = false;
########################################
[B]# Replace 'Find me' and "Replace with me" with the strings you want replaced i.e
# "<b>" => "[b]"
# "<blockquote>" => "[quote]"
# add as many elements to the array as needed
// BACK UP YOUR DATABASE
$replacer = array(
"" => "",
"" => "",
"" => "",
"" => "",
"" => ""
);
// BACK UP YOUR DATABASE[/B]
################################################################################
# CHANGE NOTHING BELOW THIS LINE # CHANGE NOTHING BELOW THIS LINE # CHANGE NOTHI
################################################################################
if (!$active)
{
exit('Not active');
}
define('IDIR', (($getcwd = getcwd()) ? $getcwd : '.'));
include_once('./../db_mysql.php');
// BACK UP YOUR DATABASE
require_once ('./../ImpExConfig.php');
$targetserver = $impexconfig['target']['server'];
$targetuser = $impexconfig['target']['user'];
$targetpassword = $impexconfig['target']['password'];
$targetdatabase = $impexconfig['target']['database'];
$targettableprefix = $impexconfig['target']['tableprefix'];
$targettablecharset = $impexconfig['target']['charset'];
$Db_target = new DB_Sql_vb_impex();
$Db_target->appname = 'vBulletin:ImpEx Target';
$Db_target->appshortname = 'vBulletin:ImpEx Target';
$Db_target->type = 'mysql';
$Db_target->database = $targetdatabase;
$Db_target->connect($targetserver, $targetuser, $targetpassword, 0, $targettablecharset);
$Db_target->select_db($targetdatabase);
// BACK UP YOUR DATABASE
# Posts
if($do_posts)
{
$posts = $Db_target->query("SELECT postid, pagetext, title FROM " . $targettableprefix . "post");
while ($post = $Db_target->fetch_array($posts))
{
$text = str_replace(array_keys($replacer), $replacer, $post['pagetext']);
$title_text = str_replace(array_keys($replacer), $replacer, $post['title']);
#$text = preg_replace('##siU', '', $text);
#$title_text = preg_replace('##siU', '', $text);
$Db_target->query("UPDATE " . $targettableprefix . "post SET pagetext='" . addslashes($text) . "', title='" . addslashes($title_text) . "' WHERE postid='" . $post['postid'] . "'");
echo "<br /><b>Post done -></b><i> " . $post['postid'] . "</i>";
}
}
# Signatures
if($do_sigs)
{
$users = $Db_target->query("SELECT userid, signature FROM " . $targettableprefix . "usertextfield");
while ($user = $Db_target->fetch_array($users))
{
$text = str_replace(array_keys($replacer), $replacer, $user['signature']);
#$text = preg_replace('##siU', '', $text);
$Db_target->query("UPDATE " . $targettableprefix . "usertextfield SET signature='" . addslashes($text) . "' WHERE userid='" . $user['userid'] . "'");
echo "<br /><b>Signature done -></b><i> " . $user['userid'] . "</i>";
}
}
# Thread titles
if($do_thread_titles)
{
$users = $Db_target->query("SELECT threadid, title FROM " . $targettableprefix . "thread");
while ($user = $Db_target->fetch_array($users))
{
$text = str_replace(array_keys($replacer), $replacer, $user['title']);
#$text = preg_replace('##siU', '', $text);
$Db_target->query("UPDATE " . $targettableprefix . "thread SET title='" . addslashes($text) . "' WHERE threadid='" . $user['threadid'] . "'");
echo "<br /><b>Thread done -></b><i> " . $user['threadid'] . "</i> :: <b>{$text}</b>";
}
}
# PM text
if($do_pm_text)
{
$pms = $Db_target->query("SELECT pmtextid, message, title FROM " . $targettableprefix . "pmtext");
while ($pm = $Db_target->fetch_array($pms))
{
$text = str_replace(array_keys($replacer), $replacer, $pm['message']);
$title = str_replace(array_keys($replacer), $replacer, $pm['title']);
#$text = preg_replace('##siU', '', $text);
$Db_target->query("UPDATE " . $targettableprefix . "pmtext SET message='" . addslashes($text) . "', title='" . addslashes($title) . "' WHERE pmtextid='" . $pm['pmtextid'] . "'");
echo "<br /><b>Pm done -></b><i> " . $pm['pmtextid'] . "</i> :: <b>{$text}</b>";
}
}
// BACK UP YOUR DATABASE
// You shouldn't be reading down here !!
?>
1 user(s) are reading this topic
0 members, 1 guests, 0 anonymous users


Sign In
Create Account


Back to top









