Sql query to replace broken images by certain date

Eagle

Well-known member
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.

Code:
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');

Code:
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.
 
You can use this add-on for checking and removing broken images (xf1).

xf2 version

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

Code:
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:

Code:
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...

Code:
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:

Code:
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:

Code:
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:

Code:
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:
188269

After:
188266

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