A PHP MySQL WordPress Tutorial
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 (
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:
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:
$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’” )
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!