Custom WordPress table Queries for data operations

WordPress is content management system. It makes easy to build website. Even a layman can sit and invest couple of hours to make basic website. It has really good user interface.

If you want to go with wordpress, please visit WordPress site.

Techincal Aspects

Some of basic technical backgrounds are as follow:

• It is developed in PHP framework.
• It uses mysql as database.

Examples of Custom Query

1. Update wp_options table and set value from other row value of same table

Update wp_options set option_value=(select option_value from wp_options where option_id=151) where option_id=151

2. Update image description from wp_posts which is image and concat string for particular image id

update wp_posts ... set post_content=concat(post_content, "

Image Description

") where post_type="attachment" and id=5590;

3. Update post type post from wp_posts table and concat string with exisiting content for specific one id

update wp_posts set post_content=concat(post_content, "

Post Description

") where post_type="post" and id=5590;

4. Update post type page from wp_posts table and concat string with exisiting content for specific one id

update wp_posts set post_content=concat(post_content, "

Page Description

") where post_type="page" and id=5590;

5. Select post which has id 5590

select * from wp_posts where id=5590;

6. Select some columns from wp_posts for specific id and which is either post or page as page type

select id, post_name from wp_posts where id=308 AND (post_type="page" OR post_type="post")

7. Replace post name with any string for specific id and post/page type

UPDATE wp_posts SET post_name= REPLACE(post_name, '-relace-string', '') where id=308 AND (post_type="page" OR post_type="post")

8. Update table wp_posts as concat string with existing post_content for specific id and either post or page

update wp_posts set post_content=concat(post_content, "

this is string which is to concat to the existing post_content of wp_posts table.

") where post_type="attachment" and id=5590;

9. Finding all the distinct post_type (attachement which is image, post, page or other) from wp_posts table

select distinct post_type from wp_posts;

10. Selecting post_meta value _aioseop_custom_link of All in One SEO plugin from wp_postmeta table. It is for plugin table

select * from wp_postmeta where meta_key="_aioseop_custom_link";

11. Insert custom _aioseop_custom_link data with post_id and meta_value for particular one id

INSERT INTO wp_postmeta ( post_id, meta_key, meta_value ) VALUES ( '5410', '_aioseop_custom_link', 'http://www.lprakash.com.np/post/' );

12. Replace string -replace-string with blank string from wp_postmeta

UPDATE wp_postmeta SET meta_value= REPLACE(meta_value, '-replace-string', '');

13. Downloading or saving custom query output from wp_posts to posts.txt file

select id,'_aioseop_custom_link',post_name from wp_posts where (post_type="page" OR post_type="post") INTO OUTFILE '/tmp/posts.txt'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

14. Delete all data from wp_posts which not page or post type of column page_type

delete from wp_posts where post_type!='page' and post_type!='post';

Please suggest if you find any query is wrong or need to discuss. Please send email for any help for wordpress query. I will add more queries in future.