{"id":2332,"date":"2018-08-10T22:03:18","date_gmt":"2018-08-10T22:03:18","guid":{"rendered":"https:\/\/www.billyok.com\/portfolio\/?p=2332"},"modified":"2023-02-10T12:56:00","modified_gmt":"2023-02-10T18:56:00","slug":"chills-thrills-and-comma-separated-values","status":"publish","type":"post","link":"https:\/\/www.billyok.com\/portfolio\/2018\/08\/10\/chills-thrills-and-comma-separated-values\/","title":{"rendered":"Chills, thrills and comma-separated values"},"content":{"rendered":"

Website:<\/strong> Quill, a Magazine by the Society of Professional Journalists
\nClient:<\/strong> Society of Professional Journalists
\nTechnologies used:<\/strong> ASP, SQL, WordPress, HTML, PHP, CSS
\nContributions:<\/strong> Exported the old site (in spite of itself), built a custom WordPress theme, rigged a couple plugins to display information in a way particular to the theme, imported the old site into the new site without breaking all the old links to stories that exist around the internet.
\nDemo: <\/strong>Quill website<\/a><\/p>\n

\"\"For reasons both complicated and not terribly interesting, SPJ\u2019s website has been hosted on a locked-in server since before I took the reins, and I have not lobbied successfully to host it somewhere more open, so it\u2019s on me to deal with the peculiarities of this setup instead. Which is fine, because it gives me some interesting (to me; your mileage may vary) challenges to creatively (same) solve.<\/p>\n

Take, for instance, SPJ\u2019s magazine, Quill, which has an online as well as physical incarnation. Since its 2001 internet debut, Quill\u2019s story archive has been locked away behind a members-only paywall, which means the database that contained all those stories was hitched to the locked-in website that also hosted SPJ\u2019s membership information. Keeping that archive as a member perk necessitated keeping Quill on SPJ\u2019s proprietary content management system, which was limited to the reader (stories almost never had images, even, unless we hand-coded them in) and a total pain to the folks on staff who had to add the magazine\u2019s stories to that archive. Nobody won \u2014 not even members, who never accessed those older stories enough to justify them as a value add.<\/p>\n

After years of unsuccessful lobbying, I finally received clearance to open that archive to all, not just members, which also meant I could host Quill anywhere and on any platform \u2014 provided I can export it off its existing proprietary platform without breaking the thousands of links pointing to Quill stories from all over the web.<\/p>\n

The process wasn\u2019t pretty, but the result looks rather nice. I hacked together a script to dump the entirety of the Quill database into a single CSV (comma-separated values; think spreadsheet but less attractive) file, created an empty WordPress database, reformatted the CSV file so that the data in the old proprietary database lined up perfectly with what WordPress would be looking for (including each post\u2019s unique ID number, which would have to be identical to the ID number it had in the old database), and imported that CSV file into the WordPress database using SQL commands. (I warned you twice that this might not be terribly interesting, so not my fault if you\u2019re bored but still with me here.)<\/p>\n

Everything imported successfully, completing the hard part of the job, though I still had to manually match each story with its correct author because no import\/export process could simplify that with the way that data appears in WordPress\u2019s database. But after that, not only was 17+ years of stories available in the new Quill, but \u2014 thanks to a little script I wrote on SPJ\u2019s web page \u2014 any time someone would visit a years-old Quill link, our site could use that unique ID number to send them to the same story on the new site without them ever knowing the difference. Like Arnold in that one Terminator 2 scene, I conquered the project without a single dead link as a casualty, which should always be the goal of any website migration, no matter how unwieldy.<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n","protected":false},"excerpt":{"rendered":"

Website: Quill, a Magazine by the Society of Professional Journalists Client: Society of Professional Journalists Technologies used: ASP, SQL, WordPress, HTML, PHP, CSS Contributions: Exported the old site (in spite of itself), built a custom WordPress theme, rigged a couple plugins to display information in a way particular to the theme, imported the old site […]<\/p>\n","protected":false},"author":1,"featured_media":2373,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[18,24],"class_list":["post-2332","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-web","tag-quill-magazine","tag-spj"],"_links":{"self":[{"href":"https:\/\/www.billyok.com\/portfolio\/wp-json\/wp\/v2\/posts\/2332","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.billyok.com\/portfolio\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.billyok.com\/portfolio\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.billyok.com\/portfolio\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.billyok.com\/portfolio\/wp-json\/wp\/v2\/comments?post=2332"}],"version-history":[{"count":6,"href":"https:\/\/www.billyok.com\/portfolio\/wp-json\/wp\/v2\/posts\/2332\/revisions"}],"predecessor-version":[{"id":2381,"href":"https:\/\/www.billyok.com\/portfolio\/wp-json\/wp\/v2\/posts\/2332\/revisions\/2381"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.billyok.com\/portfolio\/wp-json\/wp\/v2\/media\/2373"}],"wp:attachment":[{"href":"https:\/\/www.billyok.com\/portfolio\/wp-json\/wp\/v2\/media?parent=2332"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.billyok.com\/portfolio\/wp-json\/wp\/v2\/categories?post=2332"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.billyok.com\/portfolio\/wp-json\/wp\/v2\/tags?post=2332"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}