How to remove links from a database field?

[Alfa1]

I have links and Downloads Manager installed. In 1100 entries in there are dead links in the description field, due to copy -pasting of text.
I need to remove all links from the that description field, so that only plain text remains in that field. The database table is vb_linkslink and the field is linkdesc

Links look like this:

[url="http://www.somesite.com/entrez?Db=pubmed&Cmd=Search&Term=%22Guo%20Y%22%5BAuthor%5D&itool=EntrezSystem2.PEntrez.Pubmed.Pubmed_ResultsPanel.Pubmed_DiscoveryPanel.Pubmed_RVAbstractPlus"][color=black]Some author name[/color][/url]

I would like to remove the link and keep the Author name.

Does anyone know what query I could use to do this?

[BirdOPrey5]

I don't think a query would work on something like this. You'll need a custom script and need to use some sort of regular expression to find matches and remove them.

[Adrian Schneider]

PHP Code:

/**
* Strips away bbcode from a given string, leaving plain text
*
* @param    string    Text to be stripped of bbcode tags
* @param    boolean    If true, strip away quote tags AND their contents
* @param    boolean    If true, use the fast-and-dirty method rather than the shiny and nice method
* @param    boolean    If true, display the url of the link in parenthesis after the link text
* @param    boolean    If true, strip away img/video tags and their contents
* @param    boolean    If true, keep [quote] tags. Useful for API.
*
* @return    string
*/
function strip_bbcode($message, $stripquotes = false, $fast_and_dirty = false, $showlinks = true, $stripimg = false, $keepquotetags = false) 


So, Something like this?

PHP Code:

$result = $vbulletin->db->query_write("
    SELECT linkid
         , linkdesc
      FROM " . TABLE_PREFIX . "linkslink
");

while ($link = $vbulletin->db->fetch_array($result)) {    
    $vbulletin->db->query_write(fetch_query_sql(
        array('linkdesc' => strip_bbcode($link['linkdesc'])),
        'linkslink',
        "WHERE linkid = $link[linkid]"
    ));
} 


[Alfa1]

Where should that code go to execute it?

[kh99]

For things like that I usually create a plugin using hook misc_start and code like this:

PHP Code:

if ($_REQUEST['do'] == 'fixlinks')
{
    // code here

    exit("Done fixlinks.");
} 


then go to page misc.php?do=fixlinks to run it, and disable or delete it when I'm done.

[Adrian Schneider]

New PHP file works too...

PHP Code:

<?php

error_reporting(E_ALL ^ E_NOTICE ^ 8192);

require('./global.php');

$result = $vbulletin->db->query_write("
    SELECT linkid
         , linkdesc
      FROM " . TABLE_PREFIX . "linkslink
");

while ($link = $vbulletin->db->fetch_array($result)) {    
    $vbulletin->db->query_write(fetch_query_sql(
        array('linkdesc' => strip_bbcode($link['linkdesc'])),
        'linkslink',
        "WHERE linkid = $link[linkid]"
    ));
}  

die('Done');

[kh99]

Originally Posted by Adrian Schneider

New PHP file works too...

Nice, that is pretty simple. For some reason I've been under the impression that a few more things would need to be defined for that to work.

[Adrian Schneider]

Nope, those are the only two required lines (error_reporting, require global.php)

The rest are good practice but don't matter for quick one-off scripts.

[Alfa1]

Unfortunately that didnt work as planned.

Instead of hotlinked words, I now have this:

Eur J Pharmacol. (javascript:AL_get(this, 'jour', 'Eur J Pharmacol.')); 1998 Jan 12;341(2-3):131-4
 
Schultz DM  (http://www.ncbi.nlm.nih.gov/sites/entrez?Db=pubmed&Cmd=Search&Term=%22Schultz%20DM%22%5BAuthor%5D&itool=EntrezSystem2.PEntrez.Pubmed.Pubmed_ResultsPanel.Pubmed_DiscoveryPanel.Pubmed_RVAbstractPlus),  Prescher JA  (http://www.ncbi.nlm.nih.gov/sites/entrez?Db=pubmed&Cmd=Search&Term=%22Prescher%20JA%22%5BAuthor%5D&itool=EntrezSystem2.PEntrez.Pubmed.Pubmed_ResultsPanel.Pubmed_DiscoveryPanel.Pubmed_RVAbstractPlus),  Kidd S  (http://www.ncbi.nlm.nih.gov/sites/entrez?Db=pubmed&Cmd=Search&Term=%22Kidd%20S%22%5BAuthor%5D&itool=EntrezSystem2.PEntrez.Pubmed.Pubmed_ResultsPanel.Pubmed_DiscoveryPanel.Pubmed_RVAbstractPlus),  Marona-Lewicka D  (http://www.ncbi.nlm.nih.gov/sites/entrez?Db=pubmed&Cmd=Search&Term=%22Marona-Lewicka%20D%22%5BAuthor%5D&itool=EntrezSystem2.PEntrez.Pubmed.Pubmed_ResultsPanel.Pubmed_DiscoveryPanel.Pubmed_RVAbstractPlus),  Nichols DE  (http://www.ncbi.nlm.nih.gov/sites/entrez?Db=pubmed&Cmd=Search&Term=%22Nichols%20DE%22%5BAuthor%5D&itool=EntrezSystem2.PEntrez.Pubmed.Pubmed_ResultsPanel.Pubmed_DiscoveryPanel.Pubmed_RVAbstractPlus),  Monte A  (http://www.ncbi.nlm.nih.gov/sites/entrez?Db=pubmed&Cmd=Search&Term=%22Monte%20A%22%5BAuthor%5D&itool=EntrezSystem2.PEntrez.Pubmed.Pubmed_ResultsPanel.Pubmed_DiscoveryPanel.Pubmed_RVAbstractPlus)

It should read:

Eur J Pharmacol.  1998 Jan 12;341(2-3):131-4
 
Schultz DM,  Prescher JA,  Kidd S,  Marona-Lewicka D,  Nichols DE,  Monte A

Please let me know ASAP if I should revert the database table. I have 90k users adding stuff.

[Adrian Schneider]

I'd revert it for now, yeah.

If you want to send me a few example rows (10-50 or so) I can get it working for you, but will be difficult without any real data.

--------------- Added [DATE]1309800396[/DATE] at [TIME]1309800396[/TIME] ---------------

If you want, try it again, and set $showlinks to false (4th argument to strip_bbcode). That should do the trick.