Server-Side Scripting/SQL Databases/PHP

index.php

<?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

Copy and paste the code above into the following free online development environment or use your own PHP compiler / interpreter / IDE.

• PHPFiddle