phpguru.org

Access your SSH accounts
with Windows Explorer...

MySQL Table Editor

Excellent! Very Good Good Fair Poor

• Abstract
• Features
• Demo
• Example Usage
• Method List
• Dependencies
• Translations
• ChangeLog
• TODO List

A PHP 4 & 5 library to allow easy and user-friendly editing of MySQL tables. Note this is a library and not an application. It will be of no use unless you do some coding (not exactly a great deal though - see below).

Note: This is not meant to be an alternative or replacement to phpMyAdmin. This is something you can provide to users to manage tables without them happily destroying your database.

You can get the code here.

Features

• Now (as of 1.0.5) works with both PHP4 and PHP5
• Easy setup. Give it a connection resource and a table name and it will work most things out by itself.
• Search functionality (albeit basic MySQL LIKE). Can be disabled (default), and limited to certain fields only. Advanced Search (version 1.0.6) can be used to search specific fields only, or with various operators (LIKE, =, >, >=, <, <=).
• View/Add/Copy/Edit/Delete functionality, any or all of which can be disabled.
• CSV download for selected rows, current page or entire table. Can be disabled. (Version 1.0.6).
• Cusomisable header/footers.
• Common functionality such as date/time/timestamp catered for in dropdowns (see add/edit pages in demo). Also supports adding new such functionality.
• Fields can be hidden from display, editing or both.
• Ordering by any column supported.
• Default values for fields can be set for when adding rows.
• Callbacks can be assigned to fields for pre-display manipulation (Eg. cutting long strings down).
• Callbacks can be assigned to any/all of add/edit/delete actions (Eg. for logging user actions).
• Can assign predetermined values to fields (Eg. showing "nicer" values for foreign keys). These values can be supplied as an array, or from the results of an SQL query.
• Multiple deletes at once support.
• Paging support. Number of rows per page is configurable.
• Data filter support, to allow only showing of certain rows.
• Data validation callback to allow custom checking and/or manipulation of data before being added/updated.
• Custom field types: password - provides a familiar two input process for setting passwords (ie. password and password confirmation input boxes), email - automatically checks the data matches an email address format, date, time and datetime - automatically sets the default value to the appropriate current value, and adds a "now" link next to the input for setting the current value during add/copy/edit.
• Required field support, to ensure certain fields are filled in during add/edit actions.
• Caching of table data structure (so it's not worked out on every page refresh).

Demo

Example Usage

The following example is used in my admin area to allow administration of comments:

This is the demo script:

Public Method List

The following are the public methods of the object which can be used to manipulate the gui:

• Constructor(resource database_connection, string table_name)
  Pass two things to the constructor: your MySQL database connection resource, and also the name of the table you wish to be edited.
  
  
• addError(string error_msg)
  Used to add errors to be displayed. Commonly used from within data validation callbacks. This adds a general error which is displayed at the top of the screen.
  
  
• setContextualError(string field_name, string error_msg)
  Used to add errors to be displayed. Commonly used from within data validation callbacks. This adds a contextual error which is displayed next to the appropriate field on the add/edit/copy pages.
  
  
• setConfig(string name, mixed value)
  This allows you to set any of the valid configuration parameters. These currently are:

  Name	Details
  perPage	Defines how many rows of data to show per page. This is an integer.
  allowPKEditing	Determines whether to allow editing of the primary key. This defaults to false. As of 11th April 2005 this is slightly buggy when set to true. This is a boolean.
  useFunctions	Whether to use the built in functions on the adding/editing pages. Defaults to false. This is a boolean.
  functions	An array of built in functions to facilitate easy entering of things like md5 hashes, current date and/or time etc. This is an associative array where the key is what's displayed on the add/edit page, and the value is a valid PHP callback which returns the desired value. Any value entered by the user on the add/edit page is passed to the callback as an argument.
  allowView	Whether to allow viewing of rows. Defaults to allowed. This is a boolean.
  allowAdd	Whether to allow adding of rows. Defaults to allowed. This is a boolean.
  allowCopy	Whether to allow copying of rows. Defaults to allows. This is a boolean.
  allowEdit	Whether to allow editing of rows. Defaults to allowed. This is a boolean.
  allowDelete	Whether to allow deletion of rows. Defaults to allowed. This is a boolean.
  allowASearch	Whether to allow use of the advanced search. Defaults to allowed. This is a boolean.
  allowCSV	Whether to allow CSV downloads. Defaults to allowed. This is a boolean.
  headerfile	Don't like my header? Use this to define an external file to use instead. All CSS will have to be defined or it will look... well... ugly. This is a string.
  footerfile	As above but for the footer. This is a string.

  
  
  
• getConfig(string name)
  Retrieve one of the various configuration options. See setConfig().
  
  
• setSearchableFields(string field_name [, ...])
  One or more field names which are allowed to be searched on. When using the setValuesFrom*() methods, the displayed values are searched, not the values in the database. This should make it more intuitive for the user. These searchable fields also apply to the advanced search.
  
  
• setRequiredFields(string field_name [, ...])
  One or more field names which are required to be entered (ie not left blank) on the add/copy/edit pages.
  
  
• setDefaultOrderby(string field [, integer direction])
  Use this to set the initial ordering of the data displayed. Pass in the fieldname to the function, and optionally a direction (1 = ascending, 0 = descending).
  
  
• setDefaultValues(array default_values)
  Use this to set default values when adding new records. You can set default values here for fields that aren't shown to the user on the add page (Eg. You might want to set new records to "not live" until moderated).
  
  
• setDisplayName(array display_names)
  This will probably be the most commonly used function. It allows you to display user friendly column names instead of the actual column names. Pass in an associative array of which the key should be the actual column name, and the value should be the display name.
  
  
• setValuesFromQuery(string field_name, string query)
  If you have foreign keys in your table, and instead of displaying the value in the table, you want to display the value in the "other" table, then you can use this to grab the values from the other table. The query should return two columns, the first should be the value which is in the table being edited, and the second should be the value which you wish to be displayed.
  
  
• setValuesFromArray(string field_name, array values)
  This is similar to the above, except that instead of a query, you supply a predetermined array of the values. The array should be associative with the keys being the values stored in the table being edited, and the values (of the array) being the values you want to be displayed.
  
  
• setInputType(string field_name, string input_type)
  Commonly used with one of the above two methods. You can use this function to set the input type shown on the add/edit pages. In use with the above two functions you would set the input type to 'select', so that only one of the preset values can be entered. An example is listed above in the comments script where the type is set to 'select' and the values are set to the news article titles.
  
  
• noDisplay(string field_name [, ...])
  Use this function to prevent the display of fields. You can supply one or more field names to the function. This will not hide the fields from the add/edit pages, use the noEdit() method for that.
  
  
• noEdit(string field_name [, ...])
  If you wish to display a field, but not allow its editing, then use this function. Like the above, you can supply one or more field names to it.
  
  
• onlyDisplay(string field_name [, ...])
  Use this function to set the fields which will be displayed. Only those specified will be displayed, any others will be set to be hidden.
  
  
• onlyEdit(string field_name [, ...])
  Use this function to set the fields which will be editable. Only those specified will be editable, any others will be set to be hidden.
  
  
• addDataFilter(string clause [, ...])
  Adds a data filter. Works purely during display, not add/edit. You should supply one or more valid SQL WHERE clauses which produce the desired results. Eg. addDataFilter("te_desc != ''", "te_email != ''")
  
  
• addDisplayFilter(string field_name, callback callback)
  This function can be used to manipulate the table data before it's displayed. One example as shown above in the first code listing is to prevent the full display of text fields. The function in the listing str_curtail() is a user function which cuts off text after the specified number of characters (though will not cut off mid-word), and then appends "...". The value of the field is passed as an argument to the callback, and the callback should return the result.
  
  
• addValidationCallback(string field_name, callback callback)
  Adds a data validation callback to the given field name. These are run after functions, defaults, required field checks and pseudo-field validation checks. Using these you can add your own validation checks before data is added/updated in the database. These callbacks may also modify the data (eg. md5()ing a password). Now these are added, functions default to off since they're pretty much redundant.
  
  The callback must:
  • Accept two arguments: the first is the TableEditor object, and the second is the data to validate. It is important for error handling purposes that the first argument is passed by reference. This is only an issue if you're using PHP4.
  • Return the value which is to be inserted into the database. This is to allow modification of data before insertion. You can of course return the supplied data untouched if you only wish to validate it.
  • Call the addError() method on the TableEditor object if an error occurs to register the error. The error should naturally be descriptive.
  
  
  
• addAdditionCallback(callback callback)
  This function can be used to specify a callback function that should be run on the successful addition of a new record. The new data that has been added is passed as an argument to the callback. This could be used if you wish to log actions performed by a user. You can call this function multiple times to add more than one callback.
  
  
• addCopyCallback(callback callback)
  As above but for copies.
  
  
• addEditCallback(callback callback)
  As above but for edits.
  
  
• addDeleteCallback(callback callback)
  As above but for deletes.
  
  
• display()
  Call this after you've configured the object to display the page.
  
  

Dependencies

• The PEAR package "Pager" must be installed and available via the include_path, a relatively recent version that supports "Pager_Sliding" must be used.
• The PEAR package "Net_URL" must be installed and available via the include_path.

ChangeLog

16th June 2005 (1.0.6)
==============

 o Fixed bug when not displaying primary key field.
 o Fixed javascript bug in FireFox when selecting records.
 o Added CSV download option. Can download either the selected records, the current
   page or the entire table. Works well with searching too (ie. you can download the
   current page even if it's a search result). There's a callback available to
   override which escapes a single field of CSV data. The built in escape function
   replaces carriage returns and line feeds with \r and \n respectively. It also
   escapes commas with a backslash.
 o Added advanced search capabilities


26th April 2005 (1.0.5)
===============

 o Added support for join tables. Using these you can join the main table onto other
   tables to retrieve columns from the "other" tables. When using join tables add
   copy and delete functionality is automatically disabled, however edits can still
   take place.
 o Added two new methods, onlyDisplay() and onlyEdit(). As the names suggest these
   methods set all fields to be hidden except those specified. Useful if you join
   onto tables and end up having to hide lots of columns.
 o Backported to PHP4. Be careful with references when using data validation callbacks.


19th April 2005 (1.0.4)
===============

 o Added support for a "bitmask" type. Descriptive values need to be set using the
   setValuesFrom*() methods. On the add/edit pages, this type is shown as a multiple
   select. The corresponding type in MySQL is a SET, however you could just as easily
   use an int.
 o Added a "Set blank password" checkbox to the "password" pseudo input, to allow
   setting of a blank password.
 o Changed field specific errors on add/editcopy page to be next to their respective
   fields. There's now a new method called setContextualError() to allow setting of
   these errors from data validation callbacks.


17th April 2005 (1.0.3)
===============

 o Fixed a bug which made multiple display filters on a column not work correctly.
 o Search now highlights found terms. Does this by applying a display filter to each
   searchable colum, which is applied *after* any user defined display filters.


15th April 2005 (1.0.2)
===============

 o No longer classed as beta, (well, by me at least... ).
 o When using the view button, fields with preset values are now shown
   as they are on the main table page.
 o Fixed a bug which meant edit was disabled when view was, and couldn't be
   disabled by itself.
 o Fixed redirection bug with urls.
 o Fixed leading zeros being removed when inserting purely numeric values into
   a text based field type.
 o Added row copying functionality.
 o Added data filters via addDataFilter() method to facilitate the
   showing/viewing/editing/deleting of only certain rows
 o Table structure is now no longer determined on each and every page refresh, but
   is cached in the session.
 o New method: setSearchableFields(). To be used instead of setConfig('searchableFields').
   Supply one or more field names which are allowed to be searched.
 o Added support for required fields when adding/editing/copying. If a required field is
   not filled in, an error is displayed.
 o Added support for pseudo input types:
    o password  This shows two password inputs on the add/edit page. If when
                submitted the two do not match, an error is displayed. However,
                if the two are left blank/empty, the field is not updated. This
                is to allow updates to rows without changing the password field.
                You can alter this behaviour by stipulating the field to be
                required.
    o email     This shows a regular text input, however it automatically checks
                whatever contents are supplied and ensures it matches the form of
                an email address. If however the field is empty, no error is
                raised. You can alter this behaviour by stipulating the field to
                be required.
 o Added support for data validation callbacks. These are run after functions, defaults,
   required field checks and pseudo field validation checks. Using these you can add your
   own validation checks before data is added/updated in the database. These callbacks
   may also modify the data (eg. md5()ing a password). Now these are added, functions
   default to off since along with default values functions are pretty much redundant.
 o Added handling for magic_quotes_gpc


14th April 2005 (1.0.1-beta)
===============

 o Changed aesthetics for shameful self promotion
 o Added GPL licensing
 o noDisplay() method now only hides a field from display, not editing. Use
   noEdit() in combination to hide a field from editing too.
 o Renamed defaultOrderby() to setDefaultOrderby()
 o Fixed a number of E_NOTICE errors
 o Added setDefaultValues(), which sets default values when adding rows. Can
   be used with fields that aren't editable.
 o Added add/edit/delete callback function support via addAdditionCallback(),
   addEditCallback() and addDeleteCallback() methods. The callbacks are run when
   each appropriate action is taken.
 o Added ability to search fields which have their values preset with the
   setValuesFrom* methods.
 o Changed to using a single checkbox per row, and a single set of add/edit/delete
   buttons. Can now delete multiple rows at once.
 o Added View button, for viewing a row in full on it's own page.
 o Added support for external header and footer files. Changing the header will lose
   all CSS so you'll have to define your own.
 o You can now disable any/all of the view/add/edit/delete buttons.


11th April 2005 (1.0.0-beta)
===============

 o Initial release

Translations

You can get a German translation of the code here. I haven't tested this, so for all I know it may wipe your hard drive. Do a diff first to check.

TODO List

My current list of features to add:

• Advanced Search: present a page with separate inputs for each searchable field
• Multiple column primary keys
• NULL support
• Auto_increment recognition
• Check that, when adding, fields with preset value lists contain one of the preset values.