Quick mySQL Query help

[lukep]

Alright coding Bros and brosephs, I've got a simple one for you database folk.

I've got a huge, slow WP database that I just want the posts and titles out of to use again one day.

In the past I've used an exporter tool from inside wordpress to export to an XML file but I can't even keep the blog up anymore; it's pathetic.

So I'm thinking that a CSV file would be fine, such as a SQL Query from inside phpmyadmin, of the "_posts" table, with just the two columns for all published posts. ("post_content" & "post_title". So three columns including "post_status" if it's easier to do it that way.)

Sadly, I'm clueless how to do queries like this, never having gotten my Ph.D in mySQL.

Also, the post table is sure to have quotes in it so that would suck as a delimiter. Can the query ask for a | instead to delimit columns?

Anyone wanna lend a duck a hand?

Thanks bros,
Luke

 

[dbm009]

Mysql through phpMyadmin:

Open up the DB for the domain and click on the SQL tab

Input the code below-
[the red LIMIT 10 is for testing, since I don't know how big your database is]

SELECT `post_title`,`post_content` FROM `wp_posts` WHERE `post_status` LIKE 'publish' [COLOR=Red]LIMIT 10[/COLOR];

Click GO

1) Scroll to the bottom of the results and click export

2) Check:

CSV for MS Excel ( note, I usually use CSV for PMA > 3.4.x)

and

Save as file

Click GO at the bottom corner

3) open in excel and check the results

4) If the first excel file looks good, DELETE LIMIT 10 in the mysql query and export again

5) If you are using a newer version of PMA then the interface might be a little different. If that is the case then you will want to change:

a) Export Method: Custom - display all possible options
b) Format: CSV


--if the db is large( 10mbs - GB+) then you might want to check zip or gzip before exporting-depending on your download speed and computer

--If it is a really large DB, you can break the exports down into chunks at a time


An ideal way of backing up would be to go through the server(putty), dump+gzip, and then download from a ftp(winscp) or copy to another server.

In your case, though, using PMA is probably easier, unless the DB is very large...

**Not a server DB guru, but I use php/MySql and PMA daily on multiple medium sized databases(50 -100gb+)

 

[hi.imandrew]

Mysql through phpMyadmin:

Open up the DB for the domain and click on the SQL tab

Input the code below-
[the red LIMIT 10 is for testing, since I don't know how big your database is]

SELECT `post_title`,`post_content` FROM `wp_posts` WHERE `post_status` LIKE 'publish' [COLOR=Red]LIMIT 10[/COLOR];

Click GO

1) Scroll to the bottom of the results and click export

2) Check:

CSV for MS Excel ( note, I usually use CSV for PMA > 3.4.x)

and

Save as file

Click GO at the bottom corner

3) open in excel and check the results

4) If the first excel file looks good, DELETE LIMIT 10 in the mysql query and export again

5) If you are using a newer version of PMA then the interface might be a little different. If that is the case then you will want to change:

a) Export Method: Custom - display all possible options
b) Format: CSV


--if the db is large( 10mbs - GB+) then you might want to check zip or gzip before exporting-depending on your download speed and computer

--If it is a really large DB, you can break the exports down into chunks at a time


An ideal way of backing up would be to go through the server(putty), dump+gzip, and then download from a ftp(winscp) or copy to another server.

In your case, though, using PMA is probably easier, unless the DB is very large...

**Not a server DB guru, but I use php/MySql and PMA daily on multiple medium sized databases(50 -100gb+)

Great info, you beat me to it!

 

[lukep]

That was it dbm009!

Thanks a ton. It worked first time.

Cheers,
Luke

 

[dbm009]

Good to hear - glad to help