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!