Location: PHPKode > scripts > phpMyAccess > phpmyaccess-1.5.3/phpmyaccess/manual.txt
PHPMyAccess Manual
version 1.5.3
original source marcth AT users.sourceforge.net
updates for version 1.5.x by gfdos AT users.sourceforge.net


1.0  What is PHPMyAccess

  PHPMyAccess is a generic table editing program. It uses a separate engine and
  table program. The table and fields to be edited are defined in the table program,
  which just calls the engine to do the grunt work.

  A number of features have been implemented to make the calling program more capable
  of influencing exactly what the engine should display.

  The engine itself works in three passes. Pass 1 displays the selected MySQL table
  in a scrolling table   on the screen. Radio buttons are used to select a record for
  editing, viewing or deletion. If the user chooses Add, Change, Zoom, or Delete buttons,
  Pass 2 starts, displaying the selected record. If the user chooses
  the Save or Delete button from this screen, Pass 3 processes the update and
  the display returns to the original table view (Pass 1).

  The engine now has the ability to click on a field, edit the field, and post when
  clicking else where in the page. This is made possible by the addition of javascript 
  to the code making this an AJAX enabled application.
  
  The engine also fully supports searches and will handle multiple filter criteria, and allows
  for one column to be the sort column, in either ascending or descending order.

  When in filtered table display mode, the filter can easily be removed by pressing
  the "Clear Filter" button. This will clear any filter settings, but will preserve the
  sort order.

  NOTE: From version 1.5.0 onwards PHP 5.xx is recommended, and preferably one of the latest versions
  (that is 5.1x or later), since they fix a number of pretty serious security issues. 
  May still run on PHP 4.x, but has only been tested on 5.1.2 and 5.
  
  The previous versions of PHPMyAccess were late php3/early php4 and many php functions have depreciated.
  PHPMyAccess 1.5.0 is only a PHP 5 syntax revision, no new features have been added
  or developed, versions 1.5.1 and later have added the Edit in line feature set.
  
  NOTE: Version 1.5.3 adds an updated version of the pma-editinline/pma-ajaxcell libraries 



1.1

  Why is it called PHPMyAccess ?
  Well, the idea is to make something that is as easy to use as MS Access for doing
  the basic editing of a table. Actually, it should be _easier_ to use, and support the
  usual things people drool over. Maybe it'll get to that point sometime, just not
  quite yet, right now.



1.2 Creating a table program

   To create the table program, the setup.php program is used.
   It will enable you to create the table program in one of two ways, as an on-screen
   displayed php program, or as a file to download. You will have to customize the options
   you want to use, e.g. disabling editing, setting authentication options, or simply
   choosing which fields to display.

   It's use is simple, but will require a valid account on the MySql server your database
   resides on.
   In a step by step process you'll be defining what you want to do, until the program has
   enough info to create the table program.


1.3  Authentication Options.

   Authentication is optional, and can be switched on/off, and assigned a level.
   If it is asigned a level, you will need a mechanism to determine if the user is of at
   least the appropriate level. Usually this is done via a table lookup. The table can be a
   simple PHP array, or a full MySql table.
   Authentication is done via a relatively simplistic method, and at this point only supports
   basic html authentication. This means your browser will try to get a page, and when it's told
   that authentication is needed, it will try again if it has a user name/password cached.
   If it has no user name and or password, it'll ask the user to provide one.
   There is no session state maintained as such, the system is essentially stateless.
   The validation of a password / user name combo is up to you, and there are many options to
   choose from. It has been used with tables, external authentication against a netware server,
   against a static password list, combined netware/tables etc.  Be creative, but be aware
   that I will not guarantee anything beyond that "It Works for Me" (tm).



1.4  Field types


   Auto      :
   Text      :
   Date      :
   Boolean   :
   Password  :
   number    :
   linked    :


   Selection types :

   Range     :
   AutoRange :
   linked    :
   listrange :

1.5  Pre-/Post processing


   PHPMyAccess allows for pre/post processing. The means used to implement this are simple,
   it will check for the existance of the Pre_run() and Post_run() functions, and will
   if present execute them. You can do lots of nice things this way, but you can also
   screw things up fairly majorly. You will have to write the PHP functions yourself,
   except for the Post_run, which is pre-defined, but extensible.
   An example of this is functionality can be found in the Hours Demo.


1.6  .xls and .rtf output.


   There is some basic support for export into excel compatible / word compatible file
   formats. It is not totally integrated into the engine, but that is planned to happen
   as soon as I have the time for it.
   At this point there is a little demo of this functionality in the Demo program. Look
   under the reporting menu page to see how it works.

1.7  Edit in place

	Sometimes you want to just click on a single cell and edit that data. Its doesn't require the
	page to reload, so its faster and it doesn't require leaving the "big picture" of the table. 


1.8  Users, menus, menu entries.


   The system has a set of programs and file definitions included that will allow you
   to create a reasonably useful menu structure to combine the total management of a
   database.  It's not as easy as I'd like it to be, on the other hand, it's not really
   rocket science either.

   At the base is a user, a user has menu pages, which are just menus, really.
   The menus are filled from a list of menu items, using the user_menu_items.php program.
   The menu item list itself is filled using the menu_items.php program. The pages
   available are first created using the menu_pages.php program. User accounts are
   created/edited using the useraccounts.php program.

   The obvious chicken and egg problem inherent in that method is solved by using a
   default menu structure you can use, as defined in the included sql file.

   You can use the user table as defined there, or modify it using extra fields, as
   the Demo program does.


1.9  security

1.9.1   General
        A decent amount of care has been put into making the program somewhat secure.
        At this point however, I will not preclude the possibility of things like
        sql-injection, or other exploit mechanisms, as the whole system is still in a
        state of flux. If you want to use the system on the internet at large, please
        do your own audit, and try to make sure your authentication works as you
        assume it does. It's tricky, and with reason.

1.9.2   Debugging.
        At this point you will have to enable debugging per program, just set the $debug
        variable to something other than 0. The debugging uses html comments, so you'll
        need to look at the output source. The advantage is that your display stays usable.
        To add a debugging statement use pmadebug($message) , as defined in the
        pma-debuginc.php file.


1.10  Internals

1.10.1   Upgrading older files to use new features

        In most cases you will want to use the newer setup program to create a sample
		program and update the existing programs to look like it.
		New features in 1.5.3:
		Edit in line: update to the libraries, adding additional functions and fixing a few bugs.
	New features in 1.5.1:
		Edit in line: libraries added allowing modification of data right in place without reloading the page
	New features in 1.5.0:
		PHP 5 : Code has undergone an exhaustive rewrite to work on PHP 5
        New features in 1.0.10:
        Print : needs footer and post_run mods for optimum quality.
        xls   : Downloads an excel compatible file of current selection
        Query : Needs specific tables and is in alpha stage, don't use.


1.10.2   Important variables passed between calls to this program

        $GLOBAL['fm']       first record to display
        $GLOBAL['inc']      no of records to display (SELECT ... LIMIT $fm,$inc)
        $GLOBAL['fl']       is the filter row display status (boolean)
        $GLOBAL['rec']      no. of record selected for editing
        $GLOBAL['qf0']..qf1..   value of filter for column 0, column 1 etc..
        $GLOBAL['qfn']      value of all filters used during the last pass (used by sort links)
        $GLOBAL['sfn']      sort field number (- = descending sort order)
		
        $GLOBAL['prev']
        $GLOBAL['add']
        $GLOBAL['change']
        $GLOBAL['delete']
        $GLOBAL['next']
        $GLOBAL['zoom']
        $GLOBAL['query']
        $GLOBAL['cfilter']  navigation/function buttons
        $GLOBAL['sw']       filter display/hide button ( ^ and V )
        $GLOBAL['apply']                apply filter button



1.10.3   Variables you may want to set to other values

        Changes to these variables should be applied to pma-setupinc.php.
        The defaults are reasonable for most setups, but feel free to experiment.

        * date *
          $opt['ydefault'] default year selection start
          $opt['ydefnum']  default number of years in year select
        * boolean *
          $opt['booltrue'] display value for boolean field <> 0
          $opt['boolfalse'] display value for boolean field = 0
        * range *
          $opt['rdefstart'] Default range selection
          $opt['rdefstep']  Default range step value
          $opt['rdefsteps'] Default number of range steps
        * strings *
          $opt['maxdisp'] Maximum size of default string entry box in add/change
        * password *
          $opt['nopasswd']  Shown when password not set in table view, set to '********'
                   to obfuscate presence/absence of password
          $opt['pwchangedet'] Random string used to detect password changes without showing password

1.10.4   Features details


        Linked fields:
        Value in one field pulls in another value from a table.

        Read-only fields:
        user can see but not change.

        pre-select:
        Two options to preselect data ranges to display (adds an SQL WHERE clause to query)
        i.e. preselection of range of records, limiting visible records
        There could be more, but two is a nice added flexibility for little cost.

        Authentication hook:
        The authentication is well integrated into the system, but is not mandatory.
        You can write your own pasword matching code and integrate it into the system.
        It has been used with tables, netware servers, and should also be usable with
        the usual windows and samba servers and ldap. Anything but tables will need
        external code/programs.

        Text/Memo fields:
        Automatic length setting for strings.

        Selection tables:
        Option to display the result of a valid sql expression in selection tables

        Email notification of addition/change of table (partial code present)


1.10.5
        Some examples:

        Tables with fields used in example:
        Countries:
         country_code
         country

        Customers:
          ..
          ..
          account_ID
          country
          ..
          ..

        Jobs:
          ..
          ..
          customer_id
          Country
          ..
          ..

        Selecting/displaying values from another table, :
        $fdd['country']['name'] = 'Country';
        $fdd['country']['sort'] = true;
        $fdd['country']['select'] = 'T';
        $fdd['country']['required'] = false;
        $fdd['country']['values']['table'] = 'countries';      // when displaying this is the table to use
        $fdd['country']['values']['column'] = 'country_code';  // this field contains a valid index into
        $fdd['country']['values']['description'] = 'country';  // (optional) this table to obtain description
        // (optional) you can use  valid SQL to show more info in the select dropdown
        $fdd['country']['values']['selectview'] = 'concat(country, ' - ', country_code)';
        Linking fields:
        $fdd['country']['link']['linked'] = true;              // this field is linked

        Cannot input it now, it will be filled using the following data :

        $fdd['country']['link']['linkedto'] = 'customer_id';   // field in this table we are linked to
        $fdd['country']['link']['table'] = 'customers';        // linked table name
        $fdd['country']['link']['rowindex'] = 'account_ID';    // row index into linked table
        $fdd['country']['link']['column'] = 'country';         // field in linked table


        This is to add a fixed where clause to limit visible records
        $somevar    = "United%";
        $presel     = true;
        $preselfld  = "Countries.country";
        $preselval  = "somevar";  // this value is always used as the name of a var
                                  // in this way you can also use some dynamic var set in
                                  // your authentication/access control system to limit viewable content
        $somevar1    = "whatever";
        $presel1     = true;
        $preselfld1  = "Countries.country";
        $preselval1  = "somevar";  // this value is always used as the name of a var


2.0		AJAX, PHP, & javascript PROGRAMER REFERENCE 

2.0.1	PHP + javascript + XMLHttpRequest = AJAX (version 1.5.1 and later)

		This is revolutionary... I was dreaming of these tools in 2002 when this application was first written.
		Prior to version 1.5.1 this was pure php. Starting with 1.5.1 javascript and XMLHttpRequest 
		were finally welcomed to the party.
		
		You will find javascript functions are now present... just remember not to try to call them from the php code.
		
		php is running on the server side, javascript is run in your browser -- XMLHttpRequests return the data in JSON
		and javascript pushes the changes back into the DOM of the page.
		

2.0.2	Edit In Line libraries and updates (version 1.5.1, 1.5.3, and later)
		
		The AJAX features described in 2.0.1 above are used in the Edit In Line libraries (pma-editinline, pma-ajaxcell). The versions included in 1.5.3 include some functions that aren't directly used (yet) in phpMyAccess, but are there for your consideration as a programmer. (specifically the 12to24 module used for converting time formats as part of a posting call) 


2.0.3	PHP3 to PHP 5 feature changes (version 1.5.0)

		There have been quite a number of changes between PHP 5 and PHP3.
		Many of these worked fine still in PHP 4 but dont work AT ALL in PHP 5
		
		ONE HUGE change is that declaring globals using "global" key word no longer works in PHP 5
        An example of this is:
		Given a local variable: $var
		Changes to $var in the Main part of the page affect $GLOBALS['var'],
		NOTE:since $GLOBALS is an array where all variables for php execution are stored.

		In functions, changes to $var are local changes -- do not affect $GLOBALS['var']
		and are lost if not assigned to a $GLOBALS variable or returned as part of the function.
		NOTE:This can be confusing if you have a variable defined in a function with the same name
		as a variable in the main.... 

		Example PHP 5 CODE showing this below:

		function TestmyBanana() {
		echo "unassigned function local banana: $banana <br>";
		echo "unassigned function global banana: ".$GLOBALS['banana']." <br>";
		echo "-=-Banana's Set-=-<br>";
		$banana = "function local banana";
		$GLOBALS['banana']= "function global banana";
		echo "assigned function local banana: $banana <br>";
		echo "assigned function global banana: ".$GLOBALS['banana']." <br>";
		}

		echo "MAIN<br>";
		echo "unassigned main local banana: $banana <br>";
		echo "unassigned main global banana: ".$GLOBALS['banana']." <br>";
		echo "-=-Banana's Set-=-<br>";
		$banana = "main local banana";
		$GLOBALS['banana']= "main global banana";
		echo "assigned main local banana: $banana <br>";
		echo "assigned main global banana: ".$GLOBALS['banana']." <br>";
		echo "-=-<br>Calling function<br>-=-<br>";
		TestmyBanana();
		echo "-=-<br>Returned from function<br>-=-<br>";
		echo "post function main local banana: $banana <br>";
		echo "post function main global banana: ".$GLOBALS['banana']." <br>";
		echo "-=-Local Banana Set-=-<br>";
		$banana = "function local banana";
		echo "assigned function local banana: $banana <br>";
		echo "post function main global banana: ".$GLOBALS['banana']." <br>";
		echo "-=-Banana Test Complete-=-<br>";
		*/



Return current item: phpMyAccess