Getting started: Setup your first SQLite editor!
- Introduction
- 1. A basic SQLite editor instance
- 2. Adding nicer column headings
- 3. Limiting the amount of data that's displayed at once
- 4. Changing the date format that's used
- 5. Changing the order of the columns
- 6. Customising the appearance of your editor
- Conclusion
Introduction
SQLite Edit for PHP has a lot of options - making it very configurable and very versatile. That being said though, a basic implementation of it is simple and means that you can quickly get up and running and see results.
1. A basic SQLite editor instance
Here's a basic editor object that shows the contents of a table. The filename option is the path and filename of the SQLite database file and the table option is the name of the table to show the contents of. A small CSS rule has also been added so that the editor stretches across the page.
<?php
$editor = new SQLiteEdit([
'filename' => 'examples/sqliteedit.db',
'table' => 'accounts',
'style' => [
'div.editor-container-editor1 table {width: 100%;}'
],
'sql_delete' => false,
'sql_insert' => false
]);
$editor->draw();
?>
This produces the following table. The editor defaults to showing 25 results per page and if there isn't any more results you won't see any page numbers or links. By default, all of the columns are shown and the column names from the database are used as the column headings. Ordering and search are both enabled by default so they can happily be used if you want. The addition and deletion of rows is also enabled by default though these are not shown below.
2. Adding nicer column headings
The column headers that come from the column names in the database are not exactly pretty are they? So lets change them to something more presentable. Also, while we're at it, the password and deleted columns shouldn't really be shown so lets not select them in the first place. This is done by changing the SQL query that's used to select the data. And the column headers should probably be centered within each column so let's do that with a bit of CSS:
<?php
$editor = new SQLiteEdit([
'filename' => 'examples/sqliteedit.db',
'table' => 'accounts',
'sql_select' => 'SELECT id,
username,
created,
forename,
surname
FROM accounts',
'columns_names' => [
'id' => 'ID',
'username' => 'Username',
'created' => 'Date created',
'forename' => 'First name',
'surname' => 'Second name'
],
'style' => [
'div.editor-container-editor2 table {width: 100%;}'.
'div.editor-container-editor2 table thead tr th {text-align: center;}'
],
'sql_delete' => false,
'sql_insert' => false
]);
$editor->draw();
?>
This produces the following editor:
3. Limiting the amount of data that's displayed at once
This can be done with the paging options of your editor. It's simple enough to do and involves adding another option to your configuration. Whilst we're at it we'll also reduce the size of the first column so that it's smaller than the other columns.
Note that when the first column is being selected with the nth-child() CSS function we're using 1 as the index. This makes complete sense as it's the first column that we want. However, if deleting or editing was enabled then a checkbox would be placed at the start of each row to facilitate this. So that means that when you're selecting the first column of data in the table the correct index to use is actually 2 because the first column is then the checkboxes - not the data.
Here's the updated code:
<?php
$editor = new SQLiteEdit([
'filename' => 'examples/sqliteedit.db',
'table' => 'accounts',
'sql_select' => 'SELECT id,
username,
created,
forename,
surname
FROM accounts
WHERE {where}
{order}',
'columns_names' => [
'id' => 'ID',
'username' => 'Username',
'created' => 'Date created',
'forename' => 'First name',
'surname' => 'Second name'
],
'style' => [
'div.editor-container-editor3 table {width: 100%;}',
'div.editor-container-editor3 table thead tr th {text-align: center;}',
'div.editor-container-editor3 table thead tr th:nth-child(1) {width: 50px;}'
],
'sql_delete' => false,
'sql_insert' => false,
'paging_perpage' => 10
]);
$editor->draw();
?>
And this produces the following editor:
4. Changing the date format that's used
The default date format used by the database (YYYY-MM-DD) is fine - but lets change it to something that's more instantly readable. This can be done in two ways - firstly the data can be manipulated in the SQL query that's used to select the data in the first place. This might be best if you're comfortable with the SQLite date and time functions. You simply manipulate the date in the SQL function that you give and the results are automatically shown as what comes back from the database.
Alternatively, you can use the columns_callback option to specify a PHP function that's called for each row and can manipulate the data as required. This is what we're going to do here.
Here's the updated code:
<?php
$editor = new SQLiteEdit([
'filename' => 'examples/sqliteedit.db',
'table' => 'accounts',
'sql_select' => 'SELECT id,
username,
created,
forename,
surname
FROM accounts
WHERE {where}
{order}',
'columns_names' => [
'id' => 'ID',
'username' => 'Username',
'created' => 'Date created',
'forename' => 'First name',
'surname' => 'Second name'
],
'columns_callbacks' => [
'created' => function ($editor, $row_data, $name, $value)
{
return date('jS F Y', strtotime($value));
}
],
'style' => [
'div.editor-container-editor4 table {width: 100%;}',
'div.editor-container-editor4 table thead tr th {text-align: center;}',
'div.editor-container-editor4 table thead tr th:nth-child(1) {width: 50px;}'
],
'sql_delete' => false,
'sql_insert' => false,
'paging_perpage' => 10
]);
$editor->draw();
?>
And this produces the following editor:
5. Changing the order of the columns
So far the editor is looking nice - but the date column is looking out-of-place stuck there in the middle. So lets move it over to the end of the table. This can be done easily by changing the order that the columns appear in the SELECT query that we've given to the editor to use.
Here's the updated code:
<?php
$editor = new SQLiteEdit([
'filename' => 'examples/sqliteedit.db',
'table' => 'accounts',
'sql_select' => 'SELECT id,
username,
forename,
surname,
created
FROM accounts
WHERE {where}
{order}',
'columns_names' => [
'id' => 'ID',
'username' => 'Username',
'created' => 'Date created',
'forename' => 'First name',
'surname' => 'Second name'
],
'columns_callbacks' => [
'created' => function ($editor, $row_data, $name, $value)
{
return date('jS F Y', strtotime($value));
}
],
'style' => [
'div.editor-container-editor5 table {width: 100%;}',
'div.editor-container-editor5 table thead tr th {text-align: center;}',
'div.editor-container-editor5 table thead tr th:nth-child(1) {width: 50px;}'
],
'sql_delete' => false,
'sql_insert' => false,
'paging_perpage' => 10
]);
$editor->draw();
?>
And this produces the following editor:
6. Customising the appearance of your editor
The appearance of the SQLite editor can be customised with CSS. The style option allows you to add CSS which is inserted into a style tag just above the editor in the HTML document. This option is demonstrated in the example below - you add normal CSS declarations to it and they will be added to the document for you. You can actually add any CSS that you like here - it doesn't have to be specific to this editor.
<?php
$editor = new SQLiteEdit([
'filename' => 'examples/sqliteedit.db',
'table' => 'accounts',
'sql_select' => 'SELECT id,
username,
forename,
surname,
created
FROM accounts
WHERE {where}
{order}',
'columns_names' => [
'id' => 'ID',
'username' => 'Username',
'created' => 'Date created',
'forename' => 'First name',
'surname' => 'Second name'
],
'columns_callbacks' => [
'created' => function ($editor, $row_data, $name, $value)
{
return date('jS F Y', strtotime($value));
}
],
'style' => [
'div.editor-container-editor6 table {width: 100%; }',
'div.editor-container-editor6 table tr td:nth-child(2) {font-weight: bold;}',
'div.editor-container-editor6 table th {background-color: black !important;}',
'div.editor-container-editor6 table th div {border: none; color: white; padding-left: 10px;}',
'div.editor-container-editor6 table tbody tr:hover {background-color: #fdd !important;}',
'div.editor-container-editor6 div.editor-page-numbers a {color: #aee5da !important;}'
],
'sql_delete' => false,
'sql_insert' => false,
'paging_perpage' => 10
]);
$editor->draw();
?>
And this produces the following editor:
Conclusion
As you can see, the SQLite editor is easy to use, versatile and you can quickly get a good looking datagrid up and running based on either a table in your database or from the results of a custom query (using the sql_select option). Making this a valuable tool when it comes to quickly showing the results of your SQL queries or the data in your database tables on your website.
This guide doesn't even touch the editing capabilities of the SQLite editor, which can make producing rich, usable interfaces quick, easy and consistent for your whole website. Examples of all of the options that are available to you - the different form inputs that you can use - are in the download archive and they should run as soon as you extract the archive to your computer.
You can use all of the standard HTML form inputs - text inputs, radio buttons, combo boxes (select dropdown lists), checkboxes, file uploads along with the various, newer HTML5 form controls like range inputs, date inputs time inputs, number inputs etc. You can see lots of demos of the editable options in the download archive.
Last updated: 31st October 2026