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's used to insert 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_insert' => function ($editor)
{
   // Add your logic here
}

This option was called sql_add prior to beta 9.

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 column 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 associative array of column names that you want to be editable. When the results are editable you can double-click on a row to edit it. A window will popup with the details of the row and the columns that are editable will be form inputs. When saved, the data is sent back to the same page using a POST request where the editor will process it and save the editable rows back to the database. The SQL query that's specified in the sql_update option will be used to save the data back to the database. 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.

Note that you can use * as a column name wildcard.

'editable' => [
    'id'       => false,
    'forename' => true,
    'surname'  => true,
    '*'        => false
]

If you wish, you can have the HTTP POST request for saving edits request a different URL than itself when saving data. The following 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_database: ...,    // The database being edited
            editor_table: ...,    // The table being edited
editor_primary_key_column: ...,   // The name of the primary key column
editor_primary_key_value: ...,    // The value of the primary key column
                    data: ...     // An associative array of the columns for the row in
                                  // question. The keys of the array are the column
                                  // names and the values are the values that the
                                  // user has entered.
   
}

If you want a HTML <select> to appear for a particular column when you edit a row instead of a text input - set this option to select. Set the options for the <select> using the editable_types_select_options property (described below).

If you want a set of checkboxes to appear then you can set this to checkbox and if you want to see a set of radio buttons then set this property to radio. In a similar way to how the select box options are set, you can set the list of checkboxes or radio buttons with the editable_types_checkbox_options and editable_types_radio_options properties respectively.

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 the following types of HTML5 form control.

"editable_types" => [
    "column1"  => "checkbox",
    "column2"  => "color",
    "column3"  => "date",
    "column4"  => "datetime",
    "column5"  => "email",
    "column6"  => "month",
    "column7"  => "number",
    "column8"  => "radio",
    "column9"  => "range",
    "column10" => "select",
    "column11" => "text", // Not necessary as the default value is text
    "column12" => "textarea",
    "column13" => "time",
    "column14" => "week"
]

Range inputs (slider bars) are specified slightly differently to the others in order to allow you to give the minimum, maximum and step values. These look like this:

'editable_types' => [
    'age' => 'range,min=0,max=100,step=1'
]

If any of the min, max or step are omitted, the following default values are used:

min	0
max	100
step	0

There are demos of each type of form control in the download archive

Note that you can use * as a column name wildcard.

When you've specified that a column should be a select dropdown instead of a text input (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 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_types_select_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 tds    array-like  A NodeList of the  objects for the row in question.
       //                           This gives you access to all of the values of the row.
       // 
       function fetchUsernames(column, tds)
       {
           return ["henryb","kevinj","peterf","jospehk","lyrav","olgaf"];
       }
   </script>
   

   This is by far the most powerful option and allows you to have a different set of dropdown-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_types_select_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_types_select_options" => [
       "username"  => "Campbell::cam,Digworth::dig,Billingham::bill,Constantine::const,Jamie"
   ]
   

   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_types_select_options 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_types_select_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_types_select_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.

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

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

(in SQLite the "||" is the concatenation operator). There are demos in the download archive called editable-select.php and editable-select-sql.php that demonstrate this option.

Since approximately version 3.44 of SQLite there has been a CONCAT function available to use as well as the double-pipe operator:

"editable_types_select_options" => [
    "username" => "sql:SELECT DISTINCT CONCAT(username, '::', id) FROM users"
]

This option allows you to specify the different options that are used for a particular columns radio buttons. It supports the same methods for fetching and creating those options as the editable_types_select_options and editable_types_checkbox_options options including: a CSV string, an array, the results of an SQL query and the return value of a function call. As such, you can read the documentation above, for the editable_types_select_options option, for details.

'editable_types_radio_options' => [
   //'username' => 'sql:SELECT username FROM accounts'
   //'username' => 'function:getUsernames'
   //'username' => ['John Carpenter::johnc','Gary Barlow::garyb','Fred Bloggs::fredb','Luis Carroll::luisc']
    'username' => 'heyesr,hartnettj,biggsj,killer,joshh'
]

This option allows you to specify the different options that are used for a particular columns checkboxes. It supports the same methods for fetching and creating those options as the editable_types_select_options and editable_types_radio_options options including: a CSV string, an array, the results of an SQL query and the return value of a function call. As such, you can read the documentation above, for the editable_types_select_options option, for details.

'editable_types_radio_options' => [
   //'username' => 'sql:SELECT username FROM accounts'
   //'username' => 'function:getUsernames'
   //'username' => ['John Carpenter::johnc','Gary Barlow::garyb','Fred Bloggs::fredb','Luis Carroll::luisc']
    'username' => 'heyesr,hartnettj,biggsj,killer,joshh'
]

The event that you want to trigger an edit. On touch devices you may want to set this to click. Other mouse events may work too, such as contextmenu, mousedown, mouseup etc.

This allows you to run a PHP function when an edit is submitted, allowing you to perform validation on the submitted data if you want. The function should look like this:

'editable_callback' => function ($obj, &$data)
{
   // To cancel/reject the edit you can return false here like
   // this:
   // 
   // editor_messages::error($obj->id, 'That edit was rejected!');
   // return false;
}

The first argument that's passed to the callback function is the editor object and the second argument is the entire set of data that was submitted in the POST request.

Important: Note that in the example, there's an ampersand just before the second argument: &$data. In PHP this means that the argument is passed by reference so if you make any changes to the data they will be reflected in the data that gets saved to the database.

Return value: The value that you return from this function matters. If the return value of the function is null, you don't return a value at all or it's truthy) then the data will be saved to the database (remember that the data array was given to the function by reference - so any changes that you make to it will be what gets saved).

However if you return a falsey value (eg false (best), an empty string or zero) then nothing will be saved to the database and the editor will go back to the listing page.

In the case of you discarding the edit and returning false, you may also want to show an error message to the user. This can be done using the editor_messages::* functions. These functions are messaging functions that are used to show messages to the user and use the PHP session as storage so that the page can be refreshed and then the messages are shown. See the API documentation below for more details.

<?php
    editor_messages::success($id, 'Your success message!')
    editor_messages::warning($id, 'Your warning message!')
    editor_messages::error($id, 'Your error message!')
    editor_messages::notice($id, 'Your notice message!')
?>

Note: If you use checkboxes in your editing interface, remember that if the user doesn't check anything then nothing will be submitted in the POST data. So don't be surprised by not seeing an entry in the $data array - simply assume that nothing was checked and act accordingly.

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

'columns_names' => [
    'id'       => 'ID',
    'username' => 'Username',
    'forename' => 'First name',
    'surname'  => 'Second name'
],

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
]

Note that you can specify * as a column name wildcard.

This can be an associative array of true or false values controlling whether a tooltip is applied to the values in the column. The values of the tooltips are the values in that cell. 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.

Note that you can use * as a column name wildcard.

This option allows you to specify PHP functions (one per column - though that function could call other functions) 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. An example of using this option:

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

Another example of its use is to curtail the size of a column if it contains a large amount of text. Such a function might look like this:

'columns_callbacks' => [
    'description' => function ($editor, $row_data, $name, $value)
    {
        return rtrim(substr($value, 0, 15)) . (strlen($value) > 15 ? '...' : '');
    }
]

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

Note that you can use * as a column name wildcard.

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
]

Note that you can use * as a column name wildcard.

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 .

By default, the paging numbers are placed above the editor table on the right-hand-side. Normally, this is fine. However, sometimes you use the right hand column as a place that you add action buttons or an actions dropdown list. If this is the case, then you might want to style the header for this column differently - perhaps removing the background color. If you do this, then having the paging numbers placed over this column can make them look out-of-place. So that's when you can use this property to stipulate that the paging information cell, which normally spans the entire table, should only span, for example, the first X columns.

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. There's more information about the search functionality here.

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

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

If you specify this option, then only the columns that you specify and set to true will be searchable.

Note that you can use * as a column name wildcard.

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 of the columns will be able to be ordered by (unless a column is in the ordering_exclude array). For example:

"ordering_include" => [
    "*" => true
]

Note that you can use * as a column name wildcard.

If you want to exclude certain columns from being able to be ordered by, 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 will not be able to be ordered by. Note that you can use * as a column name wildcard. For example:

"ordering_exclude" => [
    "username" => true
]

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:

'style' => [
    '.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 property - the first is to supply an array of the label of the button and some JavaScript that's run when the button is pressed. 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 type="button" onclick="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 editor_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 type="button" onclick="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.

If you would prefer to see radio buttons instead of checkboxes - maybe you only want a single row to be selected at one time - you can set this to true and instead of checkboxes you will get radio buttons.

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 have multiple editors on the page though you will need to provide the id (eg editor1) 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 type="button" onclick="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 deselect the first checkbox in the row.

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

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

This function returns true or false as to whether the given argument is null or not.

This function returns true or false as to whether the given argument is null or null-like or not. Null, undefined and NaN are classed as being nullish. Zero or an empty string ARE NOT classed as being nullish.

This function returns true or false as to whether the given argument is undefined or not.

Similar to the Modal Dialog that's available with RGraph, this is a cutdown version of that and produces a modal dialog (a popup that covers the entire window and prevents the user from scrolling). The argument is a string that should be the HTML that you want to be displayed. This can be used to show information to the user or request information from the user.

If you're looking for a modal dialog for your own application though, then you'd be far better off using the standalone modal that's bundled with the RGraph library (that's linked to at the start of this paragraph).

This function hides the modal dialog that the previous function produces.

This function will allow you to get the data from the object. The (optional) id argument is the ID of the editor object. This is the same as the HTML ID of the editor container DIV (the first editor on the page is editor1, the second is editor2 etc). The optional index argument is the zero-indexed ID of the row. If you supply this then you'll just get back that row (an array of all the cells in that row) whereas if no index is a given you'll get back all the rows that are in the table.

Important: This function only gives you the data that's on display - not the whole dataset that's returned by the database.