Sql query to replace broken images by certain date

[Eagle]

Hi there. The most common result is that links are broken, and images or entire pages don't load properly. Therefore I want to remove IMG BB code from all images until a certain date. As you can see in the below example between the years 2000 and 2015. Now we need to make a following SQL query to get the result but I haven't tried it yet.

UPDATE xf_post SET message = REPLACE(message,'[IMG]','') 
AND post_date BETWEEN UNIX_TIMESTAMP('2000-12-06 00:00:00') 
AND UNIX_TIMESTAMP('2015-12-08 00:00:00');

UPDATE xf_post SET message = REPLACE(message,'[/IMG]','') 
AND post_date BETWEEN UNIX_TIMESTAMP('2000-12-06 00:00:00') 
AND UNIX_TIMESTAMP('2015-12-08 00:00:00');

So what do you think about it? How does it work? Anyway. There's a whole lot of images and those pictures are crooked. So I mean, broken. Indeed, isn't there way to resolve this problem? If there is no such concrete action, I think I will have to use normal query without timestamp. Or may never use it but at least I can think about it because of the terrible images problem we've been facing. As a result of this, I personally believe that, nothing is so terrible as a broken image in a community forum.

 

[dutchbb]

You can use this add-on for checking and removing broken images (xf1).

Link Checker by AddonsLab

Add-On Information The add-on gives full control over the links you have in all your posts, allowing you to search for them quickly, check their status, modify them one by one or at once. The supported BB Code tags are URL, MEDIA and IMG...

xenforo.com

xf2 version

Link Checker for XenForo 2.x by AddonsLab

Add-On Information The add-on gives full control over the links you have in all your posts, allowing you to search for them quickly, check their status, modify them one by one or at once. Support for other content types (resources, profile...

xenforo.com

Or upload all hotlinked images first and then remove all IMG tags (only broken will be left)
https://xenforo.com/community/resources/convert-image-all.2527/ (xf1)
https://xenforo.com/community/resources/convert-image-all.6041/ (xf2)

If you have a lot of broken images, the replace add-on would be better and you can do a test run first to check what is found.
https://xenforo.com/community/resources/post-content-find-replace.1549/ (xf1)
https://xenforo.com/community/resources/post-content-find-replace.5748/ (xf2)

 

[Eagle]

Well, I have no doubt there are those that query the possibility and I've solved that problem between 2004 and 2008. Anyway. If anyone has the need to do that, just run these queries to remove all BBCode from images until a certain date. Let's just have a look at this.

1- Make sure to backup your data first.

2- Runs the sql query and displays the query result but before run, you can choose whichever date-time 2004-01-01 00:00:01, 2008-12-08 00:00:01 is the later or earlier you want like this:

UPDATE xf_post SET message = REPLACE(message,'[IMG]','') 
WHERE post_date BETWEEN UNIX_TIMESTAMP('2004-01-01 00:00:01') 
AND UNIX_TIMESTAMP('2008-12-08 00:00:01');

Meanwhile, if you have a big board, it may take time. And if you want, you can replace it with [URL] BBCode tags like this:

UPDATE xf_post SET message = REPLACE(message,'[IMG]','[URL]') 
WHERE post_date BETWEEN UNIX_TIMESTAMP('2004-01-01 00:00:01') 
AND UNIX_TIMESTAMP('2008-12-08 00:00:01');

And...

UPDATE xf_post SET message = REPLACE(message,'[/IMG]','[/URL]') 
WHERE post_date BETWEEN UNIX_TIMESTAMP('2004-01-01 00:00:01') 
AND UNIX_TIMESTAMP('2008-12-08 00:00:01');

Note that if you don't do this, you will need to update the posts you replaced. The fact is I personally don't recommend it.

3- Runs the sql query again because there might be a couple of old [img] tags:

UPDATE xf_post SET message = REPLACE(message,'[img]','') 
WHERE post_date BETWEEN UNIX_TIMESTAMP('2004-01-01 00:00:01') 
AND UNIX_TIMESTAMP('2008-12-08 00:00:01');

4- Now we need to remove the remaining [/IMG] BB Code tags. Therefore just runs the sql query:

UPDATE xf_post SET message = REPLACE(message,'[/IMG]','') 
WHERE post_date BETWEEN UNIX_TIMESTAMP('2004-01-01 00:00:01') 
AND UNIX_TIMESTAMP('2008-12-08 00:00:01');

5- Final. And runs the sql query again:

UPDATE xf_post SET message = REPLACE(message,'[/img]','') 
WHERE post_date BETWEEN UNIX_TIMESTAMP('2004-01-01 00:00:01') 
AND UNIX_TIMESTAMP('2008-12-08 00:00:01');

Done!

Before:


After:


As you can see, that problem was solved. And I prefer to use the link rather than broken images.