Filtering Records in phpMyEdit

Force search feature ON (version 5.6)

$opts['cgi']['append']['PME_sys_fl'] = 1;

Older versions (5.5-) use:

$opts['cgi']['append']['fl'] = 1;

When filtering records *and* doing lookups to another table where *both* tables contain a column of the same name, you will likely need to call your filter as:

$opts['filters'] = 'PMEtable0.col_name = "0"';

and *not* simply:

$opts['filters'] = 'col_name = "0"';

$opts['fdd']['owner_id'] = array(
  'css'        => array('postfix' => 'right-justify'),
  'default'    => '0',
  'input'      => '',
  'maxlen'     => 5,
  'name'       => 'Site Owner',
  'options'    => 'ACPVD',
  'required'   => false,
  'select'     => 'T',
  'size|ACP'   => 5,
  'sqlw'       => 'TRIM("$val_as")',
  'sort'       => true,
  'values'     => array(
    'table'       => 'contacts',
    'column'      => 'contact_id',
    'description' => array(
      'columns'    => array('display_name', 'company', 'last_name'),
      'divs'    => array(' ', ' ', ' ')
    ),
     'filters'  => 'category = "Client"'
  )
);

Note: 'filters' is used for filtering items in dropdown during ADD/CHANGE mode using with a SQL WHERE. And ['join'] is useful for having a correct LEFT JOIN against the main table in LIST/VIEW mode.

$opts['fdd']['vendor']['values']['join'] = 'PMEjoin6.part_no = PMEtable0.part_no AND is_preferred = "TRUE" ';

// Disable all filters
$opts['cgi']['overwrite']['fl'] = 0;

Variables used in most filters can be passed from another form, or from a link included in the page footer.

<a href="my_form.php?ltr=A">A</a>

In the requested form, process the variable and make it persistent across various page modes (Next, Previous, Search, Cancel).

$ltr = array_key_exists('ltr', @$_REQUEST) 
   ? strip_tags(stripslashes(trim(@$_REQUEST['ltr']))) 
      : '';

$opts['cgi']['persist'] = array('ltr' => $ltr);

--- OR ---

$ltr = @$HTTP_GET_VARS['ltr'];

if(!isset($ltr)) {

   $ltr = @$HTTP_POST_VARS['ltr']; 

}

$opts['cgi']['persist'] = array('ltr' => $ltr);

Filter records based on a letter passed to the form and make it persistent.

$ltr = array_key_exists('ltr', @$_REQUEST) 
   ? strip_tags(stripslashes(trim(@$_REQUEST['ltr']))) 
      : '';

$opts['filters'] = 'company REGEXP "^'.$ltr.'"';

$opts['cgi']['persist'] = array('ltr' => $ltr );

A sessions script passes $valid_user AND a column named valid_user also exists in rows of records accessible only to $valid_user and fictitious user 'public'.

$opts['filters'] = 'valid_user = "'.$valid_user.'" 
   OR valid_user = "public"';

$opts['cgi']['persist'] = 
   array('valid_user' => $valid_user );

The following configures a persistent table level filter for one record. Record remains filtered even if user clicks Cancel to leave View mode.

$id = array_key_exists('id', @$HTTP_GET_VARS) 
   ?  @$HTTP_GET_VARS['id'] 
      : $rec;

$opts['cgi']['persist'] = 
   array('id' => $col_name);

$opts['filters'] = 
   $id > 0 ? "col_name = '$id'" : '';

$opts['fdd']['RecordOwnerID'] = array(
  'name'     => 'Record Owner ID',
  'select'   => 'D',
  'options'  => 'ACPVD',
  'maxlen'   => 5,
  'default'  => '0',
  'required' => false,
  'sort'     => true
);

$opts['fdd']['RecordOwnerID']['values']['table'] = 
   'mdd_staff'; // other table

$opts['fdd']['RecordOwnerID']['values']['column'] = 
   'AgentID'; // column e.g. id

$opts['fdd']['RecordOwnerID']['values']['description']['columns']['0'] 
   = 'FullName';

$opts['fdd']['RecordOwnerID']['values']['filters'] = 'AgentID = 
   "'.$AgentID.'"';

$opts['cgi']['overwrite']['RecordOwnerID'] = 
   $AgentID;

$opts['fdd']['LastModByID'] = array(
  'name'     => 'Last Mod By ID',
  'select'   => 'D',
  'options'  => 'ACPVD',
  'maxlen'   => 5,
  'default'  => '0',
  'required' => false,
  'sort'     => true
);

$opts['fdd']['LastModByID']['values']['table'] = 
   'mdd_staff'; // other table

$opts['fdd']['LastModByID']['values']['column'] = 
   'AgentID'; // column e.g. id

$opts['fdd']['LastModByID']['values']['description']['columns']['0'] = 
   'FullName';

$opts['fdd']['LastModByID']['values']['filters'] = 'AgentID = 
   "'.$AgentID.'"';

$opts['cgi']['overwrite']['LastModByID'] = 
   $AgentID;

PHP Form Generator Home | PHP Form Generator Demo