Exporting a Query to CSV From the Command Line

I’ve written about how you can use mysqldump to output specific records before but what if you need to export data from a complex query for a one off report:

SELECT lastName, firstName, count(*)
FROM User
INNER JOIN User_Log on User.id = User_Log.UserId
GROUP BY lastName, firstName;

In order to export this to a CSV file you can tack the following on to the end of you’re query and it will be exported:

INTO OUTFILE '/tmp/data.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Then you just need to download the file and send it to your client.

Link Post and Podcast Roundup: June 2015 Edition

Link: Donald Trump Speech Perfectly Exemplifies How Web Developers Are (De)Valued

Link: A Crusade Against Bad Code

The existence of BAD CODE being copied and pasted by newcomers is probably the biggest source of exploitable security vulnerabilities in the entire industry.

Podcast: Diagnosing Performance using New Relic with Anna Brown

Podcast: Building Community around the XPRIZE with Jono Bacon

Telling MySQL Which Index to Use

**Disclamer: ** I’m sure MySQL database administrators will tell me that this is SUPER wrong because I should always let the database engine pick what indexes to use but this feature exists for a reason. That being said I’m NOT a database administrator I’m just a programmer that plays one on TV.

The other day we ran into an issue where a page would take about 60 seconds to rendering and the problem was tracked back to a single query. Lets say it looked like something like this:

SELECT * 
FROM Table1
INNER JOIN Table2 ON Table2.id = Table1.Table2Id 
WHERE Table2.column = 'true';

When I ran explain on the query I got the following:

screenshot.1433185205

I’ve never seen it use intersection before and I had a index set for all the columns I was using. In MySQL you can force it to use an index:

SELECT * 
FROM column
INNER JOIN Table2 USE INDEX (column_idx) ON Table2.id = Table1.Table2Id 
WHERE Table2.column = 'true';

This simple change took the query from 60 seconds to 0.5.

Link Post Roundup: May 2015 Edition

[SLIDES] WHAT TO EXPECT WHEN YOU’RE EXPECTING: PHP 7 (PHPDAY 2015)

An overview of some of the more important improvements to PHP7. I’m super happy to see the mysql_* functions going away.

How to Design Indexes, Really

An overview of how you should be adding indexes to your MySQL tables.

How many people are missing out on JavaScript enhancement?

Horrible take away from this article:

1.1% of people aren’t getting JavaScript enhancements

I love the way they tested this:

So @tombaromba hacked some code in the GOV.UK homepage (similar to an approach inspired by an experiment Yahoo! Conducted in 2010). We chose this page because of its high volume of traffic and low likelihood of any bias towards a particular user group or demographic.

This code included three images, of which browsers should request two.

First, an image that virtually all browsers would request (the ‘base image’).

And either

  • an image that only browsers executing JavaScript would request (the ‘script image’)
  • an image that only browsers not executing JavaScript would request (the ‘noscript image’)

We deployed this code and then collected the log data from over half a million visits. I expected that number of ‘base image’ requests would closely equal the combined ‘script image’ and ‘noscript image’ requests.

I’m curious what the numbers are for sites I manage…

The God Login

I wish more sites would get behind this:

User identity is always email, plain and simple. What happens when you forget your password? You get an email, right? Thus, email is your identity. Some people even propose using email as the only login method.

Most common git screwups/questions and solutions

This is the scariest one in the bunch:

Remove all local untracked files (and directories) from your local clone

Careful! You might want to take a backup before doing this:

git clean -f -d

The why, what and how of automated static asset pipelines

A good overview of why you should be using a static asset pipelines but it doesn’t go into enough details to actually be helpful. :-)

paragonie/awesome-appsec

A curated list of resources for learning about application security

NisreenFarhoud/Bash-Cheatsheet

The main topics of this cheatsheet include an intro to the shell, navigating around the shell, common commands, environment variables, connectors, piping, I/O redirection, permissions, and keyboard shortcuts.

Podcast Roundup: May 2015 Edition

I’ve been trying to figure out how to communicate Podcasts and links that I found to be informational or interesting but I don’t want to clutter up my site with junk so I’m going to start creating roundups every month so I can still communicate these but not have 100 one link posts.

FLOSS

#335 Taiga.io

This sounds like a really interesting Agile project management system. I’ve created an account and it looks amazing.

This Week In Startups

Episode: 543: LAUNCH Incubator 2: Des Traynor, Cofounder Intercom, on product vision, roadmap, virality, & everything you need to know for “Starting Up”

This is a really interesting presentation about a bunch of topics that are important if you’re creating a project. I’ve listened to it twice and I’ll keep coming back to it. I might even listen to it again and post my notes. It was packed with information.

Sound of Symfony

Episode 7 – Talking about tools

An overview of various tools use in PHP development. I’m always looking for better tools to improve my work.

Episode 8 – Concerning command buses

An interesting overview of command buses.

HANSELMINUTES PODCAST

Accessibility (a11y) with OpenDirective’s Steve Lee

TIL a11y = Accessibility because it starts with an “a”, ends with a “y”, and has 11 characters in between.

Accessibility is one of those things that I think I should do more with but never do. I’m really going to make it a goal to get my stuff together and test my sites for accessibility.

Saving Bletchley Park with Dr. Sue Black

Bletchley Park is where Turing helped create the computer that broke the Enigma. This interview talks to one of the people who was involved in converting it to a mueseum and clears up some inaccuracies in The Imitation Game (a great movie if you haven’t seen it yet)

Diversity Hiring

We just finished hiring a new programmer at Zimco and almost all of the candidates we got were white men (a lot of that is due to our office’s location in Mid-Michigan). It was interesting to hear two separate podcasts that discussed diversity hiring within as many days.

  1. Stack Exchange Podcast #64: Diverse Hiring and a Cat Named Alan Turing
  2. Improving Diversity in Tech with Ashe Dryden & Faruk Ateş

Debugging Facebook Posts

When your users share something on Facebook they get something that looks like this:

Screen Shot 2015-05-30 at 9.31.04 PM

One of my clients had a problem where the text that was showing when sharing a page to Facebook was wrong and I learned two things.

  1. It’s very hard to test this using the normal front end.
  2. Facebook caches information about your page.

The link below give you access to see what Facebook is getting from your site so you can fix any issues and force an update to clear the cache.

https://developers.facebook.com/tools/debug/og/object/

The thing I find most interesting is that Facebook is finding this winking smiley face on the TPT home page:

Screen Shot 2015-05-31 at 2.57.21 PM

Better Know a Library: Faker

When you’re developing websites it’s very easy to create a small subset of data so you can test but there are some performance issues and bugs that will only rear their ugly head when you have a large amount of data. It’s very difficult to create this test data and have unique values set. You could just fill your database with random letters and numbers (substr(sha1(gmdate('U'))), 0, 10) but it’s better to have nice looking data if you need to demo changes.

The Faker library provides a way to generate fake but real looking data for your test environment.

Continue reading