Share it!

WordPress is a flexible CMS and allows to manage a large number of posts that doesn’t limit users to regular “posts” and “pages” types of content through the use of custom posts.

WordPress also has built-in post import tools using RSS feeds, Tumbler, Blogger and several other sources. Other sources, such as Excel or CSV files, are also used after installing plug-ins.

However, in many situations it’s necessary to create posts from other sources, such as raw data stored in a MySQL database. In that case, it’s simpler to create a customized import script. When combined with custom post types, this provides a very powerful tool.

This article describes how to create a custom “product” post type and create posts using a PHP script and MySQL database as the content source.

Creating a WordPress custom post type

Before adding products to WordPress, it’s necessary to create a new custom-post type. In this case, I called it “product” but it can be anything you want. Here, we declare the posts’ CMS labels and configuration settings.

<?php
/**
* Create a new WP custom post type (product).
*
* @link https://imelgrat.me/wordpress/bulk-upload-custom-posts-wordpress/
*/
// Only declare the function if it doesn't exist (prevents PHP fatal error)
if (!function_exists('product_post_type'))
{
function product_post_type() // Function to register new custom post type
{
// Labels used inside the WordPress CMS
$labels = array(
'name' => _x('Products', 'Post Type General Name', 'text_domain'),
'singular_name' => _x('Product', 'Post Type Singular Name', 'text_domain'),
'menu_name' => __('Products', 'text_domain'),
'name_admin_bar' => __('Products', 'text_domain'),
'archives' => __('Products Catalog', 'text_domain'),
'parent_item_colon' => __('Parent Product:', 'text_domain'),
'all_items' => __('All Products', 'text_domain'),
'add_new_item' => __('Add New Product', 'text_domain'),
'add_new' => __('Add New', 'text_domain'),
'new_item' => __('New Product', 'text_domain'),
'edit_item' => __('Edit Product', 'text_domain'),
'update_item' => __('Update Product', 'text_domain'),
'view_item' => __('View Product', 'text_domain'),
'search_items' => __('Search Products', 'text_domain'),
'not_found' => __('Not found', 'text_domain'),
'not_found_in_trash' => __('Not found in Trash', 'text_domain'),
'featured_image' => __('Featured Image', 'text_domain'),
'set_featured_image' => __('Set featured image', 'text_domain'),
'remove_featured_image' => __('Remove featured image', 'text_domain'),
'use_featured_image' => __('Use as featured image', 'text_domain'),
'insert_into_item' => __('Insert into product', 'text_domain'),
'uploaded_to_this_item' => __('Uploaded to this product', 'text_domain'),
'items_list' => __('Products list', 'text_domain'),
'items_list_navigation' => __('Products list navigation', 'text_domain'),
'filter_items_list' => __('Filter products list', 'text_domain'),
);
// Custom type configuration
$args = array(
'label' => __('Product', 'text_domain'),
'description' => __('Aquation Products', 'text_domain'),
'labels' => $labels,
'supports' => array(
'title',
'editor',
'excerpt',
'author',
'thumbnail',
'comments',
'trackbacks',
'revisions'),
'taxonomies' => array(
'category',
'post_tag'),
'hierarchical' => false,
'public' => true,
'show_ui' => true,
'show_in_menu' => true,
'menu_position' => 5,
'menu_icon' => 'dashicons-cart',
'show_in_admin_bar' => true,
'show_in_nav_menus' => true,
'can_export' => true,
'has_archive' => 'products',
'exclude_from_search' => false,
'publicly_queryable' => true,
'capability_type' => 'page',
);
register_post_type('product', $args);
}
add_action('init', 'product_post_type', 0);
}
?>

Adding custom fields (optional)

Although it is possible to place all the information about a product in the content section of the post, we can also create custom fields to do that. Using custom fields has the advantage making it much simpler to search using them as filters or sorting posts using them as sorting keys.

While there are many ways to manage customs fields (WordPress also has native functions to do it), I used Advanced Custom Fields (ACF), one of the best (if not the best) plug-ins for this task. Here, I added a price field and on for entering the product’s ingredients. Of course, it can be anything you want or none at all if your posts don’t require additional data fields.

WordPress custom fields

Database structure and loading

For this example, I prepared a simple database structure but it can be as complex as you need. After creating the table, we can import data using CSV files, SQL from other tables, even typed into the table (it would defeat the purpose of automating the process but it is a valid option). Here’s the table layout I created using phpMyAdmin.

Structure to import WordPress data

Creating the posts

Once we created the custom post type and loaded all the data into the database, we can start creating the posts on WordPress using its API. The script below gets rows from the “products” table and using a wp_insert_post() from the WP API, posts are created.

Optionally, we can use add_post_meta() to add extra, custom fields (ACF’s update_field() function can also be used but this example works even if ACF is not being used). The post creation script looks like something like this.

<?php
/* Create custom posts in WordPress taking data from a MySQL database.
@link https://imelgrat.me/wordpress/bulk-upload-custom-posts-wordpress/
Load WordPress functions and plug-ins. Put correct path for this file.
This example assumes you're using it from a sub-folder of WordPress.
If you run it from somewhere else, adjust path to wp-load.php accordingly.
*/
require_once('../wp-load.php');
$database['hostname'] = 'SERVER';
$database['username'] = 'USER';
$database['password'] = 'PASSWORD';
$database['database'] = 'DATABASE';
$mysql_link = mysqli_connect($database['hostname'], $database['username'], $database['password']);
mysqli_select_db($mysql_link, $database['database']);
mysqli_query($mysql_link, "SET NAMES UTF8");
mysqli_query($mysql_link, "SET NAMES 'UTF8'");
mysqli_query($mysql_link, "SET CHARACTER SET UTF8");
/*
— Table structure for table `products`
CREATE TABLE IF NOT EXISTS `products` (
`product_id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`description` text NOT NULL,
`price` float NOT NULL,
`ingredients` text NOT NULL,
PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
*/
$query = "SELECT * FROM `products` WHERE `proccessed`= 0 ORDER BY `products`.`id` ASC;"; // products
$result = mysqli_query($mysql_link, $query);
while ($row = mysqli_fetch_assoc($result)) {
// Insert the post and set the category.
// See https://gist.github.com/imelgrat/85ea75390757f18293674abdc9bd89e6#file-bulk-upload-php
// for custom post type declaration
$post_id = wp_insert_post(array(
'post_type' => 'product',
'post_title' => $row['name'],
'post_content' => $row['description'],
'post_status' => 'publish', // Can be draft, pending or any other post status
'comment_status' => 'closed', // if you prefer
'ping_status' => 'closed', // if you prefer
));
if ($post_id) {
// Insert post meta (ACF Custom Fields)
add_post_meta($post_id, 'price', $row['price']);
add_post_meta($post_id, 'ingredients', $row['ingredients']);
}
echo $row['name'] . ' posted<br>';
}
?>

view raw
bulk-upload.php
hosted with ❤ by GitHub

Comments

This article explains a much-simplified process for automatically creating WordPress posts using a database (MySQL in this case) as its content source. It can be enhanced in many ways but the main intent was to show the necessary steps to automate content creation using WP’s API.


Share it!