CODEWITHSUNDEEP
phpmysql
Rhydz97
Rhydz97

Reputation: 21

How to replace text in square brackets for a database change?

We're changing gallery software from shareprints to foogallery and need to change the id's that they use in blog posts. The post content now has short hand code of:

[shareprints gallery_id="14629" gallerytype = 0, etc]

And we need to change it to:

[foogallery gallery_id="14629"]

So I need to change the name of the gallery and then get rid of the rest of the text in the square brackets without changing the text that is outside of the square brackets.

Is there anyway of doing this with MySQL or does anyone have any other suggestions on how to do this?

Thanks

Upvotes: 2

Views: 133

Answers (2)

Torbjörn Stabo
Torbjörn Stabo

Reputation: 779

Depends a bit on your database server, what it supports, but here's another take using nothing but the database:

> SELECT REGEXP_REPLACE('my text before [shareprints some_othertype gallery_id="14629" gallerytype = 0, etc] my text [shareprints some_othertype gallery_id="1" gallerytype = 0, etc] my text after',
                        '\\[shareprints [^\\]]*gallery_id="([0-9]+)"[^\\]]*\\]',
                        '\[foogallery gallery_id="\\1"]');
+--------------------------------------------------------------------------------------------------+
| REGEXP_REPLACE(...)                                                                              |
+--------------------------------------------------------------------------------------------------+
| my text before [foogallery gallery_id="14629"] my text [foogallery gallery_id="1"] my text after |
+--------------------------------------------------------------------------------------------------+

Some care is required to not make the regexp too greedy, which is why [^\\]]* is used (instead of .*) to catch anything not needed within the tag(and not outside it). Backreferences(parentheses) are used for the things that should be transferred, and then a corresponding \N (N for number) in the replacement pattern.

Then when you're happy with the regexp you can use

UPDATE mytable
SET mycol=REGEXP_REPLACE(mycol,'myregexp','myreplacement')
WHERE <my condition in case I do not want to update all rows>

..or similar to keep the database busy for some time ;)

Upvotes: 0

Bastian Jakobsen
Bastian Jakobsen

Reputation: 132

The easiest way is simply to script and update everything. Unless you have millions of entries this will be the easier way. Just connect with MYSQL, get all rows into an array, then loop and update with each iteration.

<?php
// Get all results in mysql sample data underneath
$input_lines = array(
    array(
        "id" => 1, 
        "line"=>'[shareprints gallery_id="14629" gallerytype = 0, etc]'
    )
);
// Loop each data 
foreach($input_lines as $l) {
    // Replace with new format
    preg_match_all('/\[[a-zA-Z]*\ ([^\ ]*)[^]]*/', $l['line'], $output_array);
    $input_lines = "[testname ". $output_array[1][0]."]"; // Change with new name here
    // var_dump($input_lines);
    // Update mysql with ID (l['id'] here)
}
?>

Upvotes: 1

Related Questions