Tag ‘mysql’ » Archiv

4

How to do a mass text search and replace with MySQL

October
24

I noticed that all my images on the posts here weren’t showing and it was because the url to their location was still pointing at the sub blog on The Comluv Network so I needed a way to edit the database to update the url to point to the files held here.

Easy with a REPLACE function on the wp_posts table.

You just need 3 things for the replace command :

  1. The row you’re altering
  2. The string you’re searching for
  3. The string you’re replacing with

UPDATE `wp_posts` SET `post_content` = REPLACE( `post_content` , "fiddyp.comluv.com/files/", "fiddyp.co.uk/wp-content/uploads/" )

It did the whole table of posts in less than a second. Sweet, now all my images are back to normal on this site.

Code, PHP

31

My life in (other peoples) pictures

August
8

ok so, last week, I was like..

but, then I was like..

which had me feeling like..

until I wanted to …

but then..

so I needed to ..

and …

but it was hard…

and inevitably…

but at last!…

so now I’m like…

MYSQL – I OWN YOU!! hahahaha

Now I can really start to get to work on the CommentLuv site. I can pull just about any combination of data out of the database. Things like..

  • Last 10 links to receive CommentLuv on any site
    (for version 1.97 CommentLuv upwards)
  • Last 10 links from a particular URL
  • Last 10 links to a particular url
  • Top commented posts
    (that received CommentLuv on them)
  • Top commentators
    (who received CommentLuv)
  • Ban spammers!
  • Use a cacheing system for speedier retreival
  • Store default feed url’s for even speedier parsing
  • probably tons more but that’s what I’ve been playing with all this week

Keep tuned for more exciting CommentLuv news

Blog News, Code, Funnies

14

How to extract names and email addresses from your WP database

March
28

I had to do some playing around with my wordpress database when I wanted to move my install from a shared hosting to a dedicated server (which turned out to be quite easy!) and I went in a bit further to phpmyadmin to see what other things I could do.

With the current release of my new contest game, Aqua Craft, I wanted to let everyone that has visited here know about it and rather than wait for every single commenter to come back and see the post, I thought it might be easier to extract the email addresses of all commenters from the database and send them all a message. (luckily, only one person replied saying it was spam!).

I checked my wp_comments table and it had a few thousand entries, yey! I thought. I’ll send them all a message! I extracted the table into a csv file and was disappointed to see literally hundreds and hundreds of spammer email addresses in there…

wp_comments table with spam addresses

Not being a mysql database geek, I did some searching and found a way to extract all the addresses of the commenters that had been approved… here’s how…

1.) Log in to phpmyadmin
2.) select the wp_comments table on the left and click the “browse” button on comment_author_email

phpmyadmin wp_comments table

3.) Click “edit” on the mysql statment at the top of the page and change it to the following

edit mysql statement

SELECT * FROM `wp_comments` WHERE `comment_approved` = ‘1′

That will show you a list of all the comments made that have been approved.

4.) Click on the “export” tab and select these options..

export approved comments

This will give you a large .csv file ready to be opened by Excel

5.) Open the file in excel which will give you this..

raw csv file

This is a lot more than we need, so delete the columns for comment id, comment content etc so you’re left with comment_author, comment_author_email and comment_author_url

6.) Your excel file should look like this…

Fixed csv file

Now there will be a lot of duplicate email addresses from multiple commenters but if you have a decent newsletter or mass email program, you should be able to import this file and let it filter out all the dupes and discard the trackbacks and pings.

Easy peasy! I ended up with a few hundred email addresses who I could tell about my latest contest.. awesome…

Code, PHP, Wordpress