MySQL help

[The Bad Astronomer]

I am pleading to the combined experience of BAUTers here, at least those who know MySQL.

I have a bazillion pictures in a folder that I use for my blog. Stupidly, I didn't plan ahead when I started the blog, and so I have one big folder with all those images in it instead of having them in subfolders. I just posted a new entry with images, and so I created a new folder called "2006", and this is where I'll keep all my images for this year in the blog.

But I have hundreds (yikes) of images in my top blog picture folder, and I want to clean that folder up. So I want to create a "2005" subfolder and move them there. But if I do, I'll have hundreds of broken links in my 2005 blog entries.

I looked at the MySQL table for my blog, and there is a "posts" structure, and there is a table entry for "post_content" which has the text of my post. I'd like to change the links for all images from "/pix/bablog/" to "/pix/bablog/2005/". Doing it individually will take a long time of tediousness. I want to do something like global search and replace.

Does anyone know how to do this? I have phpMyAdmin, and can run SQL queries. However, my knowledge of all this is limited.

Thanks!

[HenrikOlsen]

update wp_posts set post_content=replace(post_content,"/pix/bablog/","/pix/bablog/2005/")

If possible, back up your database before doing major queries like that.

Edited after checking documentation, it is WordPress you're using, right?

[mickal555]

Would it be possibe to download the table(as a backup? or an export- via export tab(save with no compression- unless you have a gzip un zipper)) and do a find and replace in wordpad? And then restore the file via the SQL tab.
This is probobly not the best solution...

There may be an SQL query you could run... I'll look into it...

[The Bad Astronomer]

Ah, HenrikOlsen, thanks. I tried poking through some MySQL docs but didn't see that. And yes, I always back up my database before I try something stupid. :-)

I'll try that in a few minutes.

[HenrikOlsen]

Make sure you use my last edit, I started with something that was slightly wrong until I found the documentation for the blog software.

[phunk]

If that doesn't work out for some reason (although henrik's fix looks good. :)) PM me, I'm pretty good with mysql (I've recovered many BB's from the dead for my customers).

[mickal555]

I tested the query on my blog (same software), it worked fine. I knoe have 0's instead of o's :lol: .

[The Bad Astronomer]

It appears to have worked! Very cool. The query ran quickly, and I've been checking the older entries and they look fine.

Thanks again! Yay!