ALIST is a PHP and JavaScript package that generates sortable, searchable, editable tables of data on your webpage from a database. It uses bootstrap CSS styles. ALIST is written and maintained by Adam Hincks S.J. and is released under the GPL.

The most current code can be downloaded from Gitlab, and you can also download a release here (which is recommended for most use):

ALIST requires the following PHP/JavaScript libraries: JQuery, Bootstrap, Select2, Select2 Bootstrap 3 CSS.

Example

Show per page
of 8
 

Name   Height (mm)   Creator   Department   Subdepartment   Start Date   In Devel.   Notes  
pickle 12.2 Lowly the Worm research foodstuffs 09 Mar. 1972 Nope N/A
glass elevator 2.332 Willy Wonka research transportation 12 Aug. 1979 Yessir Weeeeeee!
silly putty 123.332 Willy Wonka funorama N/A 23 Jan. 1973 Nope N/A
short sword N/A Julius Caesar research warfare 02 Dec. 1904 Nope Is the date right?
the letter B 1 Big Bird marketing foodstuffs 01 May. 1999 Yessir B is for bird.

Database Schema

Here is the schema of the database table displayed above. (It is in MySQL, but you can use any database software that talks to mysqli or PDO.)

mysql> describe alist_thing;
+----------------+---------------+------+-----+---------+----------------+
| Field          | Type          | Null | Key | Default | Extra          |
+----------------+---------------+------+-----+---------+----------------+
| id             | int(11)       | NO   | PRI | NULL    | auto_increment |
| name           | varchar(128)  | NO   |     | NULL    |                |
| height         | float         | YES  |     | NULL    |                |
| creator_id     | int(11)       | NO   | MUL | NULL    |                |
| dept_id        | int(11)       | NO   | MUL | NULL    |                |
| subdept_id     | int(11)       | YES  | MUL | NULL    |                |
| date_started   | datetime      | NO   |     | NULL    |                |
| in_development | enum('Y','N') | NO   |     | NULL    |                |
| notes          | text          | YES  |     | NULL    |                |
+----------------+---------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

Note that three of the columns (creator_id, dept_id and subdept_id) are foreign keys. One of the charms of alist is that it will insert the value from the linked table. Here are the relevant tables:

mysql> describe alist_creator;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(128) | NO   |     | NULL    |                |
| notes | text         | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe alist_dept;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(128) | NO   |     | NULL    |                |
| notes | text         | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe alist_subdept;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| dept_id | int(11)      | NO   |     | NULL    |                |
| name    | varchar(128) | NO   |     | NULL    |                |
| notes   | text         | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

PHP Code

Here is a walk-through the PHP code used to generate the above. First, we set the time-zone (so that PHP doesn't complain when date methods are used), open the connection to the database and instantiate a new alist object:

date_default_timezone_set("America/Vancouver");
$db = new mysqli("localhost", "test", "password", "test");
$a = new alist($db);

Next, we take care of some global settings. The first line is required because it tells alist which database table to render. The other lines are optional and self-explanatory.

$a->set_table_and_pkey("alist_thing", "id");
$a->set_default_perpage(5);
$a->allow_edit();
$a->allow_new();

Now we can start adding columns to the table. The first column will be name, which is a string. We instantiate a new col_str. Then, as an optional step, we add an action to it. The arguments of the action are (1) a JavaScript function that will be fired on the onclick event and (2) the database column of the value that should be passed to (1) when the row is clicked. Hence, in our example the id of the table row is passed to the function example_action when the name is clicked. Finally, we use add_column to add the column to our table.

$name = new col_str("name", "Name");
$name->action = new action("example_action", "id");
$a->add_column($name);

We now add the rest of the columns; note that unlike the ‘name’ column we do not actions to them. (The arguments for the column constructors are explained below.)

$a->add_column(new col_combo("creator_id", "Creator", "alist_creator",
                              
"name"));
$a->add_column(new col_combo("dept_id", "Department", "alist_dept", "name"));
$a->add_column(new col_combo("subdept_id", "Subdepartment", "alist_subdept",
                             
"name", "id", "dept_id", "dept_id"));
$a->add_column(new col_datetime("date_started", "Start Date",
                                
"%d %b. %Y"));
$a->add_column(new col_enum("in_development", "In Devel.",
                            array(
"Y" => "Yessir", "N" => "Nope")));
$a->add_column(new col_str("notes", "Notes", 20));

Finally, with a single command we render the table:

$a->make();

N.B.: You should ensure that the make() method occurs within a div with the container or container-fluid class, as is normal in the bootstrap framework.


Reference

Global Options

A number of options can be set via methods of an alist object.

Further control can be gained by setting some global variables of an alist object.


Column Constructor Arguments

col($field, $heading, $width 1, $default null, $action null, $css null)

The col class is the base class for all the column classes and should never be used: always use one of the inherited classes. But the arguments it takes appear in all the inherited classes.


col_hidden($field, $heading, $width 1, $default null, $action null, $css null)

Queries a DB column but does not display it; you may want to use this if you want an action for one of the columns to make use of a table value that is not displayed.


col_str($field, $heading, $maxlen = -1, $width 1, $default null, $action null, $css null)

For displaying text (e.g., a varchar column).


col_numeric($field, $heading, $width 1, $format "%g", $default null, $action null, $css null)

For displaying numeric data.


col_datetime($field, $heading, $format "%Y/%m/%d_%H:%M:%S", $width 1, $default null, $action null, $css null)

For displaying dates or times.


col_combo($field, $heading, $ref_table, $ref_field, $ref_pkey "id", $ref_filt_col null, $ref_filt_field null, $width 1, $default null, $action null, $css null)

For displaying a column that refers to an auxiliary table via a foreign key (normally an id key). Instead of displaying the foreign key, the value from the designated column of the auxiliary table is displayed. When editing a row or adding a new row, the options from the auxiliary table are presented in a drop-down menu.

A further complexity is provided for: if there is a third table that is also linked to the primary table by a foreign key that determines which possible values can be selected in the auxiliary table, that can be specified too. This only matters when editing or inserting rows. In the example above, the ‘Subdepartment’ column works like this. If you edit a row, the options available under the ‘Subdepartment’ column are determined by which value is currently selected in the ‘Department’ row.

this.


col_enum($field, $heading, $option, $width 1, $default null, $action null, $css null)

For displaying an enum.