MySQL decrement number within tags

[Aaron]

Hello, on my SMF forum I have posts that are numbers within [embed][/embed] tags, example:

[embed]1[/embed]
[embed]2[/embed]
[embed]3[/embed]
[embed]4[/embed]

I have over 10,000 of these but need to remove some and need to decrement the numbers that are above this amount, such as 9001~9500, so that 9501 then becomes 9001 and 9502 becomes 9002 etc.

These are only in the smf_messages table of course.

Is it possible to do this in phpmyadmin with regex or would I need to download the .sql database and do it with something else?

Thanks.

[Chen Zhen]

Make sure you have a copy of the DB prior to any changes.

I'm not sure if I understand precisely what you're attempting to accomplish.

Are you trying to alter the custom embed BBCode content in every current post/topic, alter the actual topic ID's or both?

Assuming this is just for altering what's within your custom BBCode tags for: messages -> body
You can use custom server side code to manipulate MySQL data (ie. PHP) or you can use regexp on a MySQL command line.

MySQL command line (assuming the prefix is "smf_") :

Code Select Expand

UPDATE `smf_messages` SET `body` = CONCAT(
    SUBSTRING_INDEX(body, '[embed]', 1),
    '[/embed]',
    CAST(REGEXP_SUBSTR(body, '(?<=[embed])[0-9]+') AS UNSIGNED) - 500,
    '[/embed]',
    SUBSTRING_INDEX(body, '[embed]', -1)
)
WHERE body REGEXP '[embed][0-9]+[/embed]';

I have no idea if the above will work but it might give you something to test on a copy of the database to make sure it works as intended. If you can't figure it out with the above then I can delve into it further by running some tests on a localhost.
If you're using PHPMyAdmin, keep in mind that it's written in PHP & will time out for long queries unless you increase some PHP limits. Another option is to use MySQL via Bash which should avoid any script timeouts.
 

[Aaron]

Assuming this is just for altering what's within your custom BBCode tags for: messages -> body
You can use custom server side code to manipulate MySQL data (ie. PHP) or you can use regexp on a MySQL command line.

MySQL command line (assuming the prefix is "smf_") :

Code Select Expand

UPDATE `smf_messages` SET `body` = CONCAT(
    SUBSTRING_INDEX(body, '[embed]', 1),
    '[/embed]',
    CAST(REGEXP_SUBSTR(body, '(?<=[embed])[0-9]+') AS UNSIGNED) - 500,
    '[/embed]',
    SUBSTRING_INDEX(body, '[embed]', -1)
)
WHERE body REGEXP '[embed][0-9]+[/embed]';

I have no idea if the above will work but it might give you something to test on a copy of the database to make sure it works as intended. If you can't figure it out with the above then I can delve into it further by running some tests on a localhost.
If you're using PHPMyAdmin, keep in mind that it's written in PHP & will time out for long queries unless you increase some PHP limits. Another option is to use MySQL via Bash which should avoid any script timeouts.

Basically I have a .csv file with strings on each line, the numbers will pull the string from that corresponding line number:

[embed]3055[/embed] will post the string on line 3055 within the embed tag on the forum. At the moment I basically just have line breaks on those current lines in the .csv file but would like to "clean it up" and not have the line breaks, but I need to decrement the line numbers accordingly.

If it helps I have one single line (4677) that I have removed (line break), so now I want line 4678 to become 4677, and everything above to be -1. If we can figure this one out I should be able to do it for the rest of the ones I need to adjust. I tried your regexp code but it didn't match up. I made a DB backup, if its easier I could do it in notepad++ or something and then upload/overwrite.

Thanks.

[Chen Zhen]

The example I provided was supposed to decrement by -500 because that's the info you provided. If that wasn't literal you can change that number to -1 or whatever it should be.
I will run some tests to get it to work as intended but I might not have time until Friday.

[Aaron]

Okay so do I put 4678 where it says UNSIGNED, and thus -1 will start from there?

[Chen Zhen]

I tested out a bunch of MySQL syntax but it was taking too long to get the desired results so I decided to develop a small custom mod for you.

It will allow you to make custom adjustments which should give you the desired effect.
Please make sure to backup your database prior to using the modification in case something goes awry.
Let me know if there any bugs that I need to fix since I did not test it on 10000+ topics.

The custom mod is attached which you can install via your SMF packages manager.

[Aaron]

Wow, thank you for taking the time to make this. So I installed the mod and here is the config page:



How do I set it to start from 4678 or does it need to be placed in the BBCode field; "[embed]4678" ? Otherwise every post below that number will also be affected.

[Chen Zhen]

Uninstall & delete that version.

Download the attached v1.0.1 which has the added option for manually setting the starting message ID index.

[Chen Zhen]

Wait, I think I misunderstood your statement.
Do you want to be able to start at a certain number that's within the BBCode itself?

I'll have to add another setting for that.
The index settings thus far are for for the SMF message ID & have nothing to do with the regular expression that parses all the message bodies.