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:
Some author name (http://www.somesite.com/entrez?Db=pubmed&Cmd=Search&Term=%22Guo%20Y%22%5BAuthor%5D&itool=EntrezSystem2.PEntrez.Pubmed.Pubmed_ResultsP anel.Pubmed_DiscoveryPanel.Pubmed_RVAbstractPlus)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]

/**
* 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?

$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:


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

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]

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_ResultsP anel.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_ResultsP anel.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_ResultsP anel.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_ResultsP anel.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_ResultsP anel.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_ResultsP anel.Pubmed_DiscoveryPanel.Pubmed_RVAbstractPlus)I t 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 1309800396 at 1309800396 ---------------

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

[Alfa1]

If you want, try it again, and set $showlinks to false (4th argument to strip_bbcode). That should do the trick.
Where? Im not seeing showlinks. I used this:
<?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');

[Adrian Schneider]

Sorry it's from the strip_bbcode() documentation above.

You want

<?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'], false, false, false)), // third false disables links as (URL)
'linkslink',
"WHERE linkid = $link[linkid]"
));
}

die('Done');

[Alfa1]

Übercool! Will try that.

[Adrian Schneider]

Let me know how it goes. :)