Indispensable MySQL queries for custom fields in WordPress

Abhishek Ghosh

By Abhishek Ghosh March 12, 2011 8:23 am Updated on March 12, 2011

Indispensable MySQL queries for custom fields in WordPress

WordPress stores the data (articles, pages, categories, etc) in a MySQL database. It is therefore useful to know some codes to create a custom WordPress based website.

Note: This post includes MySQL queries for creating the custom fields in WordPress CMS (you can use for blog based design too). Before working in your database, take a backup first. You are responsible for your actions!

Disclaimers

Using phpMyAdmin

To run the applications listed here you should be able to log in to phpMyAdmin, a tool generally accessible from the administration of your host (via cPanel or Plesk for example).

Then go to the tab corresponding to the SQL database previously selected in the left side of the interface.

If you have never used phpMyAdmin, be careful!

Prefix Base wp_

For each of the queries below, remember to replace the prefix wp_ by one for your MySQL database.

It is highly likely that you do not keep the default prefix as recommended for safety reasons.

---

Backup your database

Before working in phpMyAdmin, backup your MySQL database.

Queries for custom fields

Add a custom field to all articles and pages

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'OwnCustomField'
AS meta_key 'myvalue AS meta_value FROM wp_posts
WHERE ID NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'MonCustomField');

1

2

3

4

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)

SELECT ID AS post_id, 'OwnCustomField'

AS meta_key 'myvalue AS meta_value FROM wp_posts

WHERE ID NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'MonCustomField');

This adds a custom field (custom field) to all items in the database.

Consider replacing OwnCustomField by the name of the field in question and MyValue by the value of your choice.

Add a custom field in articles only

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'MyCustomField'
AS meta_key 'myvalue AS meta_value
FROM wp_posts WHERE ID NOT IN
(SELECT post_id FROM wp_postmeta WHERE meta_key = 'MonCustomField')
`` AND post_type = 'post';

1

2

3

4

5

6

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)

SELECT ID AS post_id, 'MyCustomField'

AS meta_key 'myvalue AS meta_value

FROM wp_posts WHERE ID NOT IN

(SELECT post_id FROM wp_postmeta WHERE meta_key = 'MonCustomField')

`` AND post_type = 'post';

Same as above – with the same advice, but only for the blog articles (posts).

Add a custom field in pages only

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'MyCustomField'
AS meta_key 'myvalue AS meta_value
FROM wp_posts WHERE ID NOT IN
(SELECT post_id FROM wp_postmeta WHERE meta_key = 'MonCustomField')
AND `post_type` = 'page';

1

2

3

4

5

6

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)

SELECT ID AS post_id, 'MyCustomField'

AS meta_key 'myvalue AS meta_value

FROM wp_posts WHERE ID NOT IN

(SELECT post_id FROM wp_postmeta WHERE meta_key = 'MonCustomField')

AND `post_type` = 'page';

Same as above – with the same advice, but only for pages.

Indispensable MySQL queries for custom fields in WordPress

Disclaimers

Using phpMyAdmin

Prefix Base wp_

Backup your database

Queries for custom fields

Add a custom field to all articles and pages

Add a custom field in articles only

Add a custom field in pages only

About Abhishek Ghosh

Popular Articles