Updates for SQL + PHP in this course

This course is divided into three parts: databases with PHP, web scraping with Python, and using a web framework, Flask (also Python). I started teaching this course in January 2016. Of course, I had done a ton of research before starting, sitting down with and emailing numerous journalism practitioners of code.

After the second offering of this course concluded in April 2017, I resolved to take time during the summer to revisit, re-evaluate, and revise as needed.

I started with the intro-to-Python materials, which don’t come into the course until Week 5. I very much wanted to replace Learn Python the Hard Way as the base for learning Python. I tried using Head First Python in class, but it was a disaster. It’s just not a good book for beginners. Luckily, I discovered Automate the Boring Stuff with Python, and I couldn’t be happier! I’ll write more about that in a future post.

Much as I would have liked to just work on Python all summer, I had to go back to the first segment, databases with PHP, and get that organized.

Learning objectives

Inspired by a couple of articles by my friend Erika Biga Lee, I wanted to specify what I wanted the students to be able to do as a result of this four-week segment. The use of learning outcomes in teaching is intended to focus the instruction, assignments, and tests or quizzes on what you actually want the students to learn. This means not a big, vague idea like “I want them to be able to make web databases for journalism projects” (although that is the broad, final outcome I want) but rather the individual components that lead to that in the end.

Here are the atomic-level learning objectives for weeks 1–4. Links are included.

SQL

SQL (Structured Query Language) is a language for getting answers from your database. It’s an elegantly simple, rigid language with the power to extract very precise results across multiple tables. Since I’m not teaching data analysis in this course, I really just need the students to understand how to insert a new record and select records based on ID. However, I want them also to get a glimpse of the full power of SQL in case they do go on to need more sophisticated queries in the future. (Check out this 2-minute video.)

Databases

Most of the students have used a spreadsheet, but pretty much no one has seen database structure before. Database architecture is important to all kinds of journalism nowadays, so I want my students to have hands-on experience building a database from scratch. I want them to understand how field names are used as variables to search for and extract data from a table. I want them to be able to organize data in a table. I want them to have a glimpse of multiple tables interacting in one database, but I don’t need them to understand joins.

To make all this less abstract, I want them to have visual access to the database and its tables. I want them to see the grid as columns and rows and recognize how it differs from a spreadsheet.

I’m aware of the whole NoSQL movement, but since SQL queries are so useful in data journalism, I really want the students to get their feet wet with SQL.

XAMPP

My broad aims for SQL and databases led me to XAMPP, which has the advantages of being free and working on Windows as well as Mac OS. It very painlessly enables students to install a fully functional web server on their own laptops, and the server is already configured with a MySQL database server and PHP, a general-purpose scripting language that’s available by default on almost every web server in the world.

PHP

I have no great love for PHP, and in fact, I spent some time this summer trying to come up with a reasonable way to eliminate it from this course. However, in the end, I kept it. In spite of its age, it still offers the easiest way to whip up a script to process a web form (on most web servers) and return feedback to the user. I’ve Googled a lot and decided it’s still relevant. Plus, it’s already set up and ready to roll as soon as you’ve installed XAMPP — which is as clean an install as you could ever hope to find.

HTML and CSS

My students already spent a full semester in a course about front-end web development, but they are challenged in this SQL-PHP-database segment to review and revisit things they probably have not mastered, including properly constructed HTML forms, and HTML tables. At this point, though, they should not need me to hold their hand and make detailed instruction sheets about how to use form and table elements in their markup.

In that previous course, they already set up their own domain at a shared-hosting company, Reclaim Hosting (which is great for students and educators). Since Reclaim servers have the same technology stack as XAMPP, we don’t have much trouble transferring our database apps from our laptops to the live web.

Projects

Students complete two projects in this segment of the course. The first is a shoutbox, for which I give them all the code. They have to set up XAMPP and build one database table. They have to get it working on XAMPP, and then for a grade they have to move it to their own hosted live site.

A big part of what I needed to revise in this part of the course was how to teach about the jQuery Ajax code used in both projects. Last spring, I realized students were really unclear on the role played by this code in the shoutbox project, so I spent a lot of time Googling and thinking about how to fit that in. I ended up spending about two weeks on Progressive Web Apps as a result of that, but in the end I decided to stick with jQuery Ajax for now, and I created a new slide deck to help clarify it.

The second project is a build-from-scratch database app — although I’ve given them all the PHP code they need to build it. It was important to ensure that students understand very well the dangers posed by exposing any SQL database to attacks by malicious hackers. Even though with a journalism project we’re not exposing sensitive data (unlike banks, and any site that stores your credit card information), if you’re careless with your PHP commands for SQL, someone could delete your entire database when they submit a web form.

This led to another major revision of the materials I had developed for this course. As I reviewed them and slowly worked out my atomic-level learning objectives for weeks 1–4, I realized that my GitHub repo included too many files, and my explanations of the code were too long and too mixed up — a result of writing the detailed instructions as I was deciding what to teach and learning new things myself. So I had to thoroughly rewrite two handouts, separating out the explanation of using SQL prepared statements to prevent injection attacks from the explanation of how the files in the app interact with one another (including even more about jQuery Ajax).

You can find all my materials linked in that learning objectives document.

Feel free to copy and use anything here in your own journalism courses. Commercial and for-profit use of my materials is not granted or authorized.

If you know of ways I can improve anything here, please contact me through Twitter (@macloo) or Gmail.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s