bUKEzdTN

1<?php
2// Inserts the CSV data into a mysqli database called “popdb” with
3// username “pop-user” and password “pop-pw”
4$host = 'localhost';
5$user = 'pop-user';
6$pass = 'pop-pw';
7$database = 'popdb';
8
9$db = mysqli_connect($host, $user, $password);
10mysqli_query("use $database", $db);
11
12/********************************************************************************/
13// Parameters: filename.csv table_name
14// Be executable from the command line
15$argv = $_SERVER[argv];
16
17if($argv[1]) { $file = $argv[1]; }
18else {
19    echo "Please provide a file name\n"; exit; 
20}
21if($argv[2]) { $table = $argv[2]; }
22else {
23    $table = pathinfo($file);
24    $table = $table['filename'];
25}
26
27echo "---> Now processing " + $argv;
28
29/********************************************************************************/
30// Get the first row to create the column headings
31
32$fp = fopen($file, 'r');
33$frow = fgetcsv($fp);
34
35foreach($frow as $column) {
36    if($columns) $columns .= ', ';
37    $columns .= "`$column` varchar(250)";
38}
39
40$create = "create table if not exists $table ($columns);";
41mysqli_query($create, $db);
42
43/********************************************************************************/
44// Import the data into the newly created table.
45
46$file = $_SERVER['PWD'].'/'.$file;
47$q = "load data infile '$file' into table $table fields terminated by ',' ignore 1 lines";
48mysqli_query($q, $db);
49
50echo "---> Data inserted into table.";
51
52/********************************************************************************/
53//Performs SQL queries on the inserted data, and then processes the data as
54//necessary to provide the user with the following summary:
55
56#The min value of the POPESTIMATE2014 column
57$minquery = "SELECT MIN FROM POPESTIMATE2014";
58$resultminsqlquery = mysqli_query ($minquery, $db);
59echo '---> Min value for POPESTIMATE2014: ' + $minquery;
60
61#The max value for the POPESTIMATE2013 column
62$maxquery = ("SELECT MAX FROM POPESTIMATE2013");
63$resultmaxsqlquery = mysqli_query ($maxquery, $db);
64echo '---> Min value for POPESTIMATE2014: ' + $maxquery;
65
66#The mean and standard deviation for the POPESTIMATE2012 column
67$meanquery = ("SELECT AVG FROM POPESTIMATE2012");
68$resultmeansqlquery = mysqli_query ($meanquery, $db);
69echo '---> Mean value for POPESTIMATE2012: ' + $meanquery;
70
71echo 'All done.';
72
73?>