WordPress provides a great feature for media management. In this article, we will see where the media library lives in the database. It allows managing (add, edit, delete) the media (images and documents). It not only stores the actual file in its directory but also stores the file name in the database. The media library lives in the database in wp_posts and wp_postmeta tables.
Table of Contents
How does WordPress store media library?
WordPress stores the media in 2 locations; the directory and the database.
It creates year-wise folders in the wp-content > uploads folder to store the files.
While the file information is stored in wp_posts and wp_postmeta tables.
- wp_postmeta contain the image/file URL; while
- wp_posts contain an entry for each image/file insertion into a post, along with the post ID.
Although, many plugins offer to export and import media from one site to another. However, the scenario we have discussed in this article will help when something goes wrong with media while migrating a WordPress website.
Each media item in the Media Library is represented as a post of the post type “attachment” in the wp_posts table. Here are some key columns in the wp_posts table relevant to media items:
- ID: A unique identifier for each media item.
- post_type: The post type, which is set to “attachment” for media items.
- post_mime_type: Indicates the MIME type of the file (e.g., image/jpeg, video/mp4).
- post_title: The title of the media item.
- post_content: Additional information or description of the media item.
- guid: The globally unique identifier that represents the URL of the media item.
- post_parent: Indicates the post ID of the parent post, which can be used to associate media items with specific posts or pages.
Learn more about WordPress Media Library by clicking here.
Export and Import Media
The simplest solution is to simply export the tables from the old site and import them to the new site. However, exporting and importing the 2 tables (wp_posts and wp_postmeta) as SQL did not work. While experimenting, we received an error ‘duplicate entry for key 7’.
Exporting and importing these 2 tables as CSV did work, using the “CSV using load data” option.
Before importing, I truncated the 2 tables in the database of the target website (that is, remove all records).
Get the attachments
WordPress use post_type as attachment for storing file information in the wp_posts table. The following SQL will return all the entries to the media library:
SELECT * FROM wp_posts WHERE post_type = 'attachment';
After the execution, you can export the result table as SQL or CSV, or any other portable data format you like. Remember if you are not sure whether the entries exist in the database, use the Insert IGNORE statement instead of INSERT. (Possible through exporting tab in PhpMyAdmin or other MySQL clients). Also, some entries refer to the media library for each post, such as attachment images or thumbnail images, which are stored in the wp_postmeta table. WordPress stores them so the media can get attached to the posts and pages. If you want them to be exported too, you will need to use the following SQL query:
SELECT * FROM `wp_postmeta` WHERE meta_key IN ('_wp_attached_file', '_wp_attachment_backup_sizes', '_wp_attachment_metadata', '_thumbnail_id');
Now, export them, and then you can import them wherever you want.
Conclusion
To perform more complex queries related to media items, you might also need to consider the wp_postmeta table. The wp_postmeta table stores metadata for posts, including attachments. Information such as image dimensions, captions, and other details can be stored in this table.
Note
It’s important to note that direct manipulation of the WordPress database is generally discouraged, and any modifications or queries should be done with caution to avoid data corruption. If you need to interact with the Media Library data, it’s recommended to use the WordPress core functions and APIs provided for this purpose.
