API reference

The filename and path to your database file. This must be given. It can either be a relative path or an absolute path. Examples:

$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

The name of the table that you want the editor to be for. This must be given.

This doesn't have to be provided - the editor will try and get this from the description of the table. If your table has no primary key then editing and deleting won't work correctly (if at all).

This is the SQL query that's used to fetch the data to be displayed. You can use any SELECT query that you want but if you use dynamic columns (ie a column in your result set that's actually the result of a function), or if you use a join to fetch columns from another table, those columns will not be editable. If you want to use editing or you want to allow the deletion of rows then you should include the primary key of the table in this query.

This is the SQL query that you want to use to add a new row into the table. You can modify the default query and have it add default values for new rows if you want to.

Set this option to false to disable the addition of new rows.

This option can also be a function if you want to run some code when a new row is added (or if you want to run multiple SQL queries). The callback function is passed the editor object as its sole argument.

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

This is the query that's used to update a row after an edit. If you disable editing you can ignore this property (editing rows is disabled by default). The placeholder values in the UPDATE query (shown below) are replaced with the relevant values.

This option can also be a function if you want to run some code when a row is updated (or if you want to run multiple SQL queries). The callback function is passed a number of arguments:

• The editor object
• The name of the table
• The name of the field that was edited
• The new value
• The primary key value of the row
• The name of the primary key column

This is the query that's used to delete a row. The default query for this option deletes the row(s) entirely however you could change this so that it updates a deleted column with the current date/time , for example:

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

and then, using the sql_select option, only show rows that have a null deleted column.

Set this option to false in order to disable the deletion of rows.

This option can also be a function if you want to run some code when a row is deleted (or if you want to run multiple SQL queries). The callback function is passed the editor object and an array of ids that were selected for deletion.

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

This should be an array of column names that you want to be editable. When a column is editable you can double-click on an entry to edit it. Press [Enter] when you've finished and the new value will be sent back to the page using an AJAX POST request, where it will be saved back to the database using the SQL query that's specified in the sql_update option. Note that if the column is dynamic (for example, it's the result of an SQL function call) then when it comes to saving the value it will fail.

If you wish, you can have the AJAX call for edits request a different URL than itself when saving a value. The following POST data is passed to the URL that you specify:

{
  editor_action: "save",// Always set to "save"
      editor_id: ...,   // The ID of the relevant editor
   editor_table: ...,   // The table in question
  editor_column: ...,   // The column name being edited
   editor_value: ...    // The new value
   editor_index: ...,   // The value of the primary key
 editor_primary: ...    // The name of the primary key field
   
}

If you set this to true then the output of the POST request, that is made when you edit a value, is logged to the browsers JavaScript console. This allows you to debug your script that saves the edited value.

If you want a HTML <select> to appear when you edit a cell instead of a text input - set this option to select. Set the options for the <select> using the editable_options property (described below). If you want a text input then you don't need to do anything as that's the default.

Other types that are available include date, datetime, time, number and color and these use the relevant HTML5 form control.

"editable_types" => [
    "column1" => "text",// Not really necessary as the default value is text
    "column2" => "select",
    "column3" => "date",
    "column4" => "time",
    "column5" => "datetime",
    "column6" => "number",
    "column7" => "color"
],

When you've specified that a column should be a select dropdown instead of a text input field (using the editable_types option described above) you can use this property to set the options that should appear in the list. This allows you to stipulate what the possible options are instead of having a free-form text input field into which the user can enter any value. There are multiple possible values that this option can have:

1. A string that specifies a JavaScript function to run which returns the list of options that are to appear in the list. You would set the option like this:

   "editable" => ["forename","surname","username"],
   "editable_types" => [
       "username" => "select"
   ],
   "editable_options" => [
       "username" => "function:fetchUsernames"
   ]
   

   And the function that you specify should look like this:

   <script>
       // 
       // This is the function that's called to populate the
       // select dropdown.
       // 
       // @param column   string The name of the column
       // @param original string The original value of the column
       // @param td       object The td cell. This has various data
       //                        attributes that you may find useful:
       //                         o data-original
       //                         o data-display
       //                         o data-column-name
       //                         o data-row-index
       // 
       function fetchUsernames(column, original, td)
       {
           return ["henryb","kevinj","peterf","jospehk","lyrav","olgaf"];
       }
   </script>
   

   This is by far the most powerful and allows you to easily have a different set of options for each entry in the editor if you need that.
2. An array of the list of things that you want to appear in the dropdown box.

   "editable" => ["forename","surname","username"],
   "editable_types" => [
       "username" => "select"
   ],
   "editable_options" => [
       "username" => ["johnb","barryk","luisz"],
   ]
   

3. A comma separated list of what you want to see in the dropdown list.

   "editable" => ["forename","surname","username"],
   "editable_types" => [
       "username" => "select"
   ],
   "editable_options" => [
       "username"  => "Campbell,Digworth,Billingham,Constantine"
   ]
   

   If you have just a single list of options that you want to see in the dropdown list this is certainly the easiest way to do that.
4. Another powerful option is that the editable_select option can be a string that contains an SQL query. In this case the SQL query is run and the results are then used as the options in the select. For this option to work correctly you need to prefix the SQL query with "sql:" like this:

   "editable_options" => [
       "username" => "sql:SELECT DISTINCT username FROM users"
   ]
   

   Note the the sql query is run at the time that the editor is created.

Distinct labels and values

What happens though if you want the values that are sent to the database to be different to what is displayed in the dropdown to the user? This is quite a common pattern and you might have user ID numbers as the behind-the-scenes values and usernames as the display values (what the user sees in the dropdown list).

In this case you can separate the displayed text and the value by a double colon ( :: ). And that would look like this:

"editable" => ["forename","surname","username"],
"editable_types" => [
"username" => "select"
],
"editable_options" => [
     "username"  => "Campbell::johnc,Digworth::dwayned,Billingham::bertyb,Constantine::constd"
]

How you build that string (or array if you're using an array) of the dropdown options when you first set all of the options to the editor is up to you though - just like you building a page as you would normally.

If you're using the SQL query option and want to provide different values to the label that the user sees in the dropdown list you can use string concatenation in the SQL query like this:

"editable_options" => [
    "username" => "sql:SELECT DISTINCT username || '::' || id FROM users"
]

There are demos in the download archive called editable-select.php and editable-select-sql.php that demonstrate this option.

Friendly names for the columns instead of the database column names which are used when showing the editor.

This can be an associative array of specific column widths that you want to be applied to the resulting table. For example, you might be showing the ID column of your table but want it to have a smaller width than the default value. You could do this:

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

This can be an associative array of true or false values controlling whether a tooltip is applied to the values in the column. It could look like this:

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

By default, tooltips are shown, so you only need to set this if you want to turn tooltips off.

This option allows you to specify PHP functions (one per column) that are called when the values in that column are being displayed. For example, if you're showing a DATETIME column, which is in the format YY-MM-DD HH:MM:SS, but want to show it in a more human-readable form, for example, 21st July 2007, then you can use this option to do that if you want. If you prefer, you could also format the column in your SQL query, using the SQLite formatting functions, and you might not not need to use this option. For example:

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

You could also use the callback function to create "action" buttons at the end of each row, for example:

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

The callback function is passed the following arguments:

• The editor object.
• An array of the whole row that is currently being processed.
• The column name.
• The value of the cell

By default, all the column values and column names are escaped so that any HTML in the text will be seen as-is. If this is not desirable and you want to see the effect of any HTML tags (eg <b>, <i> or even <img>) you can set the relevant column name to false in this array. For example:

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

This setting allows you to set the page that's being displayed. It's important to remember though that this is overridden by the paging_[id] GET parameter. This allows you to set the initial page that's displayed to the user but they can then navigate to other pages using the paging links.

If you want to see a different amount of results on the page than the default you can use this setting to set the number of results to show per page .

This setting allows you to enable or disable the search function. The search allows you to enter one or more keywords, hit enter on your keyword and the results will be limited to rows that contain that text. If you enter multiple keywords the results will be those that contain both keywords - an AND search.

This allows you to restrict the search to one or more columns. It should be an array that contains the column names that you want to allow to be searched. For example:

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

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

    $editor->draw();
?>

The column that you want to order by. Instead of specifying the ordering in your SQL query you can set it here. If not given, then no ordering will be applied by default.

The direction that you want to sort by - either asc or desc. If not given, there will be no direction specified but the user will be able to change it by clicking on the column headers.

By default this is null. However, if you set this to an array, you can stipulate the columns that you want to allow ordering by. If this is set to an array, then ONLY the columns in this array will be considered for ordering (assuming that those columns are not in the ordering_exclude array). If this is left to the default of null, then all columns will be able to be ordered by (unless a column is in the ordering_exclude array).

If you want to exclude certain columns from being able to be ordered, you can specify the names of those columns with this property. This property is processed AFTER the ordering_include array, so if a column name is in this array, then it absolutely will not be able to be ordered by.

By default, this is true and makes it so that the user can change the ordering as they wish by clicking on the column headers. If you set this to false then the user will not be able to change the ordering.

By default, the ordering in the editor will ignore the case of the text but you can change this by setting this property to true.

This can be an array of strings which contain style declarations that are added to the document. You can apply styles to the editor and you can even set styles for other elements on the page. The <style> block that's added to the document is added at the end of the <head> section of the page. For example:

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

You can use this property to add extra buttons below the editor table, where the add and delete buttons are. There are two ways to use this roperty - the first is to supply an array of the label of the button and some JavaScript that is run when the button is pressed. In this case event.preventDefault(); is added before your JavaScript so that the page doesnt refresh. This option looks like this:

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

The alternative is to give a string in the actions array instead of the two element array. In this case the string is simply printed as-is so you can control every aspect of the button. This option looks like this:

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

When writing your JavaScript to go here (for example, in the onclick event listener) there's a macro that you can use: {id} which is replaced by the id of the editor (wrapped in quotes). This is intended for use with the sqlite_getchecked function but you can use it for other reasons if you so wish. You'd use it like this:

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

Using this option, you don't necessarily have to add a button - it could be any snippet of HTML that you want to add to the page. So you could, for example, add an image, a set of images or a select element (useful for when you have lots of actions that you want to give to the user) or your own menu system that provides multiple options to the user.

If you've disabled the deletion of rows by setting the sql_delete option to false the checkboxes on the left side of the rows will not be shown. You might, however, want the checkboxes for your own purposes. For example, you might have a button that you've defined with the actions option that uses the editor_getchecked() function. So by setting this option to true you can re-enable the checkboxes.

Call this function to set the page number that's currently being displayed.

This method returns an array of the values of the checkboxes that are added to the start of each row which are checked. These values are typically the primary key values of the rows in the database. If your table doesn't have a primary key though - this method will not function correctly.

If you only have a single editor on the page then you don't need to provide an argument. If you do have multiple editors on the page though you will need to provide the id (eg sqle1) of the editor that you want to query.

When you use this with the actions option (detailed above) there's a macro that you can use to make things easier - just add {id} (no quotes - it's not a string) instead of the id argument to the function and the editor will replace it with the correct id. Like this:

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

This function allows you to programmatically select the first checkbox in the row.

This function allows you to programmatically toggle the selection status of the first checkbox in the row.