Colin Devroe

Reverse Engineer. Blogger.

JSON Feed to Twitter using PHP

In 2009 I scrawled together a simple PHP script that tweeted links based on an RSS feed. I then updated it to support OAuth and open sourced it on GitHub.

I haven’t really touched it since (though I get about 3 emails a month about it). Just a small update here and there.

This morning, with all of the JSON Feed hubbub going on, I decided to recreate that same simple script to parse JSON Feed rather than RSS. I also updated to the latest release of the PHP Twitter OAuth class by Tijs Verkoyen.

You can download the latest release or clone the repository on GitHub.

This took me mere minutes thanks to JSON Feed being much easier to deal with.

Hacking rather than waiting

Yesterday afternoon Sarah Pressler retweeted Jono Young’s request for a plugin that would add a submenu to the WordPress’ Admin with the current pages for the site under the Pages menu. This would reduce the number of clicks to get to the page editor.

I was waiting for an upload to finish and I thought, given the code I have laying around from other projects, I’d be able to supply Jono with something passable for what he needed. Though it is far from what I would recommend using, it is a start.

Here is what I did:

  • I used get_pages to get a list of WordPress pages the were published.
  • While looping through each page I used add_submenu_page to add the page titles under the Pages menu
  • I used JavaScript to replace the HREF attribute on the links to link to the page editor

That last step is the hacky part in my opinion. add_submenu_page() asks for a function name to call to build the page for the menu item when it is clicked. In other words, for each item on the Admin menu there is a corresponding function that will show you the page that it results in. But I didn’t want to create a new page, I wanted the link to go to the page editor.

I dug around for five minutes or so and I didn’t see an apparent way to change the links for the page titles (though I’m sure there is a better way if I had the time to continue looking) and so after the page loads I use JavaScript to replace the attributes with links to the page editor.

Nowhere near perfect but, in a pinch, it is one step closer to what Jono wanted. Hopefully he or someone else can build upon and it make something a little more reliable and less hacky.

You can see the source on Github.

Building a single page web site using an Excel spreadsheet

Need something like this? Consider hiring me for your next project.

I was recently asked by a client if I could use an existing Excel spreadsheet and create a single-page web site from it. This way, their team would be able to use the work they’ve already done and the data they’ve already keyed in and wouldn’t need to do double the work.

I could have built an data import tool that used the Excel data and reformatted it for WordPress or another content management system, but after asking a few questions I figured the simplest and most cost-effect approach was to use the Excel spreadsheet as the datastore.

It sounded fun. So I gave it a shot.

Here is what I came up with. It is a bit simple, arguably a bit crude, and wouldn’t work for anything terribly complex or for a high-traffic site, but, it works for them and that is all that matters.

Let’s start out with a simple example spreadsheet. Let’s say you have the following data for your soccer team in Excel.

Excel Soccer Team Example

In this case you have four columns; Name, Age, Jersey Number, Goals. How can you get those to display in a web page? Using PHPExcel, which I’ve used on several client projects, you can read from and write to an Excel spreadsheet fairly easily using PHP. Using the above data as an example, the code might look like this.

// Load PHPExcel, then load Excel spreadsheet
include 'lib/PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load($file);

// Read the Sheet data, create Array
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

$players = array(); // Array used to store player information

// Go through each row one-by-one
foreach( $sheetData as $row ) :

  // If Column A is not empty or null, or "Name" (this will avoid having a player with column names)
  if ( !empty($row[A]) && $row[A] != 'NULL' && $row[A] != 'Name' && $row[A] != '' ) :
    $players[] = array( 'name' => $row[A], 'age' => $row[B], 'jersey_number' => $row[C], 'goals' => $row[D] );


Let’s walk through this code quickly. First, we load the PHPExcel IOFactory, which lets us read from Excel. Then we load the Excel file. $file, in this case, would be the full path to the Excel spreadsheet you’d like to load. Then, we create an array out of each row of data in the sheet. Once we have that, we simply loop through each row and create a “Player” out of each one.

You’ll notice the way I avoided adding a Player with the column names is to check to see if $row[A] == "Name". You could also skip the first row depending on where in the spreadsheet your soccer team’s information is. But this is just an example.

Now that you have this Player array, you could use this information anywhere on your page by creating a simple for loop to go through them and write the list of player and their information.

Let me give one more example. This represents what my client needed much more closely.

What if you had variable names in Column A and wanted the values of those variables to come from Column B? Here is an example spreadsheet where this might apply.

Excel spreadsheet example profile

Here is what the code may look like for this…

// Load PHPExcel, then load Excel spreadsheet
include 'lib/PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load($file);

// Read the Sheet data, create Array
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

$person = array(); // Array used to store person information

// Go through each row one-by-one
foreach( $sheetData as $row ) :

  // If Column A is not empty or null
  if ( !empty($row[A]) && $row[A] != 'NULL' && $row[A] != '' ) :

    // Remove spaces in Column A to use as array key and value being Column B
    // Example A:'Person Country' B:'USA' becomes $person[person_country]='USA';
    if ( !empty($row[B]) && $row[B] != 'NULL' && $row[B] != '' ) :
      $person[str_replace( ' ', '_', strtolower(trim($row[A])) )] = trim($row[B]);


The subtle differences here, from the example above, is that $row[A] becomes the array key, and $row[B] becomes the value. By doing this, the person’s profile information is now easily printed anywhere on the page using <?php echo $person['age']; ?>

This simple Excel-powered approach becomes very powerful for someone with no web language skills. They can open an Excel spreadsheet, edit the values, and the web page changes.

Need something like this? Consider hiring me for your next project.

Three Toe: A simple SMS autoresponder on top of the Twilio API

If you find yourself needing something like this, why not hire me to make it for you?

A friend of mine somewhat recently became a licensed realtor. One thing that surprised him was the lack of technology being used in his workplace. Though not a technologist himself, he enjoys an efficient workflow such as using a tablet and managing documents digitally. So whenever he’s asked to complete a transaction using piles of paper and copy machines and so forth, it obviously gets under his skin.

For the last several months we’ve been trying to find ways that I can use my various talents (finite though they may be) to make his work easier or even improve the chances he’ll be successful in a sale. We’ve had a few conversations and personally I’ve been brainstorming and keeping notes and scratching out ideas.

Some of the ideas I’ve had are more expensive and involved while others are more simple and can be done inexpensively. One more expensive and involved idea is shooting drone footage of lakeside homes to help differentiate the listing. I hope to get a chance to shoot some drone footage soon. (hire me?)

One idea that, thanks to Twilio, is very easy and inexpensive to do is writing a SMS autoresponder to send property information to a would-be-buyer. And since I’ve wanted an excuse to play with Twilio this was my chance.

Here is the idea; A buyer pulls up in front of a house that they may be interested in. In the front yard, the listing agency’s sign would have a phone number that the buyer could send a code to and get information about the property.

It turns out that writing this on top of the Twilio API is very simple. (Coincidentally they IPO’d yesterday, their stock spiked 90%, and they wrote code live from the NYSE trading floor. Pretty good day for them yesterday.)

Here is what you need:

  • A Twilio account (you can use a free trial account to get started)
  • A phone number (you can purchase one from Twilio for $1/month or a short code for $1,000/month)
  • A server to host Three Toe

Let me just state right up front; using Twilio will cost money but it is very inexpensive. You get charged for each message received and each message sent. So in our example of a buyer texting a code and getting a response that is 1 round trip that costs just about a penny and a half (at current prices). A penny to send a home buyer a link to a beautiful web site of the home they want is a no-brainer.

Here is how Twilio works:

  1. Potential buyer sends a text message to your Twilio-powered number
  2. Twilio receives that text, creates a “POST” with general information about the sender (phone number, location, and contents of message, etc.)
  3. Twilio then sends that information to your app via a “webhook”
  4. Your app parses the response, and can do anything you want. In our case, we’re using Twilio to send a message back to the sender with information based on the code they sent

Theoretically you could use this simple service to do just about anything. You could ask for their email address and subscribe them to your newsletter. You could send back a YouTube video. You could keep a log of their phone number in a database and an agent could follow up with them at a later date. You could call the sender and read an audio message back to them with background music.  Or, you could first call the agent in charge of the property and then call the sender back and connect the two together.

But I didn’t do any of that. All I wanted to do for this service was set up an easy way to allow my friend the realtor to create an auto response for the code the potential buyer sent.

Here is the app works:

  1. Twilio sends a webhook POST and it is received by Three Toe.
  2. Using the contents of the message (e.g. “T0001”) it looks in a “responses” directory for a TXT file with that same name.
    1. If that TXT file is found, it responds to the sender with its contents.
    2. If that TXT file is not found, it responds with something like “There is no information available for this property.”

Very, very simple.

To add a new “code” that the application will respond to my friend the realtor just needs to add a new TXT file to the “responses” directory. Or, alternatively, I plan on creating a very simple form that will allow him to list and edit the responses and add new ones.

Here is the code: I’ve shared the basic application on GitHub in hopes that someone else can put it to use or even help improve it.

I’m fully aware that there are thousands of open source versions of this same sort of thing. In fact, this is basically what Twilio would likely use as their example code for others to learn from. I made this simply to scratch our itch and to get familiar with the Twilio API. It was fun to hack around with.

If you find yourself needing something like this, why not hire me to make it for you?

Update on June 28th, 2016: I’ve tagged version 0.2.1 on Github which includes a new configuration area to make it dead simple to create, edit, and delete codes and responses.

Update on June 29th, 2016: I’ve tagged version 0.3.0 on Github which includes a Bootstrap-style theme for the configuration area. This makes it easy to update codes and responses on mobile devices.

I have a few planned features for Three Toe that I’ll likely be able to build as extensions. I’ve already sold three copies of this simple app (with slight modifications) and some of the features needed by those customers will be making their way back into the repo. Fun app to hack away on.

Migrating Subscriptions from one Stripe account to another

One of my recent client programming projects (hire me here) was to help a company migrate all customers, cards, plans, and subscriptions from one Stripe account to another as a result of an acquisition. I hadn’t needed to do anything like this in the past and I ended up fumbling my way through a few of the steps. So, I thought it worth the effort to jot down the process.

Here is a graphic of the overall process with notes to follow below.

Stripe Migration

I made some notes along the way during this project. Using the numbers above, here is the process step-by-step and some things you’ll want to keep in mind before you begin.

  1. Send an email to to request Customer and Card migration from Account A to Account B.
    • Remember that both accounts need to be activated
    • Stripe cannot transfer live accounts into test accounts (a shame)
  2. Stripe transfers Customer and Card information from Account A to Account B
    • Stripe cannot “schedule” this transfer for an exact date or time
    • Stripe can run the migration more than once should new Customers be added
    • Customer IDs are retained from one account to another
    • Card IDs cannot be retained
    • Stripe will not migrate Plans or Subscriptions
  3. Transfer Plans from Account A to Account B
    • Retain all plan information (recommended)
    • The most crucial piece to keep the same is plan_id
    • You may want to make adjustments to “Statement Description” at this time if the business name has changed
  4. Transfer Active Subscriptions from Account A to Account B
    • There is no need to transfer cancelled or inactive accounts
    • During the transfer, request Subscription on Account A to cancel at period end
    • Create a new Subscription on Account B based on plan_id, using billing_cycle_anchor will allow the Subscription on Account B to begin the moment the Subscription on Account A is cancelled
  5. (not in graphic) Verify all Plan and Subscription information has been moved

Whether you’re using Stripe’s excellent API to migrate your Plans and Subscriptions or doing it manually via their admin the process is basically the same. The customer I was working with had nearly 5,000 customers and over 1,000 active Subscriptions on 4 Plans. So writing a script to do this saved huge amounts of time.

Aside: How do you calculate whether or not it is worth building a script to do this or doing it yourself via Stripe’s admin? Well, the pessimistic view would be something like this comic from the always excellent XKCD re: automation. However, the more practical approach is this comic from XKCD titled Is It Worth The Time?

How did I write the code? Depending on your exact needs and your familiarity in different languages you may want to find a completely different approach to the one I took. However, I’m a PHP guy, this is what I did.

I started by doing a search on Github for “Stripe Migration” (always worth starting here) which led me to this set of simple scripts from Nyalex for migrating Plans and Subscriptions on Stripe from one account to another. This likely saved me 5 to 10 hours of development. (Thanks Nyalex!) I then reached out to him on Twitter to let him know I was about to use his script. We had a nice conversation that helped me to get my head around how his scripts worked.

My modifications to Nyalex’s script were fairly minimal but vital. Since he hadn’t used the scripts in some time they fell out-of-date with Stripe’s latest API version. So I had to make some subtle updates to account for those. Also, the client was using Easy Digital Downloads, a WordPress ecommerce plugin, as their backend so I had to write a few SQL queries to update the Subscription IDs in EDD’s customer table as the process ran. I plan on writing a Pull Request for Nyalex so that I can help keep his set of scripts up-to-date for future uses.

There were, of course, a few hiccups when running the scripts.

First, the client’s server would time out if I tried to process all 4,800 accounts at once. Each account creates several Stripe API calls (about 7) so even with Stripe’s limit of 100 accounts at-a-time that is still a hefty 700 API calls. So understandably I had to find the right number that the server’s setup could handle without choking. I also slimmed down the number of API calls slightly from the default. I did this by creating some mock API calls for the cancel and create Subscription calls and let the script run a few times until I found the right number. For this particular client it was 20 accounts at a time being processed. Which meant the script ran over 200 times to complete the process. Because I was purposefully doing this manually, rather than setting up the script to run end-over-end, this process took a little over an hour.

Second, prior to creating those mock calls I accidentally duplicated a few subscriptions for 23 accounts and needed to go back and manually cancel them all before re-running the script. I recommend not doing this. Again, it is a drawback that Stripe cannot migrate live Accounts as Test accounts so that you can do test runs on this sort of thing but it is understandable.

And, lastly, I didn’t properly create a log file for all of the transfers as they were done so I had to manually save the output of Nyalex’s script to a text file. I think I’d like to update the scripts to create a log file of old subscription ID, customer email address, and new subscription ID at the very least. This would have saved me a lot of time and would give me a “back up” of sorts should any customers have been missed. Also, since the scripts only attempt to migrate active Subscriptions it would have been nice to extend the scripts to log inactive Customers too just as a double-check that those Customers were looked at.

Overall this was an interesting project to take on and I’m hoping that my knowledge from this project will get reused in the future. If you need to migrate from one Stripe account to another please reach out.

PHP is pretty bad

Since I wrote “I’m perfectly happy using PHP” last week I figured I’d show the other side’s viewpoint as well. There are those out there that loathe the language. Evee goes off on PHP like no one else could:

PHP is an embarrassment, a blight upon my craft. It’s so broken, but so lauded by every empowered amateur who’s yet to learn anything else, as to be maddening. It has paltry few redeeming qualities and I would prefer to forget it exists at all.

Her analogy to a toolbox full of tools that you can’t really use properly is apt. I do feel like I write a lot of workarounds for things. I’ve always thought it was due to the depth of my knowledge of the language. Perhaps it isn’t. Perhaps it is indicative of it.

Some of the things pointed out are rather baffling, such as why the functions names are inconsistently styled such as using underscores or not, etc. But those are a matter of taste really. A language can throw out a style-guide and still be very useful if those functions do things that are of value. As pointed out there, it just gets worse from there.

If you’re into programming you might as well read the entire post. It is good.

Use what works, play with the new

I had Unmark’d Kyle Slattery’s post on his company site (which I think is rather good looking; here is why) regarding why his company uses Ruby on Rails. It is a good post. Notice this bit:

It’s easy to get caught up in the newest trend, and there are lots of great technologies being developed, but at the end of the day, just because something’s new and shiny doesn’t mean it will move the needle for your business.

Bingo. By the time I finish editing this post seventeen more frameworks, libraries, or pseudo-languages will have been released. And honestly, that is fantastic. Because out of those a few will take off, be well supported, and become great utilities for future projects to benefit from.

However, this doesn’t mean we need to use them in live projects immediately. Or, that we should jump from one framework to the next because you like the way the method names use camelCase.

Kyle goes on:

When it comes down to it, I’m most productive when I’m writing Rails code. Sure, I could build out my next project in Node.js, or Go, or whatever else is out there, but I’m going to be able to crank out the best, most productive code in Rails. I know it best. I’ve been working with it for almost as long as it’s been out, so I know all of its ins and outs.

This piece, as he explains, is a big reason to decide to use one language or framework over another. Not just your own productivity, as he states, but also for those that may touch the code in the future. If the framework is widely known, actively maintained, and many people use it in live projects, chances are you can add more people to the project and not have to teach them much about your project. They can likely dive right in and be productive very early on.

Lastly, this bit:

A mature framework, while it may not be as exciting, has had thousands (maybe tens of thousands) of hours of developer time spent getting it to where it is, which generally results in a more stable and secure platform.

If the project your working on ever hits any type of scale, you will want to know that hundreds if not thousands of other projects that run on the same technology has done so too. Early on, many people pointed to Twitter as a project that helped Ruby on Rails mature. And also to Facebook as one that helped PHP get a lot faster. Do a bit of digging when selecting what to use in your project, if a “big boy” is using and supporting it then you will likely get more sleep.

The very same reasons Kyle uses Ruby on Rails is why I use PHP. I do like the way Ruby looks far better than PHP. (Insert GIF of DHH saying Ruby is gorgeous here) I also think that the Rails framework is well structured for web applications. I do think Go looks succinct and interesting. And Node is likely better for some of the things I’m trying to accomplish. However, I’m faster with PHP, a lot of people know it, it is very fast and stable, and has been used in large-scale projects. So I’m perfectly happy using PHP.

I try my very best to stay completely agnostic when it comes to choices like these. (Kyle and I still get along great even though he’s a Ruby fascist.) In fact, longtime readers of my little blog here have likely seen my opinion of Microsoft technologies change dramatically since 2012 or so. Recently I saw someone’s C# and was all o_0! It looked very, very nice.

I love playing around with new things in order to see what else is out there. I can’t tell you how many times I’ve built a new Ruby on Rails application or built something with React or this or that. You’ve gotta keep the juices flowing and one way is to push your abilities into new territory. I liken it to a guitar player that mainly plays rock music riffing with a classical pianist. Expand your horizons from time-to-time and it will only make you a better guitar player.

None of this is to say that you shouldn’t switch to something new when the time is right. In fact, one very good reason to play with new languages and frameworks from time-to-time is that when something you’re using needs to get the axe you’re likely better prepared than if you had your head in the sand. There may be a time to switch from PHP to something else and when that happens I hope I have an open mind and make a good decision. I’ll likely refer back to Kyle’s post to help me make that decision too.

RSS to Twitter using PHP

Update January 19, 2010: This script is now available on GitHub. Go forth and fork.

Today I noticed that my now ancient PHP script to update Twitter automatically using PHP/cron needed to be updated. It turns out that Twitter stopped recognizing URLs with ? in them as clickable links. Here is an example tweet where you’ll notice this happening.

I could have told Twitter and asked that they update the way they handle URLs but in reality my script was old, slow, too long, and shouldn’t include ? anyway so I figured I’d write a new one from scratch that included my short URL scheme.

So, here is the PHP script to parse an RSS feed and send the posts to Twitter. It includes a caching mechanism so that you won’t have duplicate URLs posted to Twitter. If you want it, take it. However, if you are better than I am at PHP (most 6yr. olds are better than I am at programming) then I ask that you fork the script on Gist and try to improve it.

Update Dec. 6 @ 5:34p: Kyle Slattery, follow Viddler team member, loves him some Ruby on Rails. As such he’s offered up this version of the script rewritten in Ruby.

Next up we have Anthony Sterling, self-proclaimed “PHP addict”, who has rewritten the script to make the configuration a bit easier. He also changed the way the cache is saved. He’s using a hashed version of the title for each post as his key. I do not believe this to be the best way to go, since post titles can easily change after publishing – but I do like that the script is about 20 lines shorter and the code is arguably cleaner.

Thanks to both Kyle and Anthony for their versions. Lets keep this going and see if we can get this script much more succinct, stable, faster, and usable by others?