Search and replace wildcard question

CMS & WordPress
1

Hi all,

Apologies if this has been mentioned elsewhere, however I have a query regarding wildcards.

I have setup another Wordpress installation with a new responsive theme. I have imported all of the posts, and I have noticed that one of the authors has ‘positioned’ elements by using code similar to:

<p><a href="http://www.rimf.me.uk/wp-content/uploads/2011/11/Blue-Break-03.jpg" ><img src="http://www.owlsalive.com/wp-content/uploads/2011/09/BLANK-SPACE.jpg" alt="" width="440" height="4" /></a></p>

The code always starts the same and finishes the same.

My question is, how do I search the database and remove all the lines with BLANK-SPACE.jpg in them? Any info is appreciated.

Kind regards,
Richard

2

it would be something along the lines of…

DELETE
  FROM thetable
 WHERE thecolumn LIKE '%BLANK-SPACE.jpg%'

assuming, of course, that when you say “remove all the lines” you’re talking about entire rows where one of the columns contains the html

3

Hi,

The width and height on the blank-space.jpg always differ - will your code work? Also, when I say all lines, this is misleading.

The line always starts <a href and finishes </a> - the image is always in the middle with different attributes.

Richard

4

I also think you don’t want to delete those posts, you just want to remove the HTML markup from the posts, is that correct? As if that is the case, it “may” be easier to do this via PHP than within MySQL

5

No I don’t want to delete the posts, just the HTML that begins with <p><a href and ends in </a></p> that contains BLANK-SPACE.JPG somewhere in the middle.

6

Okay, so something along the lines of this would work (if you don’t have any PHP knowledge, I can write up a more workable example later tonight)

// connect to your database
// run query SELECT POST_ID, CONTENT FROM TABLE_NAME WHERE COLUMN_NAME LIKE '%BLANK-SPACE.jpg%'

// Loop through each record and run the POST CONTENT through the following code
$updatedContent = preg_replace('/\\\\<a href="(.*)"(.*)\\\\>\\\\<img src="(.*)BLANK-SPACE.jpg"(.*)\\\\/\\\\>\\\\<\\\\/a\\\\>/', '', '$row['CONTENT']');

// run query UPDATE TABLE_NAME SET COLUMN_NAME = '$updatedContent' WHERE post_id = $row['POST_ID']
7

Unfortunately my PHP knowledge is very limited. If you could supply it as easily as poss, I’d appreciate it :slight_smile:

Cheers

8

Hopefully this goes without saying, but BACKUP YOUR DATABASE before using this. :slight_smile:

Then paste the following in a file named cleanup.php

<?php
	require_once('wp-config.php');

	$pdo = new PDO('mysql:host='.$DB_HOST.';dbname='.$DB_NAME.';charset=UTF-8', $DB_USER, $DB_PASSWORD);

	$postsAffectedQuery = $pdo->prepare(sprintf("SELECT ID, post_content FROM %sposts WHERE post_content LIKE ?", $table_prefix));
	$postsAffectedQuery->execute('%BLANK-SPACE.jpg%');

	$postsAffected = $postsAffectedQuery->fetchAll();
	foreach ($postsAffected as $post)
	{
		echo sprintf('Updating POST ID = %s ... ', $post['ID']);
		$updatedContent = preg_replace('/\\\\<a href="(.*)"(.*)\\\\>\\\\<img src="(.*)BLANK-SPACE.jpg"(.*)\\\\/\\\\>\\\\<\\\\/a\\\\>/', '', $post['post_content']);

		try
		{
			$updatePostQuery = $pdo->prepare(sprintf("UPDATE %sposts SET post_content = ? WHERE ID = ?", $table_prefix));
			$updatePostQuery->execute($updatedContent, $post['ID']);
			echo 'Success!<br />' . PHP_EOL;
		}
		catch(PDOException $e)
		{
			echo sprintf('Failed with %s<br />%s', $e->getMessage(), PHP_EOL);
		}
	}
?>

Upload it to the same folder your wp-config.php file exists in.
Then open that file in your browser using www.mydomain.com/cleanup.php (or wherever it is installed, change the url accordingly to your website)

9

Sent you a PM mate.

10

Yep, got your PM, not sure what is happening so I’ve added more debugging to the script, please update it and run it again.

<?php
    require_once('wp-config.php');

    $pdo = null;
    $postsAffectedQuery = null;
    try
    {
        $pdo = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset=UTF-8', DB_USER, DB_PASSWORD);
        $postsAffectedQuery = $pdo->prepare(sprintf("SELECT ID, post_content FROM %sposts WHERE post_content LIKE ?", $table_prefix));
        $postsAffectedQuery->execute('%BLANK-SPACE.jpg%');
    }
    catch(PDOException $e)
    {
        echo sprintf('Failed to get affected posts due to %s<br />%s', $e->getMessage(), PHP_EOL);
    }

    $postsAffected = $postsAffectedQuery->fetchAll();
    foreach ($postsAffected as $post)
    {
        echo sprintf('Updating POST ID = %s ... ', $post['ID']);
        $updatedContent = preg_replace('/\\\\<a href="(.*)"(.*)\\\\>\\\\<img src="(.*)BLANK-SPACE.jpg"(.*)\\\\/\\\\>\\\\<\\\\/a\\\\>/', '', $post['post_content']);

        try
        {
            $updatePostQuery = $pdo->prepare(sprintf("UPDATE %sposts SET post_content = ? WHERE ID = ?", $table_prefix));
            $updatePostQuery->execute($updatedContent, $post['ID']);
            echo 'Success!<br />' . PHP_EOL;
        }
        catch(PDOException $e)
        {
            echo sprintf('Failed with %s<br />%s', $e->getMessage(), PHP_EOL);
        }
    }
?>
11

New code based on our PMs (since we were dealing with sensitive data)

<?php
    require_once('wp-config.php');

    $pdo = null;
    $postsAffectedQuery = null;
    try
    {
        $pdo = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset=UTF-8', DB_USER, DB_PASSWORD);
        $postsAffectedQuery = $pdo->prepare(sprintf("SELECT ID, post_content FROM %sposts WHERE post_content LIKE ?", $table_prefix));
        $postsAffectedQuery->execute(array('%BLANK-SPACE.jpg%'));
    }
    catch(PDOException $e)
    {
        echo sprintf('Failed to get affected posts due to %s<br />%s', $e->getMessage(), PHP_EOL);
    }

    $postsAffected = $postsAffectedQuery->fetchAll();
    foreach ($postsAffected as $post)
    {
        echo sprintf('Updating POST ID = %s ... ', $post['ID']);
        $updatedContent = preg_replace('/\\\\<a href="(.*)"(.*)\\\\>\\\\<img src="(.*)BLANK-SPACE.jpg"(.*)\\\\/\\\\>\\\\<\\\\/a\\\\>/', '', $post['post_content']);

        try
        {
            $updatePostQuery = $pdo->prepare(sprintf("UPDATE %sposts SET post_content = ? WHERE ID = ?", $table_prefix));
            $updatePostQuery->execute(array($updatedContent, $post['ID']));
            echo 'Success!<br />' . PHP_EOL;
        }
        catch(PDOException $e)
        {
            echo sprintf('Failed with %s<br />%s', $e->getMessage(), PHP_EOL);
        }
    }
?>
12

Excellent, replied via PM

13

Worked like a treat. Thank-you very much.