The original title for this post was “How to print the Internet in the correct order”, but my humor can fall a bit flat some times so I thought I better stick to a more descriptive title.
We recently had a project at work that required us to print out the contents of one of our sites. This is an annual project that has around 20 contributors and in the past has always started out being edited and designed in Adobe InDesign and then “ported” to the web. This year we decided to flip the process on it’s head and have all of the contributors write their sections on the site and then export the contents and do the layout in InDesign.
Luckily InDesign supports XML imports and maps tags to styles very well. Also in our favor, WordPress natively exports in WXR format. The problem we ran into is that by default WordPress exports in “post id” order which is usually somewhat chronological. Due to the security practices in our shared hosting environment writing a one-off plugin (or using any existing plugin) would have to go through so many approvals that the project would be unable to meet our deadline to get to print. That meant that the WXR export was essentially useless to us since the editor and designer would have to spend as much time formatting and editing as the “old” way and we were trying to improve the process.
Adding to our pain was the fact that our shared hosting also restricted our database access. I knew if I could get access to the database I would be able to write a query that would provide the data (even if in SQL format) in the correct order. So I set about building such a query in the hopes that I would be able to convince our systems administrator to run it and provide me the output. Here is what I came up with:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| a.post_title title, | |
| a.post_content content | |
| FROM | |
| (SELECT | |
| m.post_title, | |
| m.post_content, | |
| m.ID, | |
| m.post_parent, | |
| m.menu_order as self_order, | |
| p.menu_order as parent_order, | |
| g.menu_order as gp_order, | |
| case | |
| WHEN m.post_parent = 0 THEN m.menu_order * 100 | |
| ELSE case | |
| when g.menu_order is null then m.menu_order * 10 + p.menu_order * 100 | |
| else m.menu_order + p.menu_order * 10 + g.menu_order* 100 | |
| end | |
| END AS parentId | |
| FROM | |
| wp_posts m | |
| LEFT JOIN wp_posts p | |
| ON m.post_parent = p.ID | |
| left join wp_posts g | |
| on p.post_parent = g.ID | |
| where m.post_type = 'page' and m.post_status = 'publish' | |
| ORDER BY parentId) a |
In order to test it I ran it on my local development environment and that’s when a lightbulb went off in my head. I could import the WXR import to my local development environment and have full access to the database!
So in the end, that is what ended up happening…the contributors wrote their articles, I exported the WXR, imported the WXR, ran the SQL locally, then used MySQL Workbench to export the results as an XML file that InDesign could import.
All-in-all it was a fairly pain-free process and we’re hoping next year to refine it even more.
Leave a Reply