Jump to content

Need a simple SQL Query

- - - - -

  • Please log in to reply
6 replies to this topic

#1
SuperSponge

SuperSponge

    Learning Programmer

  • Members
  • PipPipPip
  • 35 posts
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?

#2
LuthfiHakim

LuthfiHakim

    Programming God

  • Members
  • PipPipPipPipPipPipPip
  • 765 posts
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
SuperSponge

SuperSponge

    Learning Programmer

  • Members
  • PipPipPip
  • 35 posts
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

#4
Alexander

Alexander

    It's Science!

  • Moderators
  • 4,124 posts
  • Location:Vancouver, Eh! Cleverness: 200
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.

#5
LuthfiHakim

LuthfiHakim

    Programming God

  • Members
  • PipPipPipPipPipPipPip
  • 765 posts
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 ]".

#6
SuperSponge

SuperSponge

    Learning Programmer

  • Members
  • PipPipPip
  • 35 posts
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

#7
SuperSponge

SuperSponge

    Learning Programmer

  • Members
  • PipPipPip
  • 35 posts
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