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

Question

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

So that tag is in some strings stored in database? Replace using regexp, then? https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql — Torbjörn Stabo, Feb 15 '19 at 16:12

score 1 · Answer 1 · answered Feb 15 '19 at 16:20

1

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)
}
?>

answered Feb 15 '19 at 16:20

Bastian Jakobsen

132
6

Small note : this will not work if gallery_id= isn't always the second word. – Bastian Jakobsen Feb 15 '19 at 16:22
Thanks for the reply, I do have a few thousand entries that need to be changed! Will this change the gallery_id or will that stay the same? And Also is this the line i change the testname to the new gallery name? ***$input_lines = "[testname ". $output_array[1][0]."]";*** – Rhydz97 Feb 18 '19 at 12:19
Yes, simply change testname into what you want. The ID will stay the same. A few thousand entries wont be an issue. You might want to to set "set_time_limit(2000);" at the start so that your PHP doesn't timeout. But if you got a slow mysql for a few thousand entries it might take 60-90 seconds (and apache will make a timeout at 30 if you don't set the set_time_limit). – Bastian Jakobsen Feb 18 '19 at 13:52
How would I get the data from the table i've got already for the input_lines array? is that from doing a mysqldump or is that from a csv file? Sorry for the stupid questions, i'm pretty new at php and not very experienced with mysql! – Rhydz97 Feb 18 '19 at 14:49
@Rhydz97 Fetching data from database would be by running PHP and issuing a SELECT query to the database. I think that's a question on its own here :). (Search for "php database select query" or similar on SO or Google, you'll get all the code examples you'll likely ever need) – Torbjörn Stabo Feb 18 '19 at 15:21
@Rhydz97 You **could** - probably - dump the database, load the dump in a text editor and do the replacements there. (You'll probably still use regexp though) And then use the modified dump to recreate the database. But I wouldn't recommend it. – Torbjörn Stabo Feb 18 '19 at 15:24

Torbjörn Stabo · Answer 2 · 2019-02-18T14:42:15.963

0

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 ;)

edited Feb 18 '19 at 14:42

answered Feb 15 '19 at 18:59

Torbjörn Stabo

769
4
7

Thanks for the reply, will this keep the id the same after replacing or will this change the id as well? I'd need it to keep the id as it is if it's possible! – Rhydz97 Feb 18 '19 at 12:06
@Rhydz97 Yes, as per my not so clear example above: **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** becomes **my text before [foogallery gallery_id="14629"] my text [foogallery gallery_id="1"] my text after**. Which I take is what you wanted? – Torbjörn Stabo Feb 18 '19 at 13:59
Yes that's what I need it to do! I'm running on phpmyadmin and i'm getting an error for REGEXP_REPLACE saying it doesn't exist, is this query not available in phpmyadmin or do we need to update it to a more up to date version? – Rhydz97 Feb 18 '19 at 14:44
@Rhydz97 Ok, you're probably running an older version of MySQL then(can you tell which version?). Let me check. – Torbjörn Stabo Feb 18 '19 at 14:59
@Rhydz97 No luck, REGEXP_REPLACE() requires MySQL >= 8.0.12 or MariaDB >= 10.0.5. Otherwise you need to install extra packages for the database server to add support for it. Which I take it isn't an option for you, phpmyadmin sounds like shared hosting? – Torbjörn Stabo Feb 18 '19 at 15:13

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

2 Answers2