Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
411 lines (346 sloc) 13.2 KB
<?php
function customPageHeader() {
print("\n\t\t" . '<link rel="stylesheet" href="assignment5.css">');
print("\n\t\t" . '<script src="/js/jquery-3.3.1.min.js"></script>');
}
function customMetadata() {
print("\n\t\t" . '<!--Custom Metadata-->');
print("\n\t\t" . '<meta name="description" content="Assignment 5 for CSCI3000 Web Development Class. Named: MySQL Assignment">');
print("\n\t\t" . '<meta name="keywords" content="HTML,CSS,PHP,Javascript,JQuery,MySQL">');
print("\n\t\t" . '<!--End Custom Metadata-->');
}
$loadPage = new loadPage();
$sqlCommands = new sqlCommands();
$mainPage = new homeworkAssignmentFive();
$loadPage->loadHeader();
$mainPage->printSourceCodeLink();
$mainPage->printArchiveLink();
//$mainPage->printWarning();
$sqlCommands->setLogin(getenv('alex.server.phpmyadmin.host'),
getenv('alex.server.phpmyadmin.username'),
getenv('alex.server.phpmyadmin.password'),
getenv('alex.server.phpmyadmin.database'));
$sqlCommands->testConnection();
$sqlCommands->connectMySQL();
$sqlCommands->createTable();
$mainPage->checkMySQLValues();
$mainPage->printMySQLData();
$mainPage->checkValues();
$mainPage->printForm();
//$mainPage->printArchiveLink();
$loadPage->loadFooter();
class sqlCommands {
private $server, $username, $password, $database;
public function setLogin($server, $username, $password, $database) {
$this->server = $server;
$this->username = $username;
$this->password = $password;
$this->database = $database;
}
public function testConnection() {
if($this->server === NULL || $this->username === NULL || $this->password === NULL || $this->database === NULL) {
print("<p>Sorry, but you are missing a value to connect to the MySQL server! Not Attempting Connection!!!</p>");
die();
}
$return_response = $this->connectMySQL();
if(gettype($return_response) === "string") {
print("<p>Connection to MySQL Failed: " . $return_response . "! Not Attempting Connection!!!</p>");
die();
} else {
print("<p>Connected to MySQL Successfully!!!</p>"); //object
}
}
public function connectMySQL() {
try {
$conn = new PDO("mysql:host=$this->server;dbname=$this->database", $this->username, $this->password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $conn;
}
catch(PDOException $e) {
return $e->getMessage();
}
}
public function createTable() {
try {
$conn = $this->connectMySQL();
// https://stackoverflow.com/a/8829122/6828099
$checkTableSQL = "SELECT count(*)
FROM information_schema.TABLES
WHERE (TABLE_SCHEMA = '$this->database') AND (TABLE_NAME = 'Assignment5')
";
/*
First Name:
Last Name:
Color:
Hot Food:
Cold Food:
*/
// https://stackoverflow.com/questions/1262174/mysql-why-use-varchar20-instead-of-varchar255
// lastname VARCHAR(30) NOT NULL,
// https://www.eversql.com/sql-syntax-check-validator/ - Validate's SQL syntax
// https://wtools.io/generate-sql-create-table - SQL Syntax Generator
// http://sqlfiddle.com/ - Not Checked Out Yet
$sql = "CREATE TABLE Assignment5 (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname TEXT NOT NULL,
lastname TEXT NOT NULL,
color TEXT NOT NULL,
food TEXT NOT NULL
)";
$tableExists = false;
// http://php.net/manual/en/pdo.query.php
foreach ($conn->query($checkTableSQL) as $row) {
if($row['count(*)'] > 0)
$tableExists = true;
}
if(!$tableExists) {
// use exec() because no results are returned
$conn->exec($sql);
}
} catch(PDOException $e) {
//echo $sql . "<br>" . $e->getMessage();
echo "<p>Create Table Failed: " . $e->getMessage() . "</p>";
}
}
public function insertData($fname, $lname, $color, $food) {
try {
$conn = $this->connectMySQL();
$statement = $conn->prepare("INSERT INTO Assignment5 (firstname, lastname, color, food)
VALUES (:fname, :lname, :color, :food)");
$statement->execute([
'fname' => $fname,
'lname' => $lname,
'color' => $color,
'food' => $food,
]);
} catch(PDOException $e) {
echo "<p>Insert Data into Table Failed: " . $e->getMessage() . "</p>";
}
}
public function readData() {
try {
$conn = $this->connectMySQL();
// http://php.net/manual/en/function.htmlspecialchars.php
//$sql = "SELECT * FROM Assignment5"; // Display Everything
$sql = "SELECT * FROM Assignment5 ORDER BY id DESC LIMIT 10"; // Limit to Last 10 Entries (Reverse Order) - https://stackoverflow.com/a/14057040/6828099
foreach ($conn->query($sql) as $row) {
print("
<tr>
<td>" . htmlspecialchars($row['id'], ENT_QUOTES, 'UTF-8') . "</td>
<td>" . htmlspecialchars($row['firstname'], ENT_QUOTES, 'UTF-8') . "</td>
<td>" . htmlspecialchars($row['lastname'], ENT_QUOTES, 'UTF-8') . "</td>
<td>" . htmlspecialchars($row['color'], ENT_QUOTES, 'UTF-8') . "</td>
<td>" . htmlspecialchars($row['food'], ENT_QUOTES, 'UTF-8') . "</td>
</tr>");
}
} catch(PDOException $e) {
echo "<p>Read Data from Table Failed: " . $e->getMessage() . "</p>";
}
}
}
class homeworkAssignmentFive {
public function printArchiveLink() {
//print('<a href="archive" style="text-align: center;display: block">Go to Archived Homework Assignment 5</a>');
print('<a href="' . getenv('alex.github.project') . '/commit/f3a53a91d1b7a2d94b2c5fab87b822a449a1cdad#diff-7b64b33e114a6c006a65e1d57fdfd8c2" style="text-align: center;display: block">Go to Archived Homework Assignment 5 Source Code</a>');
//print('<br>');
}
public function printSourceCodeLink() {
print('<a class="source-code-link" href="' . getenv('alex.github.project') . '/tree/' . getenv('alex.github.branch') . $_SERVER['SCRIPT_NAME'] . '">View Source Code</a><br>');
}
public function printWarning() {
print('<h1>Assignment 5 has not been created yet! Please come back later!</h1>');
}
public function checkValues() {
if(!empty($_POST)) {
//$this->verifyMySQLVars();
$this->printData();
}
}
public function checkMySQLValues() {
if(!empty($_POST)) {
/* I am intentionally separating this from checkValues(),
* so I can insert data into the database before I read the database
*/
$this->verifyMySQLVars();
}
}
public function verifyMySQLVars() {
// I could refuse to add this to MySQL if the value is not set. It is not set up this way though.
$fname = $this->getValue('first_name');
$lname = $this->getValue('last_name');
$color = $this->getValue('color');
$food = $this->getValue('food');
//$sqlCommands = new sqlCommands(); // I cannot set this unless I want to specify the auth multiple times.
global $sqlCommands;
$sqlCommands->insertData($fname, $lname, $color, $food);
}
public function getValue($value) {
$return_me = '';
print("<script>");
if(isset($_POST[$value]) && $_POST[$value] !== '') {
/* Experimenting Around with Keeping Form Options Selected After Submit */
/*print('
$(document).ready(function() {
$(".' . $value . '").val("red");
});
');*/
$return_me = $_POST[$value];
} else {
print('
$(document).ready(function() {
$("label.form-label-' . $value . '").css("color","red");
$("label.form-label-' . $value . '").text("Missing " + $("label.form-label-' . $value . '").text());
});
');
// This breaks the radio labels, but the only way that the option will be missing a value is if the user manually edits it out. E.g. Developer Tools
$return_me = "Not Set";
}
print('</script>');
return $return_me;
}
public function printMySQLData() {
try {
global $sqlCommands;
$conn = $sqlCommands->connectMySQL();
// This allows me to determine if table is empty (or even exists)
$sql = "SELECT id FROM Assignment5 LIMIT 1";
$tableExists = false;
foreach ($conn->query($sql) as $row) {
$tableExists = true;
}
} catch(PDOException $e) {
print($e->getMessage());
}
if($tableExists) {
print('
<fieldset>
<legend>Last 10 MySQL Entries</legend>
<table>
<thead>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Color</th>
<th>Food</th>
</tr>
</thead>
<tbody>');
global $sqlCommands;
$sqlCommands->readData();
print('
</tbody>
</table>
</fieldset>');
}
}
public function printData() {
print('
<fieldset>
<legend>Post Data</legend>
<table>
<thead>
<tr>
<th>Form Item Name</th>
<th>Form Item Value</th>
</tr>
</thead>
<tbody>
<tr>
<td>First Name</td>
<td>' . $this->getValue('first_name') . '</td>
</tr>
<tr>
<td>Last Name</td>
<td>' . $this->getValue('last_name') . '</td>
</tr>
<tr>
<td>Color</td>
<td>' . $this->getValue('color') . '</td>
</tr>
<tr>
<td>Food</td>
<td>' . $this->getValue('food') . '</td>
</tr>
</tbody>
</table>
</fieldset>');
}
public function printForm() {
print('
<fieldset>
<legend>Example Form</legend>
<div class="form">
<form method="post">');
if(isset($_POST['first_name']) && $_POST['first_name'] !== '') {
print('
<label class="form-label-first_name">First Name: </label><input name="first_name" type="text" value="' . $_POST['first_name'] . '"><br>
');
} else {
print('
<label class="form-label-first_name">First Name: </label><input name="first_name" type="text"><br>
');
}
if(isset($_POST['last_name']) && $_POST['last_name'] !== '') {
print('
<label class="form-label-last_name">Last Name: </label><input name="last_name" type="text" value="' . $_POST['last_name'] . '">
');
} else {
print('
<label class="form-label-last_name">Last Name: </label><input name="last_name" type="text">
');
}
print('
<br><br>
<label class="form-label-color">Pick a Color: </label>
<select id="option-color" name="color">');
if(isset($_POST['color']) && $_POST['color'] !== '') {
print('
<script>
$(document).ready(function() {
$("#option-color option[value=' . $_POST['color'] . ']").prop("selected", true)
});
</script>
');
}
print('
<option value="red">Red</option>
<option value="green">Green</option>
<option value="blue">Blue</option>
</select>
<br><br>');
print('
<!--<label>Hot Food: </label><input name="food" value="hot_food" type="radio" checked><br>-->
<label class="form-label-food">Hot Food: </label><input id="radio-hot-food" class="radio-food" name="food" value="hot-food" type="radio" checked="checked"><br>
<label class="form-label-food">Cold Food: </label><input id="radio-cold-food" class="radio-food" name="food" value="cold-food" type="radio">
');
if(isset($_POST['food']) && $_POST['food'] !== '') {
print("
<script>
$(document).ready(function() {
$('#radio-" . $_POST['food'] . "').attr('checked', true);
});
</script>
");
}
print('
<br><br>
<input type="submit">
</form>
</div>
</fieldset>');
}
}
class loadPage {
public function loadHeader() {
$PageTitle="Assignment 5 - MySQL";
$root = isset($_SERVER['PWD']) ? $_SERVER['PWD'] : $_SERVER['DOCUMENT_ROOT'];
include_once($root . "/server-data/header.php");
}
public function loadFooter() {
$root = isset($_SERVER['PWD']) ? $_SERVER['PWD'] : $_SERVER['DOCUMENT_ROOT'];
include_once($root . "/server-data/footer.php");
}
}
?>
You can’t perform that action at this time.