The SQLite Editor for PHP administration tool

• Introduction
• Example
• Download
• Option reference
• Methods
• Search functionality
• License
• FAQs
• Changelog

Introduction

The SQLite Editor for PHP is an administration tool for making interfaces to your SQLite database tables. It's not a full database administration tool however. You cannot use it to add, modify or delete tables, views etc. You wouldn't give such a tool to your users, allowing them to destroy your database! It's a tool that allows the addition, editing and deletion of data in existing tables. Adding, editing and deletion can all be enabled or disabled so you can turn them all off and just allow viewing the data if you wish.

It can be easily integrated into your pages - as you can see from the example below. It's just a single library file which makes it simple to setup and go.

Example

And here's the source-code that produces the editor above.

<?php
    require('SQLiteEditor.php');

    $editor = new SQLiteEditor([
        'filename'     => 'examples/sqliteeditor.db',
        'table'        => 'accounts',
        'sql_select'   => "SELECT id,
                                  username,
                                  created,
                                  forename,
                                  surname
                             FROM accounts
                            WHERE {where}
                                  {order}",
        'sql_add' => false,
        'sql_delete' => false,
        'paging_perpage' => 10,
        'columns_names' => [
            'id'       => 'ID',
            'username' => 'Username',
            'created'  => 'Created',
            'forename' => 'Forename',
            'surname'  => 'Surname'
        ],
        'search_columns' => ['forename','surname'],
        'style' => [
            '.sqliteeditor table tbody tr:hover {background-color: #eee;}'
        ],
        'actions' => [
            '<small><i>(add/edit/delete are disabled on this demo)</i></small>'
        ]
    ]);
    
    $editor->draw();
?>

Download

This is the latest zip file download that contains the PHP file (it's just a single file) and the example pages. Extract this zip file to a directory on to your (PHP enabled) web server and all of the examples should run without any further modification or configuration.

Option reference

• General properties
• SQL properties
• Editing properties
• Columns properties
• Paging properties
• Search properties
• Ordering properties
• Miscellaneous properties

$editor = new SqliteEditor([
    "filename" => "./my.db",
    "table"    => "accounts"
]);

// Possible variations of the path:
// /usr/local/home/web/my.db
// ./my.db
// ../web/my.db
// my.db

'sql_add' => function ($editor)
{
   // Add your logic here
}

UPDATE {table} SET deleted = DATETIME() WHERE {primary_key} IN({ids}))

'sql_delete' => function ($editor, $ids)
{
   // Add your logic here
}

{
  sqliteeditor_action: "save",// Always set to "save"
sqliteeditor_database: ...,   // The database filename
   sqliteeditor_table: ...,   // The table in question
  sqliteeditor_column: ...,   // The column name being edited
   sqliteeditor_value: ...    // The new value
   sqliteeditor_index: ...,   // The value of the primary key
 sqliteeditor_primary: ...    // The name of the primary key field
   
}

'columns_widths' => [
    'id' => 50
]

'columns_tooltips' => [
    'id'       => false,
    'forename' => true,
    'forename' => true
]

'columns_callbacks' => [
    'created' => function ($obj, $row_data, $name, $value)
    {
        if ($value) {
            return date('jS F Y', strtotime($value));
        }
    }
]

'columns_callbacks' => [
    'actions' => function ($obj, $row_data, $name, $value)
    {
        return '<button onclick="location.href=\'account.html?id=' . $row_data['id'] . '\'">View account details</button>';
    }
]

'columns_escape' => [
    'forename' => false,
    'surname'  => false
]

<?php
    require("SQLiteEditor.php");

    $editor = new SQLiteEditor([
        "filename"     => "examples/sqliteeditor.db",
        "table"        => "accounts",
        "search_columns" => ["forename","surname"]
    ]);

    $editor->draw();
?>

'styles' => [
    '.sqliteeditor_button_delete {font-size: 16pt;}',
    '.sqliteeditor_button_add {font-size: 16pt;}',
    '.sqliteeditor button {font-size: 16pt;}',
    '.sqliteeditor input[type=checkbox] {transform: scale(1.5); cursor: pointer;}',
    '.sqliteeditor table tbody tr :where(th, td)[data-column-name=id] {text-align: center;}',
    '.sqliteeditor table tbody tr:hover :not(td.checkbox_table_cell) {background-color: #eee;}'
]

'actions' => [
    ['Hello world!', "alert('Hello world!');"],
]

'actions' => [
    '<button onclick=" alert('Hello world!'); event.preventDefault()">Hello world!</button>'
]

'actions' => [
    ['Hello world!', 'var checked = sqliteeditor_getchecked({id}); alert(checked);'],
    '<button onclick="event.preventDefault(); var checked = sqliteeditor_getchecked({id}); alert(checked);">Hello world!</button>'
]

Methods

These are JavaScript functions that you can call from within the page should you need to.

'actions' => [
    ['Hello world!', 'var checked = sqliteeditor_getchecked({id}); alert(checked)'],
    '<button onclick="event.preventDefault();var checked = sqliteeditor_getchecked({id}); alert(checked)">Hello world!</button>'
]

Search functionality

The search functionality was originally based on a LIKE condition added to the SELECT query which translated to a simple wildcard search - finding whatever you typed in if it was part of the row.

From version 1.0b3, however, the search has been converted to a GLOB clause which results in a few extra options that are available to you when searching.

There are two wildcard characters available to you and they are:

• * Matches any number of any character
• ? Matches a single character (again, any character)

Asterisks are added to both ends of the term(s) that you search for. So a search for cde would find the word abcdefgh. Also, you can add a ! to the start of a word or sequence of characters to negate it.

So a search for the phrase john !Barry would find rows that contain the word john but not with the word barry.

Character classes

Similar to regular expressions you can use square brackets - [] - to define a range of characters. So the following: [abcdef] would match any of those characters - but only one of them. And you can also negate the character class by adding a caret sign at the start like this: [^abcdef] Remember though that this type of character class only matches a single character and if you negate the class it will match any character that's not in that class.

Negating a whole word

If you want to negate a whole word (so maybe you could search for: big yellow !balloons, which would look for rows that contain the words big and yellow but not the word balloons, then you can prefix an exclamation point to that word.

Examples

License

The SQLiteEditor tool has the same license as SQLite itself - ie it is released to the public domain and you can do with it as you wish.

FAQs

The SQLite Editor for PHP software is released to the public domain without any conditions. This means that you can use it freely, as much as or as little as you want to, royalty-free.

No, but the SQLite-specific parts of the code are minimal so converting it to or adding support for another type of database won't be difficult.

Yes, you can do that by changing the default queries. You would change the sql_delete query to update a "deleted" flag on the row instead of performing a delete query and also change the default sql_select option to one that excludes rows that have that deleted flag set (the default for that column should be null).

Yes, you can do this by selecting an empty column in your query and then using the columns_callbacks option to set the HTML for that column. The sql_select option query that selects data for the editor would look like this (the columns_escape option is also set here for this column so the HTML appears instead of the HTML code itself):

'sql_select'   => "SELECT id,
                          username,
                          created,
                          forename,
                          surname,
                          '' AS `actions`
                     FROM accounts",
'columns_escape' => ['actions' => false],

You could add a columns_names property like this so that the new column does not have a heading. You'd need to use CSS if your headings have a background color but you don't want one for this column.

'columns_names' => [
    'id'       => 'ID',
    'username' => 'Username',
    'created'  => 'Created',
    'forename' => 'Forename',
    'surname'  => 'Surname',
    'actions'  => ''
],

And then in the columns_callbacks option you would need to add the code the creates the button that's added to that empty column.

'columns_callbacks' => [
    'actions' => function ($obj, $row_data, $name, $value)
    {
        return sprintf('<button onclick="event.preventDefault(); location.href = \'account.php?id=%d\'">View account</button>', $row_data['id']);
        
        // This creates a dropdown list instead of a button. It
        // might be more useful to you if you have a lot of actions.
        // return sprintf('<select onchange="location.href = this.value"><option></option><option value="account.php?id=' . $row_data['id'] . '">View account</option></select>');
    }
],

There's an example in the download archive called examples/accounts.php that demonstrates this technique.

Changelog