· 9 years ago · Oct 22, 2016, 12:16 AM
1<?php
2include 'vars.php';
3
4//function connect_db connects to a MySQL Database
5function connect_db(){
6 global $servername;
7 global $username;
8 global $password;
9 // Create connection to database
10 //server name, user name, password, databse name
11 $conn = new mysqli($servername, $username, $password, $username);
12
13 //Check connection
14 if($conn->connect_error){
15 die("Connection failed: " . $conn->connect_error);
16 }
17 else{
18 echo @mysql_ping() ? '<pre>True. Connection successfull </pre>' : '<pre> False. Connection Failed. </pre>';
19
20 // sql to create table
21 $sql = "DROP TABLE IF EXISTS Months;";
22 $sql .= "CREATE TABLE Months (
23 month_num INT(6) PRIMARY KEY,
24 month_name VARCHAR(20) NOT NULL,
25 num_days VARCHAR(3) NOT NULL
26 );";
27
28 if ($conn->multi_query($sql) === TRUE) {
29 echo "Table created successfully";
30 } else {
31 echo "Error creating table: " . $conn->error;
32 }
33 }
34
35 $conn->close();
36
37 $conn = new mysqli($servername, $username, $password, $username);
38
39 //Check connection
40 if($conn->connect_error){
41 die("Connection failed: " . $conn->connect_error);
42 }else{
43 $lines=array(); //create an array called lines
44 $fp=fopen('cis475_io.txt', 'r'); //variable that opens a file
45
46 //store the CSV into an array
47 if($fp !==FALSE){ //as long as there is a file
48 while(! feof($fp)){ //as long as we arent at the end of the file
49 $line = fgetcsv($fp,","); //read a line
50 $lines[]=$line; //store it into the array
51 } //repeat
52 }
53 fclose($fp); //close the txt file that was opened
54
55 echo"<br><br><br> Array: <br>";
56
57
58 //Populate table
59 for($x=0;$x<12;$x++){ //this repeats the steps to create a HTML table row 12 times (1 row for each month)
60
61 $sql = "INSERT INTO Months (month_num, month_name, num_days) VALUES (" . $lines[$x][0]. "," . $lines[$x][1]. "," . $lines[$x][2]. ")";
62 }
63
64 if ($conn->query($sql) === TRUE){
65 echo "Table Populated Succesfully";
66 }else{
67 echo "Error populating table: " . $conn->error;
68 }
69
70 echo "<br>";
71 /* This comment explains how I navigate the array
72
73 How the csv is stored in the array
74
75 Array $lists(
76 $count[0] $count[1] $count[3]
77 $row[0] 1, January, 31
78 $row[1] 2, February, 28
79 $row[2] 3, March, 31
80 $row[3] 4, April, 30
81 $row[4] 5, May, 31
82 $row[5] 6, June, 30
83 $row[6] 7, July, 31
84 $row[7] 8, August, 31
85 $row[8] 9, September, 30
86 $row[9] 10, October, 31
87 $row[10] 11, November, 30
88 $row[11] 12, December, 31
89 )
90
91 The csv file is stored in an array. But each row in the csv file creates an array, within an array. So $list[0] is the January row, $lists[1] is the February row, $list[2] os the March row and so on. So $row navigates each "row" in the array.
92
93 $count navigates the column within the row. So $list[$row(0)[$count(0)]] is the first month. $list[$row(0)[$count(2)]] is the name of the month, and $list[$row(0)[$count(3)]] is the number of days in the month
94 */
95
96
97
98
99
100 }
101}//end connect_db
102
103?>