Networks & Transactions 1

Monday, March 27

Lab concepts:

Using CocoaMySQL to browse a centralized MySQL database
Querying a MySQL database in PHP and using the results
Adding your own tables (data sets) to a MySQL database, by exporting a CSV file from Excel and importing that into the database using CocoaMySQL

See this page for more info about the content of our specific database and how to connect to it.

Lab corresponds to:

Boudreaux pp 238-255

<html>

<head>
<title>Electricity</title>
</head>

<body>

<?php

// ----- STEP 1. CONNECT TO THE DATABASE

$connection = mysql_connect("ntdb.linkedbyair.net", "yalent", "yalent");
if (!$connection) exit("Could not connect to database.");

$database = mysql_select_db("nt");

// FIGURE OUT WHAT COUNTRY NAME TO SEARCH FOR

$countryName = $_REQUEST["countryName"];

// ----- STEP 2. SELECT RECORDS FROM TABLE

$query = "SELECT country, electr_imports, internet_country_code FROM cia WHERE electr_imports != '' AND electr_imports != '-' AND electr_imports > 0 AND country = '$countryName' ORDER BY electr_imports ASC";

$result = mysql_query($query, $connection);
if (!$result) exit("Could not do query.");

// ----- STEP 3. DISPLAY THE RESULTS ONE ROW AT A TIME

// Figure out how many rows are in this result set
$numRows = mysql_num_rows($result);

// Get one row at a time, that many times
for($i = 1; $i <= $numRows; $i++) {

// Put one row into an associative array, with keys that correspond to the column names
$row = mysql_fetch_assoc($result);

// $row["country"] = "Guernsey"
// $row["electr_imports"] = "0 kWh (2002)"
// $row["internet_country_code"] = ".gg-"

// If there is a valid country code, display the country name as a link to Google in that country
// Check if it's good data by making sure the first character of the country code is "."
if (substr($row["internet_country_code"], 0, 1) == ".") {

// Make sure to use only the first 3 characters of the country code, e.g. ".us"
$countryCode = substr($row["internet_country_code"], 0, 3);

echo "<a href='http://google$countryCode'>";
echo $row["country"];
echo "</a>";

}

// Otherwise if there's not a valid country code in the result row, just display the country name not as a link
else {
echo $row["country"];
}

// Display the electrical imports
echo " ... ";
echo $row["electr_imports"];

// Display a linebreak
echo "<br>\n";

}

?>

</body>

</html>

6b-saudiArabia.csv View in web browser

Date,4-Very favorable,3-Mostly favorable,2-Mostly unfavorable,1-Very unfavorable,5-Don't know,6-Refused,
2/10/2005,3.450,32.530,41.870,15.910,5.400,0.830,
2/12/2004,2.790,24.350,48.500,19.160,5.190,0,
2/6/2003,3.360,27.240,40.590,19.600,8.760,0.450,
2/6/2002,3.710,23.160,42.330,21.700,8.600,0.510,

Lab6