Call for SQL experts

Photobucket has screwed the world over, pushing people to paid subscriptions. This means many linked images no longer work.

To fix so many broken threads, the process would be something like:

Member X downloads their Photobucket images
Member X uploads those images to DO THE TON using the same Photobucket file name
DO THE TON executes a database update changing the IMG links for Member X to point to DO THE TON hosting

For example, an old message of mine where the IMG link points to:

http://i13.photobucket.com/albums/a275/CB550F/DO%20THE%20TON%20Alabama%202010/ZeeFotoDragon.jpg

Would need to be changed to something like:

http://www.dotheton.com/gallery/ZeeFotoDragon.jpg

The "i13" in front of "photobucket" changes, so we'd need to wildcard that as $$$.photobucket.com in a search string to replace those all with "www.dotheton.com" which would be simple enough. But the subsequent variable "/albums/a275/CB550F" in front of the filename will be a challenge to remove.

In a nutshell, we need to isolate the filename out of the string and prefix it with "http://www.dotheton.com/gallery/"

Thoughts? This is the text from the body field of the messages table as a simple example. In many cases there is more text etc following the image links, and multiple image links in a single message.

"{b}"Whoooohooo! First pro pics of my Dragon runs are up.
/>
{img width=762 height=1024}http://i13.photobucket.com/albums/a275/CB550F/DO%20THE%20TON%20Alabama%202010/ZeeFotoDragon.jpg{/img}"{/b}"

Sounds like something I can do.

What environment are we working in? All MySql?

Possible I could get a sandbox table and limited login to play with existing data?

All MySQL - I'll try to create a copy you can have access to or I can give you a link to the full DB backup which is 1.7GB. Downloads fast from the site.

That sucks that it's still a lot of manual work caused by photobucket before you can even execute the query on DTT.

Sent from my SAMSUNG-SM-G930A using DO THE TON mobile app

I have been meaning to donate to the site for a while if you guys pull this off i will have to get off my ass and chip in.

Not sure if it's feasible for you to reach out to other Admins of forums but another site I frequent, oilburners.net seems to be working through this BS.

It can be done by selecting the filename with SUBSTRING, then CONCAT it with the new host and path.

I created a SQL fiddle where you can see a working example:

http://www.sqlfiddle.com/#!9/bdb135/2

It needs to be modified to fit the forum schemas structure of course.


Edit: just noticed that there is more than the link in the field, so obviously that needs to be accounted for also. I'm actually starting to think it might be easier to take a dump of the database and then fix the links with something like sed and then restore it again. If i can have a sample dump of the db, I'll be willing to give it a try also.

I ran into the same limitations with the SQL functionality. I'm writing a quick program in C# and RegEx to perform the replacements.

Sonreir™ said:

I ran into the same limitations with the SQL functionality. I'm writing a quick program in C# and RegEx to perform the replacements.

Click to expand...

Hmmm, what was it you always say about using RegEx's????

I also say the same thing about listening to my own advice... lol

Good that youre finding solutions to boycott those greedy bastards. Gotta find time and broadband to download my photobucket library and terminate the account. Hopefully they just ran themselves out of business.

I can try to help as well... I dont need a whole dump of the table... but a handful of examples would be nice. I saw the one you posted... but a larger smathering would go a long way.

OK... I suck at Regex... anyone help me out?

I need something to pull out the contents of an image tag, ignoring case and any attributes the image tag might have.

Sonreir™ said:

OK... I suck at Regex... anyone help me out?

I need something to pull out the contents of an image tag, ignoring case and any attributes the image tag might have.

Click to expand...

pm me some examples

This regex string (which wont work in Javascript due to the use of positive lookbehinds) works in c#

tested with this url string

output:

We need to isolate it to only Photobucket image links as well.

I was thinking there are likely only a few hundred / thousand links. Not millions. If we ran a select to grab the preceding X characters and trailing Y characters (leaving lots of room for error) and grabbed all the Photobucket IMG links, then I could clean them up a bit, get it down to the full list of distinct clean Photobucket IMG links (with associated DTT member number from each message).

Can the position of 'photobucket' be identified in a message? I used to do this in Excel, where I located the position of the character I wanted, then selected the preceding / trailing X/Y characters from that position from within the string.

Then I can use them to do specific select / replace statements in the database as members move their photos.

I've got an initial SQL query to grab just the message bodies that contain an image tag with photobucket following it. Rich, Kelly, and I have taken the discussion to messenger and we're tossing around some ideas. I think we have a working Regex solution which I'm going to test tonight. I think Kelly was going to give things a shot with a Perl script, too.

Yep, the download grab "should" be relatively straightforward. I'll do some testing tonight.
It might make some of the Regex tricks unnecessary

My head hurts now...I understand nothing any of you have said except fixing broken photobucket images

If we can get an output table of Member ID number and the photo link URL (one row per link) and a 3rd column for the edited link, then I can update the target link column to whatever it needs to be.

I might even get each member to update in an Excel file or something their own links based on where they upload the pics to / change filenames to etc. Then do the update to the DB table member by member.