MySQL Data Manipulation
PHP and MySQL
I completed this assignment for CSIS 2440 Web Programming. Using PHP and MySQL I connected to the database, created a new table, and inserted values into it. Then, using SQL queries, I retrieved data from the table, sorting, grouping, and displaying it on a web page.
Here's the PHP script I wrote to connect to the database and create the stateStats table:
$servername = "208.117.38.17";
$username = "terimotj_root";
$password = "*****";
$dbname = "terimotj_StateStats";
$tbl = "stateStats";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully
";
// Create stateStats table
$query = "CREATE TABLE $tbl ("
."Abbr char(2) NOT NULL,"
."Name varchar(20) NOT NULL,"
."Capital varchar(25) NOT NULL,"
."Population int(11) NOT NULL,"
."TotalSqMiles int(11) NOT NULL,"
."LandSqMiles int(11) NOT NULL,"
."DateOfStatehood date NOT NULL,"
."StateFlower varchar(30) DEFAULT NULL,"
."StateBird varchar(30) DEFAULT NULL,"
."StateTree varchar(30) DEFAULT NULL,"
."PRIMARY KEY (Abbr),"
."UNIQUE KEY Abbr_UNIQUE (Abbr)"
.") COMMENT='State statistics';";
//.") ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='State statistics';";
$return = mysqli_query($conn, $query) or die (mysqli_error($conn));
echo "table $tbl successfully created <br>"; /* message displayed if $query statement successful, otherwise error message returned from $query */
// Insert records into table
$newAuth = array(
array('AL','Alabama','Montgomery',4858979,52419,50744,'1819-12-14','Camellia','Yellowhammer','Southern Pine'),
array('AK','Alaska','Juneau',738432,663267,571951,'1959-01-03','Forget-me-not','Willow Ptarmigan','Sitka Spruce'),
array('AZ','Arizona','Phoenix',6828065,113998,113635,'1912-02-14','Saguaro cactus blossom','Cactus Wren','Paloverde'),
array('AR','Arkansas','Little Rock',2978204,53179,52068,'1836-06-15','Apple blossom','Mockingbird','Pine'),
array('CA','California','Sacramento',39144818,163696,155959,'1850-09-09','California poppy','California Valley Quail','California Redwood'),
array('CO','Colorado','Denver',5456574,104094,103718,'1876-08-01','Rocky Mountain columbine','Lark Bunting','Blue Spruce'),
array('CT','Connecticut','Hartford',3590886,5543,4845,'1788-01-09','Mountain laurel','Robin','White Oak'),
array('DE','Delaware','Dover',945934,2489,1954,'1787-12-07','Peach blossom','Blue Hen Chicken','American Holly'),
array('FL','Florida','Tallahassee',20271272,65755,53927,'1845-03-03','Orange blossom','Mockingbird','Cabbage Palmetto'),
array('GA','Georgia','Atlanta',10214860,59425,57906,'1788-01-02','Cherokee rose','Brown Thrasher','Live Oak'),
array('HI','Hawaii','Honolulu',1431603,10931,6423,'1959-08-21','Hawaiian hibiscus','Nene','Kukui'),
array('ID','Idaho','Boise',1654930,83570,82747,'1890-07-03','Syringa, mock orange','Mountain Bluebird','Western White Pine'),
array('IL','Illinois','Springfield',12859995,57914,55584,'1818-12-03','Violet','Cardinal','White Oak'),
array('IN','Indiana','Indianapolis',6619680,36418,35867,'1816-12-11','Peony','Cardinal','Yellow Poplar'),
array('IA','Iowa','Des Moines',3123899,56272,55869,'1846-12-28','Wild prairie rose','Eastern Goldfinch','Oak'),
array('KS','Kansas','Topeka',2911641,82277,81815,'1861-01-29','Sunflower','Western Meadowlark','Cottonwood'),
array('KY','Kentucky','Frankfort',4425092,40409,39728,'1792-06-01','Goldenrod','Cardinal','Yellow Poplar'),
array('LA','Louisiana','Baton Rouge',4670724,51840,43562,'1812-04-30','Magnolia','Eastern Brown Pelican','Baldcypress'),
array('ME','Maine','Augusta',1329328,35385,30862,'1820-03-15','White pine cone and tassel','Chickadee','Eastern White Pine'),
array('MD','Maryland','Annapolis',6006401,12407,9774,'1788-04-28','Black-eyed susan','Baltimore Oriole','White Oak'),
array('MA','Massachussets','Boston',6794422,10555,7840,'1788-02-06','Mayflower','Chickadee','American Elm'),
array('MI','Michigan','Lansing',9922576,96716,56804,'1837-01-26','Apple blossom','Robin','Eastern White Pine'),
array('MN','Minnesota','St. Paul',5489594,86939,79610,'1858-05-11',"Pink and white lady''s slipper",'Common Loon','Red Pine'),
array('MS','Mississippi','Jackson',2992333,48430,46907,'1817-12-10','Magnolia','Mockingbird','Southern Magnolia'),
array('MO','Missouri','Jefferson City',6083672,69704,68886,'1821-08-10','Hawthorn','Bluebird','Flowering Dogwood'),
array('MT','Montana','Helena',1032949,147042,145552,'1889-11-08','Bitterroot','Western Meadowlark','Ponderosa Pine'),
array('NE','Nebraska','Lincoln',1896190,77354,76872,'1867-03-01','Goldenrod','Western Meadowlark','Cottonwood'),
array('NV','Nevada','Carson City',2890845,110561,109826,'1864-10-31','Sagebrush','Mountain Bluebird','Bristlecone Pine'),
array('NH','New Hampshire','Concord',1330608,9350,8968,'1788-06-21','Purple lilac','Purple Finch','Paper Birch'),
array('NJ','New Jersey','Trenton',8958013,8721,7417,'1787-12-18','Purple violet','Eastern Goldfinch','Northern Red Oak'),
array('NM','New Mexico','Santa Fe',2085109,121589,121356,'1912-01-06','Yucca flower','Roadrunner','Pinyon'),
array('NY','New York','Albany',19795791,54556,47214,'1788-07-26','Rose','Bluebird','Sugar Maple'),
array('NC','North Carolina','Raleigh',10042802,53819,48711,'1789-11-21','Flowering dogwood','Cardinal','Pine'),
array('ND','North Dakota','Bismarck',756927,70700,68976,'1889-11-02','Wild prairie rose','Western Meadowlark','American Elm'),
array('OH','Ohio','Columbus',11613423,44825 ,40948,'1803-03-01','Scarlet carnation','Cardinal','Ohio Buckeye'),
array('OK','Oklahoma','Oklahoma City',3911338,69898,68667,'1907-11-16','Mistletoe','Scissor-tailed Flycatcher','Eastern Redbud'),
array('OR','Oregon','Salem',4028977,98381,95997,'1859-02-14','Oregon grape','Western Meadowlark','Douglas fir'),
array('PA','Pennsylvania','Harrisburg',12802503,46055,44817,'1787-12-12','Mountain laurel','Ruffed Grouse','Eastern Hemlock'),
array('RI','Rhode Island','Providence',1056298,1545,1045,'1790-05-29','Violet','Rhode Island Red','Red Maple'),
array('SC','South Carolina','Colombia',4896146,32020,30110,'1788-05-23','Yellow jessamine','Great Carolina Wren','Cabbage Palmetto'),
array('SD','South Dakota','Pierre',858469,77117,75885,'1889-11-02','Pasque flower','Ring-necked Pheasant','White Spruce'),
array('TN','Tennessee','Nashville',6600299,42143,41217,'1796-06-01','Purple iris','Mockingbird','Yellow Poplar'),
array('TX','Texas','Austin',27469114,268581,261797,'1845-12-29','Texas Blue Bonnet','Mockingbird','Pecan'),
array('UT','Utah','Salt Lake City',2995919,84899,82144,'1896-01-04','Sego lily','American Seagull','Blue Spruce'),
array('VT','Vermont','Montpelier',626042,9614,9250,'1791-03-04','Red clover','Hermit Thrush','Sugar Maple'),
array('VA','Virginia','Richmond',8382993,42774,39594,'1788-06-25','American dogwood','Cardinal','Flowering Dogwood'),
array('WA','Washington','Olympia',7170351,71300,66544,'1889-11-11','Coast rhododendron','Willow Goldfinch','Western Hemlock'),
array('WV','West Virginia','Charleston',1844128,24230,24078,'1863-06-20','Rhododendron','Cardinal','Sugar Maple'),
array('WI','Wisconsin','Madison',5771337,65498,54310,'1848-05-29','Wood violet','Robin','Sugar Maple'),
array('WY','Wyoming','Cheyenne',586107,97814,97100,'1890-07-10','Indian paintbrush','Western Meadowlark','Cottonwood'),
array('DC','District of Columbia','Washington',672228,68,61,'1791-09-09','American Beauty rose','Wood thrush','Scarlet oak'),
);
//print_r($newAuth); /* this statement prints the contents of the array variable $newAuth */
echo "<hr><br><h2>Records inserted</h2><hr>"; /* display heading */
foreach ($newAuth as $insertArray) { /* for loop used to insert each row of array data into table */
$query = "INSERT INTO $tbl (`Abbr`, `Name`, `Capital`, `Population`, `TotalSqMiles`, `LandSqMiles`, `DateOfStatehood`, "
. "`StateFlower`,`StateBird`,`StateTree`) VALUES ('$insertArray[0]', '$insertArray[1]', '$insertArray[2]', '$insertArray[3]',"
. " '$insertArray[4]', '$insertArray[5]', '$insertArray[6]', '$insertArray[7]', '$insertArray[8]', '$insertArray[9]');"; /* variable $query to hold SQL statement */
$return = mysqli_query($conn, $query) or die (mysqli_error($conn));
echo "$insertArray[1] was added
"; /* display each record added to table */
}
// Close the connection
$conn->close();
Once the table was created, here's the PHP script I ran utilizing SQL queries to retrieve specific data and display it on the web page:
$servername = "208.117.38.17";
$username = "terimotj_root";
$password = "*****";
$dbname = "terimotj_StateStats";
$tbl = "stateStats";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully<br>";
//Largest population
$query = "SELECT * FROM $tbl WHERE Population=(SELECT MAX(Population) FROM $tbl)";
$success = $conn->query($query);
$return = mysqli_query($conn, $query) or die (mysqli_error($conn));
$row= mysqli_fetch_array($return);
echo "<p><b><em>State with max population: </b></em>" . $row['Name'] . "</p>";
// Order by descending population
$query = "SELECT * FROM $tbl ORDER BY Population DESC";
$success = $conn->query($query);
echo "<b><em>States ordered by descending population:</em></b><br>";
if ($success->num_rows > 0) {
// output data of each row
echo "<table><tr><th>State</th><th>Population</th></tr>";
while($row = $success->fetch_array()) {
echo "<tr><td>".$row['Name']."</td><td>".$row['Population']."</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
//Lowest population, excluding DC
$query = "SELECT * FROM $tbl WHERE Population=(SELECT MIN(Population) FROM $tbl)";
$success = $conn->query($query);
$return = mysqli_query($conn, $query) or die (mysqli_error($conn));
$row= mysqli_fetch_array($return);
echo "<p><b><em>State with min population: </b></em>" . $row['Name'] . "</p>";
// Order by ascending population
$query = "SELECT * FROM $tbl ORDER BY Population";
$success = $conn->query($query);
echo "<b><em>States ordered by ascending population:</em></b><br>";
if ($success->num_rows > 0) {
// output data of each row
echo "<table><tr><th>State</th><th>Population</th></tr>";
while($row = $success->fetch_array()) {
echo "<tr><td>".$row['Name']."</td><td>".$row['Population']."</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
//State with the closest average population size
$query = "SELECT Name, abs(Population - (select avg(Population) from stateStats)) as Pop_Difference from $tbl order by Pop_Difference";
$success = $conn->query($query);
$return = mysqli_query($conn, $query) or die (mysqli_error($conn));
$row= mysqli_fetch_array($return);
echo "<p><b><em>State with the closest average population size: </b></em>" . $row['Name'] . "</p>";
//State with largest land size
$query = "SELECT * from stateStats order by LandSqMiles DESC";
$success = $conn->query($query);
$return = mysqli_query($conn, $query) or die (mysqli_error($conn));
$row= mysqli_fetch_array($return);
echo "<p><b><em>State with largest land size in square miles: </b></em>" . $row['Name'] . "</p>";
//State with smallest land size in square miles, excluding DC.
$query = "SELECT * FROM $tbl WHERE LandSqMiles=(SELECT MIN(LandSqMiles) FROM $tbl WHERE Name!= 'District of Columbia')";
$success = $conn->query($query);
$return = mysqli_query($conn, $query) or die (mysqli_error($conn));
$row= mysqli_fetch_array($return);
echo "<p><b><em>State with smallest land size in square miles: </b></em>" . $row['Name'] . "</p>";
//State with the closest average land size in square miles.
$query = "SELECT Name, abs(LandSqMiles - (select avg(LandSqMiles) from $tbl)) as Land_Difference from $tbl order by Land_Difference";
$success = $conn->query($query);
$return = mysqli_query($conn, $query) or die (mysqli_error($conn));
$row= mysqli_fetch_array($return);
echo "<p><b><em>State with the closest average land size in square miles: </b></em>" . $row['Name'] . "</p>";
//(Calculate the following for water size) The difference between TotalSqMiles and LandSqMiles is WaterSqMiles.
// State with the largest size in square miles of water.
$query = "select Name, (TotalSqMiles - LandSqMiles) as WaterSqMiles from stateStats Order by WaterSqMiles DESC";
$success = $conn->query($query);
$return = mysqli_query($conn, $query) or die (mysqli_error($conn));
$row= mysqli_fetch_array($return);
echo "<p><b><em>State with the largest size in square miles of water: </b></em>" . $row['Name'] . "</p>";
// State with the smallest size in square miles of water.
$query = "select Name, (TotalSqMiles - LandSqMiles) as WaterSqMiles from stateStats Order by WaterSqMiles";
$success = $conn->query($query);
$return = mysqli_query($conn, $query) or die (mysqli_error($conn));
$row= mysqli_fetch_array($return);
echo "<p><b><em>State with the smallest size in square miles of water: </b></em>" . $row['Name'] . "</p>";
// State with the closest average size in square miles of water.
$query = "SELECT Name, abs((TotalSqMiles - LandSqMiles) - (select avg(TotalSqMiles - LandSqMiles) from stateStats)) as Water_Difference from stateStats order by Water_Difference";
$success = $conn->query($query);
$return = mysqli_query($conn, $query) or die (mysqli_error($conn));
$row= mysqli_fetch_array($return);
echo "<p><b><em>State with the closest average size in square miles of water: </b></em>" . $row['Name'] . "</p>";
//Total US population size
$query = "SELECT SUM(Population) FROM $tbl";
$success = $conn->query($query);
$return = mysqli_query($conn, $query) or die (mysqli_error($conn));
$row= mysqli_fetch_array($return);
echo "<p><b><em>Total US population: </b></em>" . $row[0] . "</p>";
//Total US land size in square miles.
$query = "SELECT SUM(LandSqMiles) FROM $tbl";
$success = $conn->query($query);
$return = mysqli_query($conn, $query) or die (mysqli_error($conn));
$row= mysqli_fetch_array($return);
echo "<p><b><em>Total Land Size in square miles: </b></em>" . $row[0] . "</p>";
//Total US water size in square miles.
$query = "SELECT SUM(TotalSqMiles-LandSqMiles) FROM $tbl";
$success = $conn->query($query);
$return = mysqli_query($conn, $query) or die (mysqli_error($conn));
$row= mysqli_fetch_array($return);
echo "<p><b><em>Total Water Size in square miles: </b></em>" . $row[0] . "</p>";
//Average population size per land square mile for each state.
$query = "SELECT Name, Population / LandSqMiles from $tbl";
$success = $conn->query($query);
echo "<p><b><em>Average population size per land square mile for each state: </b></em></p>";
if ($success->num_rows > 0) {
// output data of each row
echo "<table><tr><th>State</th><th>Pop/land square mile</th></tr>";
while($row = $success->fetch_array()) {
echo "<tr><td>".$row[0]."</td><td>".$row[1]."</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
//Average population size per land square mile for the entire United States.
$query = "SELECT SUM(Population) / SUM(LandSqMiles) FROM $tbl";
$success = $conn->query($query);
$return = mysqli_query($conn, $query) or die (mysqli_error($conn));
$row= mysqli_fetch_array($return);
echo "<p><b><em>Average population size per land square mile for the entire US: </b></em>" . $row[0] . "</p>";
//Average population size per water square mile for each state.
$query = "select Name, Population / (TotalSqMiles - LandSqMiles) FROM $tbl";
$success = $conn->query($query);
echo "<p><b><em>Average population size per water square mile for each state: </b></em></p>";
if ($success->num_rows > 0) {
// output data of each row
echo "<table><tr><th>State</th><th>Pop/water square mile</th></tr>";
while($row = $success->fetch_array()) {
echo "<tr><td>".$row[0]."</td><td>".$row[1]."</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
//Average population size per water square mile for the entire United States.
$query = "SELECT SUM(Population) / (SUM(TotalSqMiles - LandSqMiles)) FROM $tbl";
$success = $conn->query($query);
$return = mysqli_query($conn, $query) or die (mysqli_error($conn));
$row= mysqli_fetch_array($return);
echo "<p><b><em>Average population size per water square mile for the entire US: </b></em>" . $row[0] . "</p>";
//Display the distinct state bird, state tree, state flower.
$query = "SELECT DISTINCT(StateBird) FROM $tbl";
$success = $conn->query($query);
echo "<p><b><em>State birds: </b></em></p>";
if ($success->num_rows > 0) {
// output data of each row
while($row = $success->fetch_array()) {
echo $row[0]."<br>";
}
} else {
echo "0 results";
}
$query = "SELECT DISTINCT(StateTree) FROM $tbl";
$success = $conn->query($query);
echo "<p><b><em>State trees: </b></em></p>";
if ($success->num_rows > 0) {
// output data of each row
while($row = $success->fetch_array()) {
echo $row[0]."<br>";
}
} else {
echo "0 results";
}
$query = "SELECT DISTINCT(StateFlower) FROM $tbl";
$success = $conn->query($query);
echo "<p><b><em>State flowers: </b></em></p>";
if ($success->num_rows > 0) {
// output data of each row
while($row = $success->fetch_array()) {
echo $row[0]."<br>";
}
} else {
echo "0 results";
}
//Number of states having the same state bird, state tree, state flower.
$query = "SELECT StateBird, COUNT(*) FROM $tbl GROUP BY StateBird";
$success = $conn->query($query);
echo "<p><b><em>Number of states with the same state bird: </b></em></p>";
if ($success->num_rows > 0) {
// output data of each row
echo "<table><tr><th>Bird</th><th># of States</th></tr>";
while($row = $success->fetch_array()) {
echo "<tr><td>".$row[0]."</td><td>".$row[1]."</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
$query = "SELECT StateTree, COUNT(*) FROM $tbl GROUP BY StateTree";
$success = $conn->query($query);
echo "<p><b><em>Number of states with the same state tree: </b></em></p>";
if ($success->num_rows > 0) {
// output data of each row
echo "<table><tr><th>Tree</th><th># of States</th></tr>";
while($row = $success->fetch_array()) {
echo "<tr><td>".$row[0]."</td><td>".$row[1]."</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
$query = "SELECT StateFlower, COUNT(*) FROM $tbl GROUP BY StateFLower";
$success = $conn->query($query);
echo "<p><b><em>Number of states with the same state flower: </b></em></p>";
if ($success->num_rows > 0) {
// output data of each row
echo "<table><tr><th>Flower</th><th># of States</th></tr>";
while($row = $success->fetch_array()) {
echo "<tr><td>".$row[0]."</td><td>".$row[1]."</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
//List the order of statehood.
$query = "SELECT name, DateOfStatehood from stateStats order by DateOfStatehood";
$success = $conn->query($query);
echo "<p><b><em>Order of Statehood: </b></em></p>";
if ($success->num_rows > 0) {
// output data of each row
echo "<table><tr><th>State</th><th>Date</th></tr>";
while($row = $success->fetch_array()) {
echo "<tr><td>".$row[0]."</td><td>".$row[1]."</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
// Close the connection
$conn->close();