yJuba3Jn

1<?php
2require("connect.php");
3
4// Delete old database, use this script with care. Will remove all non test data
5$deleteDB = "DROP TABLE IF EXISTS $db";
6if ($con->query($deleteDB) === TRUE) {
7    echo "<br>Database $db deleted successfully";
8} else {
9    echo "<br>Error deleting database: " . $con->error;
10}
11
12// Create database
13$createDB = "CREATE DATABASE IF NOT EXISTS $db";
14if ($con->query($createDB) === TRUE) {
15    echo "<br>Database $db created successfully";
16} else {
17    echo "<br>Error creating database: " . $con->error;
18}
19
20$con = new mysqli($servername, $username, $password, $db);
21
22// Invoked for every table
23function createTable($table_name, $connection) {
24    if ($connection->query($table_name) === TRUE) {
25        //echo "<br>Table $table_name created successfully";
26    } else {
27        echo "<br>Error creating table: " . $connection->error;
28    }
29}
30
31// Invoked for every test record added to the table
32function insertRecord($record, $connection) {
33    if ($connection->query($record) === TRUE) {
34        //echo "<br>New record created successfully<br>";
35    } else {
36        echo "<br>Error: " . $record . "<br>" . $connection->error;
37    }
38}
39
40/* 5 TABLES: User, Subscription, VegetablePackage, vegetableList, Vegetable
41  All filled with test data to support the whole system, filling up one package. 
42  Add more comments soon.
43 */
44
45/* CREATE TABLE Subscription AND ADD TEST DATA */
46$subscription_table = "CREATE TABLE IF NOT EXISTS Subscription(
47subscriptionid INT AUTO_INCREMENT,
48uid VARCHAR(45),
49vpid INT,
50subscriptionInMonths INT,
51PRIMARY KEY (subscriptionid),
52FOREIGN KEY (uid) REFERENCES User(userid),
53FOREIGN KEY (vpid) REFERENCES VegetablePackage(vegpackid))";
54
55
56// Fill User table with data
57$subscription_records = "INSERT INTO Subscription (subscriptionInMonths)
58VALUES(12)";
59
60
61$user_table = "CREATE TABLE IF NOT EXISTS User (
62userid VARCHAR(45),
63password VARCHAR(45),
64adminStatus BOOL,
65firstName VARCHAR(45),
66surName VARCHAR(45),
67phone INT,
68email VARCHAR(45),
69address VARCHAR(45),
70country VARCHAR(45),
71zip INT,
72PRIMARY KEY (userid))";
73
74// Tests and runs the variable created above to create table
75
76// Fill User table with data
77$user_records = "INSERT INTO User (userid, password, adminStatus, firstName, surName, phone, email, address, country, zip)
78VALUES('JH002','encryptedpw', TRUE,'Joergen','Hansen', 98823376, 'something@mail.com', 'Superstreet 85c', 'Norway', 4617)";
79
80// Tests and runs the variable created above to add records
81
82
83/* CREATE TABLE VEGETABLEPACKAGES AND ADD TEST DATA */
84$vegetablePackage_table = "CREATE TABLE IF NOT EXISTS VegetablePackage(
85vegpackid INT AUTO_INCREMENT, 
86packageSalesName VARCHAR(255),
87priceForPackageInEuro INT,
88vegList INT,
89PRIMARY KEY (vegpackid),
90FOREIGN KEY (vegList) REFERENCES VegetablePackage(vegListId))";
91
92
93// Fill User table with data
94$vegetablePackage_records = "INSERT INTO VegetablePackage (packageSalesName, priceForPackageInEuro)
95VALUES('Back to the Roots', 29, 0010)";
96
97$vegetableList_table = "CREATE TABLE IF NOT EXISTS VegetableList(
98vegListId INT AUTO_INCREMENT,
99vegetable1 VARCHAR(255),
100vegetable2 VARCHAR(255),
101vegetable3 VARCHAR(255),
102vegetable4 VARCHAR(255),
103vegetable5 VARCHAR(255),
104vegetable6 VARCHAR(255),
105vegetable7 VARCHAR(255),
106PRIMARY KEY (vegListId))";
107
108$vegetableList_record = "INSERT INTO VegetableList ('vegetable1', 'vegetable2', 'vegetable3', 'vegetable4', 'vegetable5')
109VALUES('Potato', 'Sweetpotato', 'Carrot', 'Pear', 'Lemon')";
110
111createTable($user_table, $con);
112insertRecord($user_records, $con);
113
114createTable($vegetableList_table, $con);
115insertRecord($vegetableList_record, $con);
116
117createTable($vegetablePackage_table, $con);
118insertRecord($vegetablePackage_records, $con);
119
120createTable($subscription_table, $con);
121insertRecord($subscription_records, $con);
122
123$con -> close();
124
125?>