Insert WordPress Posts Through MySQL and Create Large Websites Quickly

8 Dec

A PHP MySQL WordPress Tutorial

PHP MySQL WordPress

Today we’re going to cover how to backdoor WordPress and insert posts through MySQL. We’ll also cover how to randomize dates, allowing you to take all of your inserts and create future WordPress posts. Why would you want to do this? In one earnest night of work, you’ll have created an autoblog with completely unique content, that will update entirely on it’s own. When you really start considering the possibilites this allows, you begin to realize that you can create some very large scale automated websites that will earn passive income with no user intervention.

Inserting a post in WordPress through MySQL allows you to dump massive amounts of information into a database at once. You can create very large websites in no time at all. If you have an excel spreadsheet or Access database, you can transfer all of that information into WordPress in a very short period of time.

Many of you will likely know what MySQL is but for those that might be new to the term, I’ll offer up a quick explanation. MySQL is the database that holds all of your WordPress posts. In the days of old, if you had a 1000 page website, you had 1000 pages in your directory. With PHP and MySQL, you use the same page everytime and it simply pulls information dynamically (on the fly) from the MySQL database as needed. No more overstuffed folders and looking through a thousand pages to update a single page on your website. MySQL is just a container that holds the contents of your website.

I’ve inserted wordpress posts through the backend for several of my websites. This allows me to create a lot of content in the shortest possible time. For my example today, we’ll look at my newly created King James Bible online. I created the first online Bible where every verse in the Bible is a separate post in WordPress! That’s a total of 24,678 posts in the Old Testament alone. Obviously, this would have likely taken me a few years to achieve this through the WordPress control panel. While I didn’t utilize the php date randomizing for this website, I have on several others, including a recipe website that has over 1200 recipes and will be updating on it’s own over the course of the next three years!

Okay. Let’s get to it…

What we’ll do here is convert an Excel spreadsheet to WordPress, so you’ll need an excel spreadsheet with data in it. If you’re just wanting to learn, create a new Excel spreadsheet and add five new entries. For my Bible website, I had each chapter of the bible as a separate tab in my worksheet. Each worksheet contained verse numbers (Genesis 1:1) and the actual verse. For a one page worksheet, simply insert a blank row at the top (If you have many worksheets, skip this step and we’ll do it later in Access). We need to make the 2 column worksheet into three columns so name each column ‘Title’, ‘Description’ and ‘Date’. Insert a date (any date will do), highlight all the ‘Date’ rows and do a quick copy -> paste (If you do plan to create your column headings later, you’ll still need to add a dummy date to every row for now). Easy enough. Excel is ready.

Next, I considered how I was going to get this data into MySQL. I knew this would be a much easier task if all of my information was in Access instead of Excel, since it’s relatively easy to make databases talk to each other these days. This process is pretty quick and painless. Open Access, create a blank database, go to the ‘External Data’ tab, click on Excel, follow the wizard and import your Excel spreadsheet. It will ask bring up a list of all your Excel tabs. Select the one you wish to import and click ‘Next’. If you already named your Excel columns as Title, Description and Date, select the box that says ‘First Row Contains Column Headings’. If you didn’t click ‘Next’ and name each column. Use a Data Type of ‘Text’ and ‘Date/Time’ for the date. Next, let Access add a primary key. This is a unique identifier for each insert that I needed to correctly order my posts in WordPress. Even if you don’t think you’ll need it, add it just in case you realize later you might. Tell Access the new table name and voila!

Now I needed a way to get this data into MySQL. With a quick Google search, I found a wonderful free product at Bullzip.com. Grab a copy of ‘Access to MySQL’.

When launching the program, you’ll likely receive a popup that says it can’t find the MySQL ODBC driver on your computer. We don’t care about that, as we’re just creating insert statements here. Click ‘Yes’ to continue. Browse to your new Access database, create a new ‘Dump File’ name, be sure the table found is selected and choose ‘Run Now’. Exit this little gem of a program and browse to the dump file you created. There’s all the MySQL statements needed to create a brand new database using phpMyAdmin. You can delete the very last line that tells the # of records and delete the first 20-something lines up to your INSERT statements.

phpMyAdmin is simply the interface we use to administer the MySQL databases. If you’re new to it, it can look a bit daunting but it’s a very easy program to use so jump in and learn! Your web host should have a link for you to login to phpMyAdmin as it’s a browser application.

In phpMyAdmin, I created a new database (name it anything) and table that I would need to transfer my Access database data to. For the book of Genesis, I created my table with this:

CREATE TABLE Genesis (
id INT(10),
title varchar(255),
description text,
date timestamp,
PRIMARY KEY (id)
);

As you can see, I created a new column for my primary key, the title, description and date. The table is created. Simply grab the contents of your new dump file, paste it in the SQL tab and run it. Your new database is populated. At this point, you may want to randomize dates, but since everyone won’t want to do this, I’ll put that information at the bottom of this post. Go there now to randomize.

Next up.. Create your WordPress category through the WordPress Admin (as you normally would). Create a new php page (we’ll call it transfertowordpress.php) and copy this into it:

// Configuration info goes here: this is the db I'm pulling from.
$hostname="localhost";
$dbname="database_name";
$user="database_user";
$pass="database_password";

// Update the category # below inside the array.

$link = mysql_connect($hostname, $user, $pass);
mysql_select_db($dbname, $link);

$results = mysql_query("SELECT * FROM Genesis",$link);

require('./wp-load.php');

$i = 0;
while ($row = mysql_fetch_array($results,MYSQL_ASSOC)) {
$post = array();
$post['post_status'] = 'publish';
$post['post_category'] = array(6);
$post['post_date'] = date('Y-m-d H:i:s',strtotime($row['date']));
$post['post_title'] = $row['title'];
$post['post_content'] = $row['description'];
$posts[$i] = $post;
wp_insert_post($post);
$i++;

}

mysql_free_result($results);
mysql_close($link);

?>

Edit the $dbname, $user and $pass, adding the information for your newly created database (my Bible database with the Genesis table). You’ll also need to insert the category number inside the array. If you don’t know how to find that, you can hover over the new category name in WordPress and look to the bottom of your browser. You’ll see the number displayed there.

Now let’s take a look at what this does. We’re obviously connecting to our new database first, doing a select in our database to get the records and then we’re cleverly passing the /wp-load.php file into the script. This is the php page that WordPress calls when we create a new post. It calls the WordPress database and already contains the connecting information we need. The script then performs a loop ($i++) and inserts each record into our WordPress database!

To execute, you just upload this file to the base directory of your WordPress install and exectue it through your browser. When you browse to http://yourdomain.com/transfertowordpress.php, you’ll see the file takes a few extra seconds to run, as it is populating your WordPress database.

That’s it! You now have all of your original Excel data in your WordPress database as unique posts. This method also allows us an inside look as to how easily a database can be compromised. If you have a guest folder that allows someone full rights, it’s not too hard to create a file that can call /wp-load.php and do some nasty things to your WordPress database. I’d also highly recommend that you delete this new php file after running it.

I also promised to randomize dates so let’s follow through with that. This should be done before you export your data into your WordPress database as WordPress will use your dates in several different places. Create a new php page. Let’s call it randomdates.php and paste the following in it:

// define mysql connect
$dbname = "database_name";
$dbuser = "database_user";
$dbpass = "database_password";
$dbhost = "localhost"; // this will usually stay 'localhost'

$my_table = "Genesis"; // define wordpress table name

$gmt_offset = '-6'; // -8 for California, -5 New York, -6 Missouri etc.

// I'm setting into the future year so WordPress can publish as , else I would use 1 for min and 60 for max, for last 2 months.
$min_days_old = -365; // the minimum number of days old
$max_days_old = 1; // the maximum number of days old

// connect to db
mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);

$result = mysql_query("SELECT ID FROM $my_table") or die(mysql_error());
while ($l = mysql_fetch_array($result)) {
$post_id = $l['ID'];
echo "Updating: $post_id
“;

$day = rand($min_days_old, $max_days_old);
$hour = rand(0, 23);

$new_date = date( ‘Y-m-d H:i:s’, strtotime(“-$day day -$hour hour” ) );
$gmt_new_date = date( ‘Y-m-d H:i:s’, strtotime(“-$day day -$hour hour -$gmt_offset hour” ) );

mysql_query(“UPDATE $my_table SET date=’$new_date’ WHERE ID=’$post_id’” )
or die(mysql_error());

}

echo “


DONE!”;

?>

Just update your database information, table name and specify the values for minimum and maximum days old. The values above show how to post into the future for one year. Like the above example, call this page in your browser and all of the dates in your database will be evenly randomized for the next year! How easy is that?!

Hopefully this article will generate some ideas for you and allow you to create a truly automated website or a site with a lot of unique content, without having to go through the tedious process of creating individual posts. Let me know what you think!

As always, thanks for stopping by and reading and I hope that you will please subscribe!

Jeff

Jeff

Jeff is a php web developer and designer with a focus on creating scripts that help others achieve financial success online.

Jeff

Jeff

Latest posts by Jeff (see all)

Related posts:

  1. New WordPress Theme – October Red

27 Masterfully Constructed Opinions - Add Yours to “Insert WordPress Posts Through MySQL and Create Large Websites Quickly”

Login below to comment or reply.
  1. Dave January 3, 2011 at 5:00 am #

    Smile

  2. dave January 4, 2011 at 6:30 am #

    Amy, can you post a sample of your ‘excel’ document to see exactly what you are pasting into your mysql table?

  3. dave January 4, 2011 at 6:40 am #

    Also is there a way to add ‘tags’, select authors, and choose from an ordered list of categories (say you’d want it posted in more than one category) to the posts? Please write me back on any info you might have, thanks!

  4. Amy January 5, 2011 at 5:29 pm #

    Hi Dave,
    There’s really not anymore to the Excel doc than what I explained above. You can literally create a new doc with 3 columns at the top: ‘Title’, ‘Description’ and ‘Date’
    For testing, you could populate just a single row under those headings with anything you want and that is enough to make this work.

    As for multiple categories, just add other numbers to the category array above in the code. I haven’t messed with multiple authors but I really don’t think it would be too hard to modify the code to do that though.

    Thanks for stopping by!

  5. Frank January 24, 2011 at 12:19 pm #

    Hi
    Amy came across your post on how to get data into mysql to add WordPress posts.

    I worked the other end and add posts via the csv import tool for wordpress. I am still adding posts. I will try the method you outlined
    Thanks for ther tip
    -Frank

  6. Amy February 1, 2011 at 3:12 am #

    Hi Frank.
    Thanks for the comment. I’ve not experimented with the csv import tool. I’m intrigued now so I’ll have to have a look. Let me know how it works out.
    Thanks for stopping by!
    Amy

  7. Vegas Kev February 10, 2011 at 5:47 am #

    Hopefully you can help me. Explaining this may be a pain for ya, but I would really appreciate any assistance that you may be able to give me.

    Here is my scenario.

    -I am trying to import a csv file into phpmyadmin.
    -The MySQL database is for WordPress.
    -The .csv file is individual posts.
    -Each post has multiple custom fields.
    -Some of the fields include content such as (“Wink and (,)

    My issues are as follows:

    1) I am trying to figure out how to do the import into the wp_post table, even though there is no “field” within THAT table that is “category”, nor are the “custom field” fields. They are apparently within different tables.
    2) I cannot import the posts without the custom fields, as they are extremely valuable to the posts themselves.
    3) Two of the custom fields are hyperlinks that reference specific images within the database
    4) One of the custom fields is a hyperlink to another page within the site.

    Any ideas? Even a point in the right direction would help out a lot. I’m just getting starting with databases, and would love a little nudge in the proper direction. I don’t want to completely mess up the website before I even get it to launch. I’m highly considering the “Excel to Access to MySQL” suggestion, however, I still have the same questions and issues as before. As for the dates (it’s a directory, so I’d want to have them published same day. Thanks in advance.

    • Vegas Kev February 10, 2011 at 5:49 am #

      that wasn’t a smiley character above…it was “quotation marks”

  8. Vegas Kev February 10, 2011 at 5:48 am #

    …ugh…that wasn’t a smiley character above…it was a “comma”

  9. Alfred February 18, 2011 at 4:34 pm #

    Thanks for the info Amy! I guess for the beginners the “CVS importer” plug in will do the job easily. Smile

  10. Amy March 1, 2011 at 1:57 am #

    Vegas, Let me know if you’re still having trouble. I found your comments in my spam folder. I try to go through them all before just performing a mass delete and saw them in there a bit late! Thanks,
    Amy

  11. Rotten Elf March 1, 2011 at 6:38 am #

    A note to all users of the CSV Importer plugin. It is a great tool, however, if you need to import huge (thousands of posts or more), you may need to do some config customizations. I have been working with this plugin, as well as the developer of it to do some very large projects. If you need any more help (I know the developer is extremely busy lately), feel free to hit me up for FREE SUPPORT. Just leave a comment on my page about the plugin: http://rottenelf.com/wordpress/wp-plugins/csv-importer-plugin-help <–I will gladly help anyone with issues on this plugin. It's the least I can do for the developer who put so much time into a plugin that has saved me thousands of hours and will continue to save me countless hours.
    Rotten Elf recently posted.. CSV Importer Plugin Help

  12. Shahid May 19, 2011 at 11:01 am #

    Amy,

    You have wrote what i was thinking about.

    Thanks for this helpful tutorial about wordpress and mysql.

    Love to read more about wordpress.
    Shahid recently posted.. Pathan SMS in English and Urdu

  13. James June 4, 2011 at 2:12 pm #

    Amy,

    Thank you for the great article. I was researching how to do something similar. I really liked your bible post project.

    Yours is the second article that I’ve read about using wp-load instead of doing it in perl like I had planned, so I guess I should actually crack open the editor and see why it is recommended.

    Thanks!
    James recently posted.. Google MX Setup

  14. James Barber June 23, 2011 at 3:56 pm #

    Amy,

    Great work. I am having a slight issue however, I wonder off the top of your head what you think it might be – I have no issues following your script where text within the ‘description’ is fairly short. Where I have a few paragraphs of text within my description; I can see the text I require within the .sql dump created with the Bullzip app. However, when I run it through the php script to import it into WordPress, only the first few lines of description text imports. I have it set to longtext as you describe – any ideas?

    Thanks for posting this, it has been very useful! (for short paragraphs, anyway!).

    James
    James Barber recently posted.. Brand Success – Applying Theoretical Frameworks &amp Models

    • Blue September 2, 2011 at 12:15 pm #

      This makes everything so compellety painless.

  15. web jaipur July 15, 2011 at 12:16 pm #

    thanks JEFF for the great post.
    i have spent 2 days searching this.
    i have a question here.
    i want to add some 5 custom field along with posts
    so what code i should add to make this happen?

    • Jeff July 19, 2011 at 7:44 pm #

      I’m not quite sure what you are trying to achieve. Are you trying to add data to the WordPress posts table? If so, I’m not sure how you would achieve that as I don’t think you can append columns to that table.

      What I would do is look to include the information in the description, and line break where necessary.

      That’s my first guess at a solution. Not having a firm understanding of what you’re trying to do makes it a bit tough for me to make a good recommendation.

  16. Web Jaipur July 20, 2011 at 5:20 am #

    i have table which has 5 more columns other then ( post_status, post_category, post_date, post_title, post_content )

    say post_img, post_url, post_ etc.

    now how do i add those data along with title, description,date etc.

  17. Bill October 13, 2011 at 2:10 pm #

    I tried the above exactly as indicated. I’m using WP v3.2.1 but I’m getting the following when I try to the transferwordpress.php file:

    Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\test\transfertowordpress.php on line 19
    # Time Memory Function Location
    1 0.0003 376416 {main}( ) ..\transfertowordpress.php:0
    2 0.2480 27206336 mysql_fetch_array ( ) ..\transfertowordpress.php:19

    Warning: mysql_free_result() expects parameter 1 to be resource, boolean given in C:\wamp\www\test\transfertowordpress.php on line 32
    Call Stack
    # Time Memory Function Location
    1 0.0003 376416 {main}( ) ..\transfertowordpress.php:0
    2 0.2482 27206392 mysql_free_result ( ) ..\transfertowordpress.php:32

  18. Bill October 13, 2011 at 6:55 pm #

    I figured it out. I was missing the db password. How can I modify this to populate pages instead of posts though? Is that possible?

  19. thomas salvador November 5, 2011 at 6:10 pm #

    @bill,

    pages are also written with wp_insert_post.

    just add the post type

    find
    $post = array();
    $post['post_status'] = ‘publish’;

    and change to
    $post = array();
    $post['post_type'] = ‘page’;
    $post['post_status'] = ‘publish’;

    regards,

    thomas.

  20. tranh nghe thuat March 7, 2012 at 9:13 am #

    A cool tip for insert post in wordpress. Thanks

  21. Steve May 4, 2012 at 2:26 pm #

    Do you have a script to post keywords for a WP entry that is added manually like the above code?

    • bb hank September 24, 2012 at 6:26 pm #

      Script works but only imports 105 entries from database. Gives no error, just stops as though completed. Database has over 900 rows with 8 fields in each and more to come. Seems to have no rhyme or reason as to which it selects but never more than 112 and always the same set. A second run just repeats the first – It needs to append with different entries – no dups until finished. List will increase to over 8000 rows.
      Also, looking for way to put data in custom fields. Country, City, State, Zip, Address. It puts name in Title field as needed but need other information where it can be accessed for locational data.

  22. Donna Hennessy December 4, 2013 at 4:08 am #

    It was too heavy for me as I am still a newbie to Mysql. But I have understand the usefulness of using mysql to insert wordpress posts. It will take me some time to learn. Smile