MySQL: Group By Column And Include Its Count

Wave Crashing, MySQLWithout going into too much detail about the why, consider the below table. It’s a snippet of a table I’m working with for an e-commerce site. Any user (user_id) can have different products (product_id), but any user can also have multiple entries of the same product.

id user_id product_id
1 10 50
2 11 50
3 11 51
4 11 51
5 12 50
6 12 50

When displaying this data to a user, say user 11, I can’t do a simple flat list. This would confuse the user, showing multiple entries of the same product. Like this:

select id, user_id, product_id 
FROM wp_wc_licenses 
WHERE user_id = 11;

And the resulting table:

id user_id product_id
2 11 50
3 11 51
4 11 51

The solution
To give the user a more accurate display of their products, I want to group them by product, but also include a count of that product. Here is a much better way to get the data that makes sense.

SELECT id, user_id, product_id, count(*) AS `product_count`
FROM product
WHERE user_id = 11 group by product_id;

And the resulting table:

id user_id product_id product_count
2 11 50 1
2 11 51 2

I found this technique from this post. It saved me from myself, having to process this in PHP. That. Would. Have. Sucked.

Posted in: Code Samples, Development  |  Tagged with:  |  Leave a comment

WooCommerce: How To “Trash” All Orders Really Fast

WooCommerce Logo
Some context
I am working on a WooCommerce installation where I’m dealing with literally thousands of historical orders. All of which I am programmatically importing (including user accounts!) and doing a bunch of special updates, which are numerous and complicated, but not the subject of this post. But this gives you enough context on which to move forward.

As I develop this import tool, these thousands of orders need to be removed (or “trashed”) after each test I make (finding and fixing bugs) because naturally you can’t have duplicate orders. Am I right or what?

The WooCommerce order admin interface is robust and powerful, but not for bulk trashing. I can only send around a maximum of 350 orders to the trash at any one time. So this process of “resetting” the orders is time consuming and cumbersome.

I searched and thought and searched and thought some more, wondering how to quickly trash every single one of those damned orders after each test. And then I remembered: an order is just a custom content type. Well I didn’t forget, but if you’ve ever developed anything, you’re jugging hundreds of lines of code and switching from language to language and from one task to the next. Not everything you work on is at the forefront of your brain, our human RAM, if you will. I think you’re following me. We basically can’t see the forest through the trees.

But then I did remember.

The solution
The order custom content type (shop_order) that is WooCommerce has all the basic functionality that is WordPress. Any content type has a post_status field associated with it and can have values such as: publish, draft, trash, etc. You can read up on post statues, here.

To quickly send all orders to the trash run the following sql command. Super easy. This would work for any content type you’d just need to update the where clause.

update wp_posts set post_status = 'trash' where post_type = 'shop_order';

Then you can go one step further and view your order trash via WordPress admin and click on ‘Empty Trash’. I wouldn’t recommend using this anywhere near a production machine. Use at your own risk. 😉


Posted in: Code Samples, Development  |  Tagged with: , ,  |  Leave a comment

Harvest Launches Mobile Friendly Invoices

Harvest LogoI love Harvest — the time-tracking bombshell of a service. They do more than track time, though. You can generate client invoices, manage projects, run real-time reports and with their mobile or desktop app you can easily switch between projects and tasks at the touch of button (okay, two). As far as I’m concerned this is the best $12 I spend every month. I can’t function without Harvest.

Time tracking, the nightmare
For years, working as an employee for many different companies, time tracking was always a nightmare. One particular Seattle agency (which I won’t name, because it hasn’t any relevance to the point I’m making) had such an archaic and inefficient system that my boss hounded me regularly to logged my time. It’s not that I didn’t value tracking time, it’s that I HATE inefficiencies, so I just wouldn’t do it. My time tracking or lack thereof got so bad that the company actually offered to buy me my own license to the software and have it installed on my work computer (because back then they had only a few seats). Needless to say the system sucked. Anyways, I digress.

Those days are gone with companies like Harvest. And just a couple of days ago they launched a mobile friendly version of client invoices. This not only enable my clients to quickly and easily make payments on the go, but it shows you have a very professional way of doing business. You can read more about it here.

Posted in: News  |  Tagged with: , ,  |  Leave a comment

Proton Mail Indiegogo Campaign Raises $115K In 3 Days

ProtonMail ScreenshotIf you’ve seen or heard of Proton Mail in the news recently you might just be wondering what exactly they are all about. Well, I’ll tell you. Proton Mail is an NSA-proof end-to-end encrypted email service built by scientists from MIT & CERN. You can read an in-depth review here. Yes this is all quite geeky I realize, but what they are building is essential to maintaining private communications in this day and age. And the best part is they are focusing on simplicity and usability. No encryption knowledge necessary.

So anyways, a few days ago they launched an Indiegogo campaign raising $115K to expand their infrastructure to support the overwhelming response they’ve received. And in the time it took me to write this post they’ve hit $119K.

Of course, not everyone is jumping up and down, throwing money at them. One commenter on Indiegogo pointed out a reasonably significant usability issue when sending Proton emails to non-Proton accounts (grammatical errors aside):

So basically If I want to send an encrypted email to my colleagues, what I’m really sending is a link to a web page where they can access through a password. I’m sorry to say but that is not an email. A much more simple and fastest way is to use a P2P encrypted service like Bitmessage

It’s a valid argument, but at this point I don’t see how they could implement it any other way. They do give you the option of not encrypting a particular email, in which case it passes directly into the users inbox. But then why the hell would we use Proton Mail in the first place? Haha.

Posted in: News, Technology  |  Tagged with: , , , ,  |  Leave a comment

“Can You Throw This up on the Website?”

In my profession, occasionally I’ll get an email from a client requesting if I can ‘throw something up on the[ir] website.’ I’ve had good laughs with co-workers and colleagues in the past about this, because this question is ultimately very loaded and is anything but the simplicity the question implies. This question implies that I could do this task as easily as throwing a basketball through a hoop. (Which doesn’t take very long, by the way.)

Now to be fair, there is nothing wrong with this question. I’m sure in other industries (that I know nothing about) I’ve ask similarly naive questions. (Haha… I can’t think of any right now!) The questioner simply doesn’t know what it takes and more importantly doesn’t know what questions to really ask. This is a huge part of my job. To ask questions. It’s amazing actually, as I type this, how many questions I do ask on a daily basis. I have a bit of a complex about asking too many questions. Alas, that’s the way I work. I need information. I need to know how something functions, how it looks, where it goes, how it integrates, how do you, the client, need to administer it, if at all?

So the take away?
As with most challenges in life, you have to think through it. So what if it’s a loaded question? Be nice. I’d have to dig down and figure this out even if a client wrote me a book on what s/he wanted. This is nothing more than an opportunity to hone my communication skills and hopefully help someone understand what it takes to throw something up on their website.

(Of course, if you’re up against a deadline, a one line request like this could prove very frustrating. In which case you’d have to fall back onto your contract and renegotiate or provide a separate quote for the extra work.)

Feel free to tell me your stories in the comments.

Posted in: Development  |   |  Leave a comment

Twitchy Artifacts with 1080i Video in Final Cut Pro X

I was cutting a quick video—which ended up not being so quick—for a friend. One not-so-small clip was recorded in 1080i 60 fps. Once imported into FCPX there were these twitchy artifacts forming every couple seconds during playback.

I searched the web and the problem has to do with needing to first de-interlace the source video. One solution was to download a program called JES Deinterlacer. After a number of failures—the program would freeze after processing a short number of frames—I gave up and went to bed.

The following morning I suspected that the freezing problem was due to the fact that I was working off an external hard drive. No bother. So after copying the file over to my Desktop I was able to process the entire video. The resulting file, after importing into FCPX, was free of those twitching artifacts.

Sadly, however, the quality was degraded. But that is a side effect of de-interlacing. Read more about it here.

Also, one solution was to allow FCPX to do the do-interlacing when you share out a master file, but I had no luck with that. Any time I rendered the project the resulting master file would contain the artifacts.

I am very thankful for the JES Deinterlacer.

Posted in: Video Production  |  Tagged with: , , ,  |  Leave a comment

The Stupid Zip Archive and WordPress Duplicator Issue

I’ve run into this issue a couple times and had to set everything aside and figure it out. It didn’t take long. I use WordPress Duplicator often to, well, duplicate and move different WordPress sites from production, for example, to a local dev installation.

Well, recently on two sites running the WordPress Duplicator the Zip Archive failed to load. After some searching I ran across this post which pinpoints the problem to the PHP version you are using.

So I simply switched the PHP version from 5.3.27 to 5.5.6. The post above, however, suggests downgrading the version to 5.3.x is what works. That post, however, is almost a year old, so I imagine something change on the Media Temple end.

Posted in: Development  |  Tagged with: , , , , ,  |  Leave a comment

Unzip via SSH

I had to unzip a file on a clients server today. Unzipping it locally and then re-uploading it would have been a pain; the unzipped folder was about double the bits of the original ZIP file. Anyways, once terminal’d in, this is how it works on the Media Temple GRID server.

unzip /path/to/

Pretty simple. Thanks to this resource for the know-how.

Now I’m off to go play in the ocean.

Posted in: Development  |  Tagged with: , , ,  |  Leave a comment

Troubleshooting: Media Temple GRID SFTP Connection Issues

Sometimes I need to change a GRID Server Administrator password over at Media Temple. Once changed, I continually run into SFTP connection issues. It’s goddamn frustrating. And it was just working! I just want to code, not putts around with this crap. Even more annoying is I can connect just fine via FTP on port 21.

So in an attempted to save my sanity (and maybe yours), I’m going to list all the troubleshooting steps I’ve had to deal with every time this happens. Ug… so annoyed.

  • Is your IP address blocked from too many failed login attempts? That’ll do it and drive you crazy. Here’s how to remove an IP ban.
  • Are you connecting with the Server Administrator account or a secondary FTP account that you’ve created? Secondary FTP accounts aren’t allowed to connect via SFTP. So don’t bother. You’ll go mental until you figure this one out. Here’s more info on using FTP and SFTP.
  • Have you tried a different FTP client? I use Coda for FTP (and development) and once I was able to connect via SFTP using FireFTP I narrowed the issue down to Coda. Which leads me to troubleshooting option 4.
  • Have you restarted your FTP client? This is what burned me today. Once I restarted Coda I was able to connect via SFTP. Something was cached or confused. Honestly. You’re killing me Smalls.

Well that was 2 hours well spent wasted.

Posted in: Development  |  Tagged with: , , ,  |  Leave a comment

Launched: Trivial Beersuit (for All Your Trivia Night Needs)

Trivial Beersuit Screen GrabFor the past few months I have been working on a subscription-based site that provides monthly custom curated trivia questions to bars and other establishments.

It all began last summer at a friend’s wedding where I met the woman behind the trivia, the trivia girl herself, Katie Christofferson. Since 2010, Katie has been writing trivia questions for a number of bars across Montana, Idaho and Oregon. Initially, her whole business was run by snail mail. Once the trivia questions were written she would mail them on CDs to her clients. As you can imagine, this was a cumbersome process to say the least. If there were any issues or updates, she would have to do the entire snail mail process over again.

Katie’s vision was to develop a user-friendly system where her clients would log in and download a new trivia package each month. Once signed up they would get the first month free, then to maintain monthly access they would need to subscribe to the service.

And that is exactly what we built.

With this system, making updates is a breeze and as easy as uploading a file. If any bar has a problem, Katie can update the affected accounts with new trivia.

So without further adieu Katie and I are pleased to announced we’ve launched her new custom curated trivia service: Trivial Beersuit.

So go forth, click around, check it out. If you’re a bar looking for this service I highly recommend giving Katie a what up!

Posted in: Development, Portfolio  |  Tagged with:  |  Leave a comment

Most recent work

  • Our Restroom Our Restroom is an (inter)national crowdsourced campaign who’s goal is simple: compel businesses to make their single occupancy restrooms gender neutral. Championed by Kristin Russo, designed by Allison Weiss, built by me on WordPress.
  • How To Win At Feminism The badasses over at Reductress are at it again with their new book How To Win At Feminism.
  • Reductress I crank out on-going web projects for Reductress. Super fun as you can imagine.
  • Everyone Is Gay Everyone Is Gay is an indispensable question and answer resource for the LGBTQ community. Hell, it’s great advice for anyone with a body with an emotion or two. I made the site responsive among other things. Give it a look see.

Need hosting?

  • Media Temple My go to hosting provider. You get rockstar hosting and I make a little cash. No pressure, just clean honest fun.

Past work

  • Christine Chaney Creative The online presence of Seattle artist Christine Chaney. Specializing in architecture, art and apparel.
  • Get Your Shit Together This is a site dedicated to helping you get your shit together before an unexpected tragedy, like the loss of a loved one.
  • IHG Instagram Sweepstakes An awesome Instagram driven sweepstakes site for IHG.
  • Trivial Beersuit A site that provides professional pub trivia to bars and restaurants.
  • Audi YouTube Channel This site was a quick turn-around hair pulling adventure for the Super Bowl: revamping Audi’s YouTube Channel.
  • The new Providing tools to help students become teachers.
  • Valhalla DSP A one man Seattle based company, writing pretty sick professional audio plugins. Check ’em out!
  • Microsoft Visual Studio 2012 Launch This sweet new Visual Studio 2012 Launch website sports a mobile first, responsive web design architecture and comes in 14 different languages.
  • Nice Manners Music Nice Manners is an LA based recording studio offering their clients all sorts of digital audio services. This is a demo of an internal genre based music search & player I built for them. I am ecstatic about launching this project—check it out.
  • Holiday Inn Tumblr A quick, fully responsive update to the Holiday Inn Tumblr page.
  • Microsoft's Art of Touch An artful HTML5 Microsoft website. Sadly, this site is no longer live.
  • Microsoft Hardware Healthy Computing Tool A useful little tool to help you find the perfect Microsoft mouse or keyboard.
  • Pella Professional Pella manufactures high quality doors and windows. This is their professional site.
  • Imaging the World A non-profit dedicated to providing ultrasound services in remote areas of the world.
  • Ginny Ruffner: A not so still life A site for a documentary film about Seattle artist Ginny Ruffner.