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:
- Total numbers of executions of women with details and counts by race and crime.
- Counts of executions of women by state.
- Details of executions of women in Virginia with details and counts by race and crime.
- 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!