Getting Answers from Your Data

My assignment this week was was to run the queries I “designed” last week. (*I am, of course, using the term “design” very loosely, as last week was full of hand wringing and general PHP consternation.)

This week, I ran 4 total queries. I am pretty happy with how they turned out:

  1. Total numbers of executions of women with details and counts by race and crime.
  2. Counts of executions of women by state.
  3. Details of executions of women in Virginia with details and counts by race and crime.
  4. Counts of executions of women by year.

I had hoped not only to pull results, but also to display them in a way that made sense. The two biggest challenges proved to be pulling details from each execution into a table and displaying the count for individual variables in the state, year, race and crime fields. Because of the nature of what I was trying to learn, I found my PHP code remained relatively simple, whereas my SQL queries got increasingly more complicated.

Tables

I pulled some code from Jeri and from an online tutorial, but when I ran the php, I was creating an individual table for each row of results. By removing the HTML for the table and header rows from the PHP and using generic html tags, I was able to create one large table.

[sourcecode language=”css”]

<p><h2>Details</h2></p>
<p>Details of all the executions of women, in chronological order.</p>
<p><table border=’1′ cellpadding=’10’>
<tr><th>First Name</th><th>Last Name</th><th>Crime</th><th>Method</th><th>State</th><th>Year</th></tr>

<?php

/* Requery # of women total */
$result = mysql_query("SELECT * FROM executions WHERE sex=’F’ ORDER BY executions.year ASC ", $connection);
if (!$result) {
die("Database query failed: ");
}

/* Organize results of Query into a table */
while($row = mysql_fetch_array($result)) {

// set up a row for each record
echo "<tr>";
echo "<td>" . $row[‘first’] . "</td>";
echo "<td>" . $row[‘last’] . "</td>";
echo "<td>" . $row[‘crime’]. "</td>";
echo "<td>" . $row[‘method’]. "</td>";
echo "<td>" . $row[‘state’]. "</td>";
echo "<td>" . $row[‘year’]. "</td>";
echo "</tr>";

}

?>

</table></p>

[/sourcecode]

Counts and Group By

The other queries were created with complex mysql queries. A fun little function in PHPMyAdmin “show PHP” allowed me to see how the query worked. From there I was able to figure out how to phrase the query and the order in which it should appear. To be honest, this was a long process of trial and error, but once I got the syntax, it is easy to cut and paste and adapt for all of my complex queries.

Thanks to Laura for showing me how to phrase my search, I was able to do some Googling to find good examples of successful Count and Grouped queries.

[sourcecode language=”css”]
<?php
/* Query # of women by race in Virginia */
$result = mysql_query("SELECT crime, count(crime) AS crimeCount FROM executions WHERE sex=’F’ AND state=’VA’ GROUP BY crime ORDER BY crimeCount DESC", $connection);
if (!$result) {
die("Database query failed: ");
}

/* Answer */
while($row = mysql_fetch_array($result)){
echo ($row[‘crime’]) . ": " . ($row[‘crimeCount’]) . "<br />";

}
?>
[/sourcecode]

I was surprised by several of the results that came back. I’m now really looking forward to visualizing these results!

Asking Questions of Your Data

Now that my data is normalized and I have a good idea of what is contained in my database, it’s time to start asking questions of my data. What do I want to know? Which queries are only for me? Which would I like to embed into a web page? While they don’t seem like tough questions, it is beginning to dawn on me that my issues with PHP may be more than just a learning curve. It isn’t so much the learning of the language (although, I am struggling with that), it’s also the fact that I don’t sit down and think about what I want it to do BEFORE I start coding.

I dive right in, copy examples and start changing variables and functions without even thinking about what I want it to do, let alone what this code snippet was programmed to do. The maddening part of PHP is that it’s so personal and subjective–each coder has written into the code their own logic and language. What is mine?

<crickets>

Honestly, I don’t know. I do know the questions I want to ask.

  1. How many women were executed in the United States?
  2. For what crimes? (Cluster the crimes)
  3. In what states? (Cluster both the state numbers AND the crimes by state)
  4. Are there any moments in time where there were an abnormally large group of women executed?
  5. Are there any states in which more women were executed over others?
  6. What of Virginia? What happened here?
  7. What overall trends can I see?
  8. What is missing?

Some of these questions are really specific and easy to both code and show. Others are not. All of this is to say that I’m still working on figuring this out. I admit that this “paralysis by analysis” affected my productivity this week. My output wasn’t what I had been hoping. Still, this is all part of the process, right? Right?

PHP Tango

Oh PHP. How you vex me.

Our assignment this week was to play with PHP, specifically to create a working data entry form using HTML, PHP and MySQL. I can report that, with very generous help from my Clio class, I did all of that.

My simple, but working form is here.
My simple, but functional PHP code is here.

I’m still not sure I totally understand WHY it works. I am told this is natural and to be expected. That with time and increased familiarity, my comprehension will improve. I look forward to that day. I will continue to plug away at it if you promise me you won’t judge or mock me for doing a happy dance every time I can actually make something work.

Deal?

Adventures in Programming

My third year as a PhD student began this week and I’m excited to be taking both Feminist Philosophy & Theory and Programming for Historians. (There’s something ironic and fun about reading The Second Sex in the same week that I’m learning how to create my own databases from scratch.)

As part of our assignment for Clio 3, as we’re affectionately calling our programming class, we’ll be creating technical tutorials for programminghistorian.org. There’ll be a wide variety of topics from my classmates including, creating databases, PHP, web scraping and theory modeling, among others. I’m on the hook for mapping and data visualization, so I’ll be sure to post links to those when they go up. In the meantime, I’ll be putting my development successes and failures in Sandbox.. for all to see. God help me.