PHP. How to Create a Contact From Using the SQLiteDB API


SQLiteDB is a cloud hosting service for SQLite database. It allows to use a fully fledged SQL database, without the headache of managing it yourself. SQLIteDB provides a remote API which you may use to execute remotely SQL statements verus your databases.

In this post I am going to show you a quick way create contact form which saves the results in SQLiteDB.

Before you start go login to your account in SQLiteDB and create a new database. Get your new database API URL, API Key.

First, we will set a function which will create a table in our new database, to store the submitted contact information

function createTable($sqlitedbApiUrl, $sqlitedbApiKey) {

    $sql = 'CREATE TABLE "contacts"';
    $sql .= ' (';
    $sql .= ' "Id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,';
    $sql .= ' "FirstName" text NOT NULL,';
    $sql .= ' "LastName" text NOT NULL,';
    $sql .= ' "Email" text NOT NULL,';
    $sql .= ' "Message" text NOT NULL,';
    $sql .= ' "SubmittedAt" text NOT NULL';
    $sql .= ')';

    $callUrl = $sqlitedbApiUrl . '?api_key=' . $sqlitedbApiKey . '&sql=' . urlencode($sql);

    $result = file_get_contents($callUrl);

    // Is it successful?
    if (strpos($result, 'success') !== false) {
        return true;
    } else {
        return false;
    }
}

Second, we are going to create a function which will save the details to the contact table above

function insertRecord($sqlitedbApiUrl, $sqlitedbApiKey, $firstName, $lastName, $email, $message) {
    $firstName = SQLite3::escapeString($firstName);
    $lastName = SQLite3::escapeString($lastName);
    $email = SQLite3::escapeString($email);
    $message = SQLite3::escapeString($message);
    $submittedAt = SQLite3::escapeString(date('Y-m-d H:i:s'));

    $sql = 'INSERT INTO contacts ';
    $sql .= ' (FirstName,LastName,Email,Message,SubmittedAt)';
    $sql .= ' VALUES ';
    $sql .= '(' . $firstName . ',' . $lastName . ',' . $email . ',' . $message . ',' . $submittedAt . ')';

    $callUrl = $sqlitedbApiUrl . '?api_key=' . $sqlitedbApiKey . '&sql=' . urlencode($sql);

    $result = file_get_contents($callUrl);

    if (strpos($result, 'success') !== false) {
        return true;
    } else {
        return false;
    }
}

And here is live example of the code above that you can test:

https://lesichkov.co.uk/apps/sqlitedb-contact-form/