The SQLite Editor for PHP tool

• Introduction
• Example
• Download
• Option reference
  • General properties
  • SQL properties
  • Editing properties
  • Columns properties
  • Paging properties
  • Miscellaneous properties
• Methods
• License
• FAQs

Introduction

The SQLite Editor for PHP is a 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",
        'sql_add' => false,
        'sql_delete' => false,
        'paging_perpage' => 10,
        'columns_names' => [
            'id'       => 'ID',
            'username' => 'Username',
            'created'  => 'Created',
            'forename' => 'Forename',
            'surname'  => 'Surname'
        ],
        'style' => [
            '.sqliteeditor table tbody tr:hover {background-color: #eee;}'
        ]
    ]);

    $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

$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

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

{
  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
]

'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>'
]

Methods

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

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.