SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    England
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question Search and replace wildcard question

    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:

    Code:
    <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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    it would be something along the lines of...
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    England
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    it would be something along the lines of...
    Code:
    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
    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. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rawkinrich100 View Post
    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
    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
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  5. #5
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    England
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    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
    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. #6
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    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)
    PHP Code:
    // 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'] 
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  7. #7
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    England
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately my PHP knowledge is very limited. If you could supply it as easily as poss, I'd appreciate it

    Cheers

  8. #8
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Hopefully this goes without saying, but BACKUP YOUR DATABASE before using this.

    Then paste the following in a file named cleanup.php
    PHP Code:
    <?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)
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  9. #9
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    England
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    Hopefully this goes without saying, but BACKUP YOUR DATABASE before using this.

    Then paste the following in a file named cleanup.php
    PHP Code:
    <?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)
    Sent you a PM mate.

  10. #10
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    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 Code:
    <?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_USERDB_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);
            }
        }
    ?>
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  11. #11
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    New code based on our PMs (since we were dealing with sensitive data)
    PHP Code:
    <?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_USERDB_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);
            }
        }
    ?>
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  12. #12
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    England
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Excellent, replied via PM

  13. #13
    SitePoint Addict
    Join Date
    Aug 2003
    Location
    England
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Worked like a treat. Thank-you very much.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •