Simple PHP Data Storage

The subject of my post today is about a database schema I often to use for websites. To make things a bit more practical I present some PHP code snippets detailing how I often store and retrieve form data, which is a very common task when developing custom websites.

But first, let's talk databases. There are exceptions to every rule, but my default data storage/retrieval library is SQLite. I've used it for desktop applications, embedded smartphone applications, as well as for websites. I've accessed it from a number of languages. In case you are not familiar with this RDBMS:
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.
From SQLite's When To Use page:
The basic rule of thumb for when it is appropriate to use SQLite is this: Use SQLite in situations where simplicity of administration, implementation, and maintenance are more important than the countless complex features that enterprise database engines provide. As it turns out, situations where simplicity is the better choice are more common than many people realize.
Another way to look at SQLite is this: SQLite is not designed to replace Oracle. It is designed to replace fopen().
And later from the same link:
SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). The amount of web traffic that SQLite can handle depends, of course, on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.
Second, let's talk frameworks. There are a myriad of web application frameworks we can use to help us develop our websites. In the link above, the list just for PHP-based ones has 17 entries. (The funny thing is, I consider PHP itself to be a "web application framework". A very general one. It is certainly not much a general-purpose programming language.)

IMHO, the reason there are so many frameworks to choose from is because each is only appropriate for it own set of requirements which, apparently, don't overlap as much as one might assume. There may be more sinister reasons. So we need to be very careful and skeptical when choosing a framework.

No one person can be familiar with more than a few of these frameworks, but I'm sure most of the frameworks provide some help in mapping form data to the database and back. But what if we decide not to use a framework?

Finally, the interface between forms and their data storage. The most obvious way is to map a form to a database table. Each column is a field in the form. (Multiple select listboxes are a complication.) Each row in the table is a different user. The only drawback to this, but it's a big one, is that every time one of a form's fields change the database schema has to change.

So I've gotten into the habit of modeling the form data into associative arrays. It's almost as simple as a one-to-one mapping. Yet much more robust of a design. All the forms and all the users can go into one table if needed. The schema?
CREATE TABLE ArrayData (  -- all form data can be stored here
    ownerId TEXT,     -- form user
    arrayId TEXT,     -- form Id
    arrayKey TEXT,    -- form field name
    arrayValue TEXT,  -- form field value
    uTime NUMBER      -- date record last modified, PHP microtime(TRUE)
);   

Notice the flexibility. The table's schema never changes no matter how any form changes. The timestamp means old data doesn't have to be deleted from the table unless you need to.

Here is a PHP code snippet I used for interfacing to the database for the last website I built. (A simple, small, internal website.) I cleaned it up a little bit. Notice its schema does not have columns for ownerID or uTime as the more general example above did. They weren't needed for this particular web application. However, there is code for handling multi-valued form items.

/*************************************************************************
 * Simple SQLite DB access functions.
 *
 * Our web pages will need some sort of db, for example, storing form 
 * data. In this case, nothing complex, so set up something easy, 
 * convenient, but not absurdly inefficient or with obvious injection 
 * issues.
 *
 * Example usage:
 *
 *     $form_id = 'test_form';
 *
 *     $form_data = array(
 *         'one' => 'first',
 *         'two' => 'second',
 *         'three' => 'third',
 *         'four' => array(
 *             'fourth',
 *             '4th',
 *             'one less than fifth'
 *         )
 *     );
 *     $multi_valued_items = array('four');
 *     save_form_data($form_id, $form_data);
 *     $new_form_data = fetch_form_data($form_id, $multi_valued_items);
 *     // Note: fetch_form_data($form_id) would return only last value in 
 *     // 'four' array.
 *
 *
 * NOTE: If using the highest level functions (save_form_data and 
 * fetch_form_data) are not appropriate, we can often use the lower level 
 * functions to help access any SQLite 3 database.
 * See open_db, exec_db, fetch_db, and fetchall_db below.
 *
 ***************************************************************************/

// A form's data will be persisted to/from an SQLite database modeled as an 
// associative array.

// Insert/replace the forms data in the database.
function save_form_data($form_id, $form_data, $db_filename='info.sqlite') {
    $db = open_db($db_filename);
    exec_db($db, "CREATE TABLE IF NOT EXISTS ArrayData (" .
        "arrayId TEXT, arrayKey TEXT, arrayValue TEXT)");
    foreach ($form_data as $key => $value) {
        exec_db($db, "DELETE FROM ArrayData " .
            "WHERE arrayId = ? AND arrayKey = ?", array($form_id, $key));
        if (is_array($value)) {
            foreach ($value as $val) {
                exec_db($db, "INSERT INTO ArrayData " .
                    "VALUES (?, ?, ?)", array($form_id, $key, $val));
            }
        } else {
            exec_db($db, "INSERT INTO ArrayData " .
                "VALUES (?, ?, ?)", array($form_id, $key, $value));
        }
    }
}

// If some keys can have multiple values (like a list box) then they must 
// be identified in $multi_values array.
function fetch_form_data($form_id, $multi_values=NULL, 
    $db_filename='info.sqlite') {
    
    $form_data = array();
    $db = open_db($db_filename);
    $results = fetchall_db($db, "SELECT * FROM ArrayData");
    foreach ($results as $value) {
        if ($value['arrayId'] != $form_id) continue;
        $key = $value['arrayKey'];
        if (is_array($multi_values) AND in_array($key, $multi_values)) {
            $form_data[$key][] = $value['arrayValue'];
        } else {
            $form_data[$key] = $value['arrayValue'];
        }
    }
    return $form_data;
}

function open_db($db_filename = 'info.sqlite') {
    try {
        $db = new PDO('sqlite:' . $db_filename);
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (Exception $e) {
        // Hard to see how we can continue in a way meaningful to the user.
        // Log and let user know bad news.
        $e_msg = $e->getMessage();
        $msg = "$db_filename: $e_msg";
        //log_error($msg);
        exit();
    }
    return $db;
}

function exec_db($db, $sql, $parameters=false) {
    try {
        if (!$parameters) {
            $result = $db->exec($sql);
        } else {
            $query = $db->prepare($sql);
            $result = $query->execute($parameters);
        }
    } catch (Exception $e) {
        $msg = $e->getMessage();
        //log_error("EXEC EXCEPTION: $msg"); // try to keep plugging along
        return false;
    }
    return $result;
}

// Return result (one row) of $sql query.
// Returns false rather than an assoc array on failure.
// Since an exception is likely just bad SQL, also returns false on exception.
function fetch_db($db, $sql, $parameters=false) {
    try {
        if ($parameters) {
            $query = $db->prepare($sql);
            if ($query) {
                $query->execute($parameters);
            };
        } else {
            $query = $db->query($sql);
        }
        if ($query) {
            return $query->fetch(PDO::FETCH_ASSOC);
        } else {
            return false;
        }
    } catch (Exception $e) {
        //log_error("SQL: $sql");
        if ($parameters) {
            foreach ($parameters as $param) {
                //log_error("PARAMETER: $param");
            }
        }
        $msg = $e->getMessage();
        //log_error("FETCH EXCEPTION: $msg");
        return false;
    }
}

// Return results (all rows) of $sql query.
function fetchall_db($db, $sql, $parameters=false) {
    try {
        if (!$parameters) {
            $query_results = $db->query($sql);
            if ($query_results) {
                return $query_results->fetchall(PDO::FETCH_ASSOC);
            }
        } else {
            $query = $db->prepare($sql);
            $query->execute($parameters);
            return $query->fetchall(PDO::FETCH_ASSOC);
        }
    } catch (Exception $e) {
        $msg = $e->getMessage();
        //log_error("FETCHALL EXCEPTION: $msg");
        return false;
    }
}

No comments:

Post a Comment