A Few Key Posts

For those who feel 2+ years of a blog qualifies as TL;DR.

Posted in Feelings, myExperience | Leave a comment

Self-Doubt Pays Off

TIL (Theotherday I Learned) That Word “Group” Doesn’t Mean What You Think It Means.

Here’s my schema so you can follow what I was trying to do.


Figure 1. My Schema

My database gathers data I collect using the Spotify Web API on the popularity of artists, albums, and tracks over time. For example, I have a weekly cron task that grabs the popularity of a bunch of artists.

This query’s ultimate purpose is so I can show the latest popularity scores for all albums by a given artist. As pictured below, it shows the artistName, albumName, year (the album was released), and latest popularity score. It needs the albumID for two of the tables it selects from and the artistID to select from two tables. I only want the latest popularity score so it selects from the popAlbums table multiple times.

After several days of trying to get this complex query to work (I kept getting a “mysqli_fetch_array() expects parameter … to be mysqli_result, boolean given in” error), I broke it up into a couple views and incorporated them into this query:


Figure 2. Query with Views Now that I think about it, in retrospect, those scores may not be accurate because maybe that popAlbumsLatest view doesn’t really group them how I want. I’ll have to check. 

It worked fabulously. I was delighted. But I needed to have a PHP variable in a couple spots to generate it dynamically so I couldn’t use views. It still took me a while longer to get the sub-queries to function properly. This is what I came up with. It uses the actual artistID instead of a variable for testing purposes.


Figure 3. Query #1 using subqueries instead of views Stick a pin in that score and date for Trash.

It worked fine in MySQL Workbench (which I’ve fallen in love with along with Sequel Pro for Mac OS) but I’ve seen stuff be fine in my editor and fail with the Terminal, browser, server, etc. so I uploaded that and when it actually worked in the browser from my server—instantly with no error—my first reaction was shock and skepticism.


Figure 4. Results of Query #1 Pay attention to that score for Trash (among others).

Maybe it was pessimism or maybe it was intuition but my first thought was, “That can’t be right.”

The browser showed only one result per album but … still … my gut didn’t accept it.

I ran a query for all (Alice Cooper) results in the popArtists table to see if these were, indeed, the latest results and not … whatever … so I looked for album popularity scores that were different the last time I pulled them from Spotify.


Figure 5. Checking my work All dates except the most recent have pop=63 but Dec 2nd has pop=62

What I saw was that the score was, indeed, incorrect in Figure 3 and Figure 4. But the date was correct (see Figure 3).

“This isn’t going to end the way you think” Luke said but I didn’t listen.

My results were showing the latest date but not the latest popularity score.

Now I was really confused. But fascinated. I had that gleeful feeling I’ve heard scientists get when they say, “THAT’s not supposed to happen!”

Some googling told me Group doesn’t work exactly how you think and, thankfully, one of the solutions was presented and explained in a way that worked for me.

GROUP BY groups the specified column but, apparently, it doesn’t necessarily keep all the other columns with it … you know, like in Excel … or, at least, it doesn’t … I don’t know. It just doesn’t work like most people think it should. I am far from the first to encounter this phenomenon.

I’m still not sure I could explain how the solution works to someone else but it involved yet another sub-sub-sub-query that first groups the results then grabs the row with the … but you’d think that’s what … nevermind … just trust me the solution works.

Here’s what finally worked:


Figure 6. Correct date with correct score. 

And the result


Note several of the scores changed once the query is written* and works correctly.

*It worked just fine … MySQL isn’t broken …

Lastly, I love both MySQL Workbench and Sequel Pro because they allow me to work remotely via ssh getting instant test results (not having to write a file, upload it, test it in the browser, rinse, repeat like I was doing) while not worrying about people attacking, say, an installation of PHPMyAdmin (no matter how securely I’ve locked it down). These apps made this and so much other work go SO much faster so it was so much more enjoyable.

Posted in MySQL, Spotify | Tagged | Leave a comment

From Excel to MySQL Mess

Here are my candidates in Excel:

The CSV file in VS Code:

My import command and … the mess I can’t figure out (and immediately deleted):

WHAT happened?

Attempt #2

I surrounded everything with single quotes and …


Note it created only 16 rows!

Posted in Uncategorized | Leave a comment

Android Desktop App Dev?

Just read there’s a docking station for the Galaxy s8 with which you can use a monitor, keyboard, and mouse for a desktop experience. Hmm … maybe Android app development could be something that would fit in with my future plans for world domination.

Posted in Android | Leave a comment

Google Scholarship

I’ve been revising my essay questions since I first submitted the application almost a month ago. Tomorrow is the deadline so this is my final draft. I really, really hate “tooting my own horn,” job interviews, etc. but I’m posting these here because I feel like they’re my new Mission and Vision statements.

What do you hope to accomplish through this program?

I want my creative groove and mojo back. My field, Instructional Design, used to be the coolest job in the universe. When Flash died, so did my field. With Flash, you were limited only by your imagination. Now, it’s all quasi-neo-powerpoint apps. I need to be stimulated and challenged again.  Programming does that for me. I am, once again, only limited by my imagination. I’d like to do it for a living rather than a hobby.

At work, I redesigned our LMS and automated what little I could access.  I’ve loved making stuff that does stuff – I miss it. I’ve also always loved teaching even moreso – ID was only a means to that end until it became the end.

I’ve volunteered with Coding Dojo, Hour of Code, Code for America and Mil-OSS. I’ll continue giving back, living something I learned from Kahn Academy: “Learn. Create. Teach. Repeat.”

Why do you deserve a scholarship?

This isn’t a hobby or “maybe it will work out” for me. This is what I desperately want and have been pursuing on my own for 17 months spending every spare moment with books, tutorials, meetups, and a healthy combo of Lynda, StackExchange and Reddit. Just added Udemy’s Web Dev Bootcamp course (update: added three more during their Black Friday sale) to my existing studies and side-projects/work to optimistically prepare for this.

The scholarship will not be wasted on me. I am super-low risk. I promise to be the poster-boy of why you give scholarships. You’ll be proud to say I was a recipient.

Also, I could use a little help. I am 48-years-old so I need skills and creds that far exceed people (at least) half my age for any job. This would really, really help. I am a father of three with a student wife (as in she’s finishing college, not like she’s in Wife School) as well so a scholarship would rock our financial world.

If Google and Amazon pick Detroit as their home and this was a springboard to get a job with either (Google would be my first choice, of course), my wife could be convinced to move back home. To Detroit, that is. And I want to go back home. The NY Times just called Detroit “a haven for visionaries and creatives.” Please help me go back where I belong – among visionaries and creatives – even if it means staying in icky Tampa. #MoveTheWorld

Posted in Uncategorized | Tagged , , , , , | Leave a comment

My Second Cron Job

Nothing like waiting until the last minute.

I’ve been getting increasingly frequent “renew your Let’s Encrypt cert” emails but the task kept getting postponed because I didn’t have the command(s) memorized and wanted to create a cron job for it but whenever I thought of it, I didn’t have the time or whatever to look it up or do it.

That’s my excuse.

Yesterday, I received the “your cert expires in 0 days” email and promised myself I’d renew it yesterday.

I didn’t.

This morning, I got up and had a faint hope that it expired at an exact time and not just the date. I checked that last email and the gods were merciful. I had a few hours left. Just renewed it and saw this in bright red amidst the bright green:

Encountered vhost ambiguity when trying to find a vhost for but was unable to ask for user guidance in non-interactive mode. Certbot may need vhosts to be explicitly labelled [sic] with ServerName or ServerAlias directives. Falling back to default vhost *:443 ...

I was concerned, especially because it was a particularly important subdomain, but below that I also saw (in the normal bright green):

Congratulations, all renewals succeeded.

I do want to check into that though. I’ll probably be as quick about that as I was about renewing …

I did, however, create the cron job so I wouldn’t have to stress or worry about it in the future. Things to note:

That tute states, “The certbot Let’s Encrypt client has a renew command that automatically checks the currently installed certificates and tries to renew them if they are less than 30 days away from the expiration date.” DO shows a cron task that tries the renew command every single day so if the cert is within 30 days of expiration, it gets renewed.

I know it’s a tiny little command, but I hate the idea of something happening every single day that doesn’t have to. I think running the command bi-monthly is best (“best” meaning it makes me feel like I’m beating the system) so I thought using * * 31 * * would be super-nifty. I thought months with 31 days are pretty much every other month so it would be perfect and I’m so brilliant.

When I drew it on the whiteboard I saw

  • It would have worked for today (Nov 24) because Oct 31
  • It would work for the next renewal in 90 days on Feb 24 because Jan 31
  • Before it hits another two consecutive renewals successfully (Aug and Nov 2018) it would totally miss May 2018 because April has only 30 days.

This concept could still work if I could get it to attempt renewal on the first day of a month following any month with 31 days.

TIL: You can use If statements in UNIX shell scripts.

For now, so that I can complete this task, we’ll just go with the first day of every month.

I’ll update this post once I write the shell script.

Hmm …

Just typed sudo ctrontab -e and saw what looked like an empty file. That command is what I usually see in cron tutorials. I wonder what it does …?

So, while I file that question away in a drawer I may never check, I’ll just edit the crontab file like I did in My First Cron Job post … as I suspected and hoped, my other command is, indeed, there with my new job now beneath it.


But there are still at least a couple things I want to know.

  • In my first cron job, I indicated a user because one (just one) tute said to and the directions in the file have a column for that. Do I need that? I didn’t put one for my new cron job so we’ll see.
  • In the first job, there’s a command to change directories which makes sense for the file and the scripts in that file. DigitalOcean’s tutorial that inspired my second job/line of code includes /usr/bin/certbot renew but, knowing that I can run certbot from any location, I’m going to see if I can omit that path. The script should run in a few days and I’ll also have a couple more months during which I can experiment.
  • That second command in the job writes the output of renew in a file located at /var/log/le-renew.log but that log file didn’t exist and I haven’t created it. I’m thinking that the command creates the file if it doesn’t already exist. We’ll see in a few days.

I wish I’d noticed where the renew command I did earlier stored any output. The tute states output should have included a line saving debug info to /var/log/letsencrypt/letsencrypt.log and I had already check in that letsencrypt folder to see if le-renew.log was in there. Trying to cd into it as me got me Permission denied and trying as root got me No such file or directory!

So, as I said, we’ll see …

Posted in Security, Site Admin, UNIX | Leave a comment

My First Cron Job(s)

Learning from this great “Automated Tasks” page at CentOS.org

Normally, I prefer stuff like this in a well-written tutorial (see below awesomeness from DigitalOcean) but the above is … wow.

Tangent Sidebar: You know what’s kind of irritating? How I use DigitalOcean documentation all the time — not just because it’s higher in the Google results but it is so much better than the documentation of my actual host. Far more subjects, ridiculously specific, much better written, and far more accurate.

Two cases in point:

The latter is especially useful today because I keep getting emails from Let’s Encrypt reminding me to renew my shizzle.

But, as it turns out, none of those told me exactly what I needed but this did:

How to run PHP scripts from cron jobs

That’s my host. I’m very proud of them. And if their customer support is ever consistently good or consistently “not bad,” I’ll totally apologize. Interestingly, that tute gave me the final piece I needed but if I hadn’t read the other tutes first I’d still have no idea what to do because it only gave the final step, not the first several. That’s my host.

The first three (the good) tutorials taught me how to write a line in the crontab using the columns:

  • minute
  • hour
  • day
  • month
  • dayofweek
  • command

“How to Use Cron …” even gave me the “shortcut” of using @weekly instead of 00**0.

But Newbie: Intro to Cron which I think — sincerely and seriously — is a beautifully designed page, gave me the best tip in telling me to look for the following folders in my /etc directory:

  • cron.daily
  • cron.hourly
  • cron.monthly
  • cron.weekly

Dude. Just drop a script in one of those and viola!

And … and … earlier, one of those tutorials stated, “Cron is a daemon …” and I wondered, “What is a daemon?” That Newbie tute said,

Cron is a daemon, which means that it only needs to be started once, and will lay dormant until it is required. A Web server is a daemon, it stays dormant until it gets asked for a web page. The cron daemon, or crond, stays dormant until a time specified in one of the config files, or crontabs.

I wish cogNiTioN wrote every tutorial on all of the sites and I wish all of the sites were designed like that one.

Okay, so I write a line in my crontab telling it to change directories to where I keep a certain php file, then use php to run that file. Like so …

00 16 * * * user-name cd /my/directory/path; php -q myfile.php

It’s almost 3:30pm now so in a little over half an hour, I’ll check to see if it worked. I’d love that. I’m rather sick of trying to remember to do this particular task.

I had it change directories first because from what I understand, the relative paths in the file would get all confused and fail if the little crontab tried to run the commands from /etc.

If you’re still reading and you care, it’s a script that will go grab popularity scores from Spotify for a bunch of artists I’m tracking.

Another nifty thing I think is interesting is … I did a magical “save as” on the php file I’ve been using for this task and realized, wow, the crontab doesn’t need all of the html, css, jquery, etc. because that’s for me to see so I know the script worked. Not only do I know it worked but the crontab couldn’t care less so I stripped it all out of a new, dedicated file. I also rewrote (or, rather, wrote a new script based on) the script I’ve been using because part of the original added rows to a table that the crontab wouldn’t look at even if it existed. Because the crontab doesn’t have eyes. And doesn’t care.

Posted in CentOS, Linux, Server Admin, Site Admin, Spotify | Tagged | Leave a comment

Filling Empty Cells with Zero in Excel

I had an enormous spreadsheet I’d made by saving a PDF as an Excel workbook and rearranging the data.


Some 2016 presidential election results in Excel

I needed to get the data into MySQL but there were countless empty cells where candidates received no votes and I knew that wouldn’t fly. I needed zeroes.


Select all the empty cells by clicking Find & Select in the Editing section of the Home tab in the spawn-from-Hell ribbon-thing and choosing Go To Special.



Select Blanks and click OK.



Press F2 (function key at the top of your keyboard) to select one of the selected empty cells (using F2 prevents deselecting all the other empty cells).


Type “0” and press either Ctrl+Enter on Windows or Cmd+Return on a Mac.


Behold …


Posted in Tutorials | Tagged , | Leave a comment