H3XED

WordPress Media Library Database Row/Table Scheme & Info

Sep 30, 2017   Web Development   Nick Vogt   Comments
This post contains information on how WordPress stores images in the media library and its related database entries. Useful if you need to make corrections, perform actions in bulk, or want to create a plugin. As always, be sure to backup your database before making changes.


The Basics


Each image in the media library has one corresponding row in the wp_posts table and two in the wp_postmeta table. The row in wp_posts is the main entry for the image, while the rows in wp_postmeta describe information about the image, such as its file location and thumbnails available for it.

wp_posts
Each image in the media library has one row in this table, and its `post_type` will always be "attachment". The `post_mime_type` will be the image's mime type, which is useful for targeting specific types, such as "image/jpeg" or "image/png".

Keep in mind that WordPress stores blog posts, pages, images, and other types of attachments all in this table. They are differentiated by the `post_type` column.

wp_postmeta
Each row in wp_postmeta is a key-value pair, and each image in the media library requires two rows in this table.

The first row has a `meta_key` of "_wp_attached_file" and `meta_value` containing the file path in the uploads folder. For example: "2017/03/myimage.jpg"

The second row has a `meta_key` of "_wp_attachment_metadata" and `meta_value` containing a JSON string that describes all of the various thumbnails sizes available for that image. This string can be pretty long and hard to read, as it is generated by code and contains no extraneous spaces or line breaks.

Here is an example of what the two entries look like in the wp_postmeta table:

WordPress MySQL wp_postmeta

The `post_id` is the foreign key that is equal to the `ID` of the row in wp_posts, so WordPress knows what post the metadata corresponds to.


Deleting an Image's Database Entries


Let's say you deleted an image from your uploads folder using an FTP program, and now need to get rid of its lingering entries in the database. The first thing you need to do is find the deleted image's entry in wp_posts so you can get its `ID`. I suggest searching using the `post_name` column, which will be equal to the image's filename without extension:

SELECT * FROM `wp_posts` WHERE `post_name` = "myimage"
Once you've found the row corresponding to your deleted image, make a note of its `ID` (let's assume the ID is 6873) and then run these commands to delete the wp_posts entry and two wp_postmeta entries:

DELETE FROM `wp_posts` WHERE `ID` = 6873;
DELETE FROM `wp_postmeta` WHERE `post_id` = 6873;
Share This Post
Facebook Twitter

Comments (0)

Share This Post
Facebook Twitter
H3XED © Nick Vogt   RSS   Policies   Facebook   Twitter   Google+