Server-Side Scripting/SQL Databases/PHP
index.php
edit<?php
// This program creates and displays a temperature database
// with options to insert, update, and delete records.
//
// References:
// https://en.wikibooks.org/wiki/PHP_Programming
// https://www.php.net/manual/en/book.sqlite3.php
// http://zetcode.com/php/sqlite3/
$DATABASE = "temperature.db";
$FORM = '
<h1>Temperature Data Entry</h1>
<p>Enter country and temperature. Enter again to update.</p>
<p>Enter country without temperature to delete.</p>
<form method="POST">
<p><label for="country">Country:</label>
<input type="text" id="country" name="country" required></p>
<p><label for="stop">Temperature:</label>
<input type="text" id="temperature" name="temperature"></p>
<p><input type="submit" name="submit" value="Submit"></p>
</form>
<hr>
';
main();
function main() {
switch($_SERVER["REQUEST_METHOD"]) {
case "GET":
try {
check_database();
echo $GLOBALS["FORM"];
echo get_data();
} catch (Exception $exception) {
echo $exception->getMessage();
}
break;
case "POST":
try {
$country = $_POST["country"];
$temperature = $_POST["temperature"];
if (!country_exists($country)) {
insert_country($country, $temperature);
} else if ($temperature != "") {
update_country($country, $temperature);
}
else {
delete_country($country);
}
echo $GLOBALS["FORM"];
echo get_data();
} catch (Exception $exception) {
echo $exception->getMessage();
}
break;
default:
echo "Unexpected request method:" . $_SERVER["REQUEST_METHOD"];
break;
}
}
function check_database() {
$sql = "
SELECT COUNT(*) AS Count FROM sqlite_master
WHERE name = 'Countries';
";
$parameters = [];
$rows = sqlite_query($sql, $parameters);
if ($rows[0]["Count"] > 0) {
return;
}
$sql = "
CREATE TABLE Countries(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
Country TEXT UNIQUE NOT NULL,
Temperature REAL NOT NULL);
";
$parameters = [];
sqlite_exec($sql, $parameters);
}
function get_data() {
$sql = "
SELECT ID, Country, Temperature FROM Countries;
";
$parameters = [];
$rows = sqlite_query($sql, $parameters);
$result = "<table><tr><th>ID</th>";
$result = $result . "<th>Country</th>";
$result = $result . "<th>Temperature</th></tr>";
foreach ($rows as $row) {
$result = $result . "<tr><td>" . $row["ID"] . "</td>";
$result = $result . "<td>" . $row["Country"] . "</td>";
$result = $result . "<td>" . $row["Temperature"] . "</td></tr>";
}
$result = $result . "</table>";
return $result;
}
function country_exists($country) {
$sql = "
SELECT EXISTS(
SELECT * FROM Countries
WHERE Country = :country) AS Count;
";
$parameters = [
$country
];
$rows = sqlite_query($sql, $parameters);
$result = !!$rows[0]["Count"];
return $result;
}
function insert_country($country, $temperature) {
$sql = "
INSERT INTO Countries (Country, Temperature)
VALUES(:country, :temperature);
";
$parameters = [
$country,
$temperature
];
sqlite_exec($sql, $parameters);
}
function update_country($country, $temperature) {
$sql = "
UPDATE Countries
SET Temperature = :temperature
WHERE Country = :country;
";
$parameters = [
$temperature,
$country
];
sqlite_exec($sql, $parameters);
}
function delete_country($country) {
$sql = "
DELETE FROM Countries
WHERE Country = :country;
";
$parameters = [
$country
];
sqlite_exec($sql, $parameters);
}
function sqlite_query($sql, $parameters) {
$rows = [];
$database = new SQLite3($GLOBALS["DATABASE"]);
$statement = $database->prepare($sql);
for ($index = 0; $index < count($parameters); $index++) {
$statement->bindValue($index + 1, $parameters[$index]);
}
$result = $statement->execute();
while ($row = $result->fetchArray()) {
array_push($rows, $row);
}
$statement->close();
$database->close();
return $rows;
}
function sqlite_exec($sql, $parameters) {
$database = new SQLite3($GLOBALS["DATABASE"]);
$statement = $database->prepare($sql);
for ($index = 0; $index < count($parameters); $index++) {
$statement->bindValue($index + 1, $parameters[$index]);
}
$result = $statement->execute();
$statement->close();
$database->close();
}
?>
Try It
editCopy and paste the code above into the following free online development environment or use your own PHP compiler / interpreter / IDE.