database - I need to find posts that contain YouTube links without http: or https: prefix and add http: to all of them

admin管理员组

文章数量:1333451

Is it possible to do this via WordPress Dashboard or PhpMyAdmin at once?

For example:

Find this: 
youtube/watch?v=ycbjSLCaMXw

Replace to this:

There is hundreds of them.

Edit: I found this line on PhpMyAdmin but I don't know how to use it.

And there is no "operator" dropdown in "Find and Replace" screen.

Is it possible to do this via WordPress Dashboard or PhpMyAdmin at once?

For example:

Find this: 
youtube/watch?v=ycbjSLCaMXw

Replace to this:
http://youtube/watch?v=ycbjSLCaMXw

There is hundreds of them.

Edit: I found this line on PhpMyAdmin but I don't know how to use it.

And there is no "operator" dropdown in "Find and Replace" screen.

• database
• phpmyadmin

Share Improve this question edited Jun 14, 2020 at 8:42 Numan Çebi asked Jun 13, 2020 at 13:49 Numan ÇebiNuman Çebi 333 bronze badges 2

• WP CLI search replace would be the best option here, but is this just the post content field? Or are you using custom page builders or plugins like ACF that store things in post meta? – Tom J Nowell ♦ Commented Jun 13, 2020 at 14:02
• @TomJNowell this is just for post content field. – Numan Çebi Commented Jun 13, 2020 at 19:40

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

I think you'll have to do a little PHP work and SQL-ing. As you might know, WordPress stores data in a database---and that database has multiple tables, each of which has several fields. You first have to know in what specific field(s) in which specific table(s) that youtube content resides. When you know where it is, then you can replace it. You can't search for content if you don't have some general idea where it is. For example, suppose you know that these URLs are in the post_content field of the posts table. That's where you'd look.

After you figure out where you want to look, then you can come up with a plan on how to do the work. I'll continue with my example where I am looking in the post_content field of the posts table. My plan includes ensuring that all my youtube strings are prefixed the same, so I'll have to do a search and replace over all records that contain a youtube/ string in their post_content field.

For each record, I will first remove all www prefixes from strings that contain //www.youtube/. This will make all my youtube strings look something like this: ...//youtube/.... Then, I will do the same thing on https:// prefixes by replacing them with http:// on all http://youtube/ strings. This will ensure that the only youtube strings that exist look something like this: http://youtube/...; and youtube/....

Next, my plan would include prefixing all youtube strings with http://. This will result in youtube strings looking something like this: http://http://youtube/...; and http://youtube/.... Not a big deal, because the next part of my plan calls for changing all the double http:// prefixes to just a single http:// making all my youtube strings look like this: http://youtube/.... Tada! (That's what you want, right?)

So, here's my "search-and-destroy" code for that. (I have described what each line does with a comment above that line of code so you can cross-reference it with my explanation above.)

// Get the object that can do WordPress database searches.
global $wpdb;

// Select records from the "posts" table where its "post_content" field contains "youtube/", and return all columns (*) for each of those records.
$results = $wpdb->get_results("SELECT * FROM {$wpdb->posts} WHERE post_content LIKE '%youtube/%' ");

// Iterate over each $record of the results
foreach($results as $record) {

    // Grab the string saved in post_content and put it in a variable we can manipulate.
    $str = $record->post_content;

    // Remove "www" from youtube strings
    $str = str_replace('www.youtube/', 'youtube/', $str);

    // Replace "https://" with "http://"
    $str = str_replace('https://youtube/', 'http://youtube/', $str);

    // Prepend the "http://"
    $str = str_replace('youtube/', 'http://youtube/', $str);

    // Replace the double "http://http://" with the single "http://"
    $str = str_replace('http://http://youtube/', 'http://youtube/', $str);

    // Save the manipulated string back into the database with the pretty youtube strings.
    $record->post_content = $str;
    wp_update_post(array(
        'ID'           => $record->ID,
        'post_content' => $str,
    ));

}

A final thought: if you put this code in your functions.php file of your template, it will run every time the file is included in the WordPress process, which is not ideal. So you should run the above code block once (by loading a page) then immediately remove (delete) the code block.

But, wouldn't it be nice to be able to just leave this code in your template somewhere and run it, say, once every 24-hours until the end of time without having to think about it (just in case more youtube strings need formatting in the future)? You could do this by putting the above code inside a hook which would initially run the code, then set a timer and run it again upon that timer's expiration? After all, it is expensive to query the database. If I were doing this, I would use the after_setup_theme hook and run the code once every 24-hours by setting a transient. Check this out:

// Whenever WordPress fires the "after_setup_theme" hook (which is pretty much on every page load), execute the block of code.
add_action('after_setup_theme', function() {

    // Only run our "search-and-destory" code if our transient expired
    if( !get_transient('_numanCebi_timer') ) {

        // Our "search-and-destroy" code goes here ...

        // Set a transient to expire after 24-hours so this code block is not entered again for a day.
        set_transient( '_numanCebi_timer', TRUE, DAY_IN_SECONDS );

    }

});

Update (6/14): The edits to your question are not WordPress related, and therefore are not germane in this forum; do not expect the updated question related to PhpMyAdmin to be answered here. Looks like what you found is a search tool in the PhpMyAdmin graphical user interface. As shown in your first screenshot, use % as a wildcard to search the records (which your screenshot shows you don't have in your youtube/ query). Next, there is no "operator" in a search and replace ("search-and-destroy") operation. It simply finds the string specified and replaces it with another specified string.

本文标签：