MySQL related: How export url from 8k posts?

sonic

I have Wordpress blog with 8k posts.

Each post have 1 - 3 image url which all starts with http://mydomain/user/...

Do you know which SQL command can help me export all image url from 8k posts to TXT file?

mikho

is the image url set as featured image in Wordpress or is it in the body of the post?

sonic

@mikho said:
is the image url set as featured image in Wordpress or is it in the body of the post?

It's in body of posts

mikho

thats bad
I guess you can do a mysql query to extract the body part of each post to a text file and then grep the urls, it won't be foolproof and I'm sure that someone will come up with a more optimized way in the end:)

use adminer.php or phpmyadmin to export the data, it is in the "post_content" column in table "wp_posts".

you should exclude revisions of post and only exctract published posts.
when you have the extracted posts you can start testing with grep to get the urls

rethinkvps

If you can give us an example of the posts table, we can see what we would need to do.

rethinkvps

Also send me a PM, and I will see how I can assist.

jcaleb

nice exercise to solve. If I were younger I'll give this a go

ricardo

If you want to dump the data, use HEX() on the text fields.

What I would do:

• Create a temp.php and query your DB for the full result without buffering
• Do your replacements
• Write to file, using hexadecimal where there's going to be tabs or newlines.
• LOAD DATA INFILE into new table using CREATE TABLE newtable LIKE oldtable
• Check data is OK. If so, replace old table with new.

taronyu

Can you show a example of a database row with a image?

bookstack

I do not think sql query has good support of the regex, you can export the posts from the wordpress to a xml file, then use your favorite programming language, (awk, sed, perl, python) to parse the content and extract the pattern.

komputerking

If you export the data to an excel spreadsheet, you can use a regex function from inside of excel to a new column, and obtain all of the images that way. Once done with that, use a new column to aggregate them all, if you are trying to make use of a php method to download all of the files locally. That, or use a bot like with uBotStudio to download all the images for you