Using phpMyEdit to edit Master-Detail Databases

James H. Thompson
Sept 20, 2003
jht@lava.net

License: same as phpMyEdit
Copyright 2003 by James H. Thompson

First Draft!


An example a master-detail database would be a database with:

    Company Table - one row per company
    Invoice Table - one row per invoice

In the Invoice Table there can be multiple invocies for each company listed in
the Company Table.  The Company Table would be the master, and the Invoice
Table would be the detail.

When editing the Company Table, its convenient to have a button for each row
to view and/or edit the associated detail records in the Invoice Table.

Only minor changes to phpMyEdit are required to enable master-detail editing.
These instructions assume you are familar with the patch tool and the general
process of development on unix systems.

Untar phpMyEdit-5.1 in a work directory.

Apply the patch shown in Appendix I

(An aside, this patch also enables displaying images in the displayed rows.
See Appendix II at the end of this document)


Now follow the normal instructions to generate a phpMyEdit files for 
editing your Master and Detail tables.  You will end up with company.php
invoice.php (or whatever you call the tables).

We will need a field (column) in the master and detail tables that ties
the two tables together.  In our Company/Invoice example it might be a field
named 'company_number' that appears in both tables.  When we examine 
a row in the master table, then the associated rows in the detail table will
be the ones with a matching 'company_number'.

Edit the company.php file and make the following changes:

Add two new fields.  You will need to addjust these examples for your
site and database.

$fdd["drilldown"] = array(
	'name'=>'Drilldown',
	'select'=>'T',
	'type'=>'int',
	'sort'=>true,
    'expression' => 'company_number',
    'URL' => 'http://www.yoursite.com/invoice.php?filters=company_number=$value',
    'URLtarget' => 'invoicedetail',
    'URLdisp' => 'View Details',
    'options' => 'VL',

);

$fdd["add_detail"] = array(
	'name'=>'Add Detail',
	'select'=>'T',
	'type'=>'int',
	'sort'=>true,
    'expression' => 'compan_number',
    'URL' => 'http://www.yoursite.com/invoice.php?operation=Add&company_number=$value',
    'URLtarget' => 'invoicedetail',
    'URLdisp' => 'Add Detail',
    'options' => 'VL',

);





Edit the invoice.php file and make the following changes:

None required.


Create new file (edit_db.html) that looks like this:

<html>
<head><title>Product Info</title>
</head>

<frameset rows="50%,50%">
<frame src="company.php" name=master>
<frame src="invoice.php" name=invoicedetail>
</frameset>

</html>




Now to edit the database, just point your brower at edit_db.html




Appendix I

Patch for file: phpMyEdit.class.php

--- phpMyEdit-5.1/phpMyEdit.class.php	2002-10-17 11:06:46.000000000 -1000
+++ phpMyEdit-5.1new/phpMyEdit.class.php	2003-03-16 00:37:04.000000000 -1000
@@ -2018,7 +2018,21 @@
 					echo '      <td'.$colattrs.'>';
 					if (! $this->hidden($k) && ! $this->password($k)) {
 						// displayable
-						if (isset($this->fdd[$k]['URL'])
+						if (isset($this->fdd[$k]['HTML'])
+								) {
+							/* It's an  HTML block
+							   Put some conveniences in the namespace for the user
+							   to be able to use in the URL string. */
+							$key     = $key_rec;
+							$name    = $this->fds[$k];
+							$value   = $row["qf$k"];
+							$page    = $this->page_name;
+							$value_filterd = htmlspecialchars($value);
+                            $HTMLblock = $this->fdd[$k]['HTML'];
+                            $HTMLblock = str_replace('$value',$value,$HTMLblock);
+							echo $HTMLblock;
+                            }
+						elseif (isset($this->fdd[$k]['URL'])
 								|| isset($this->fdd[$k]['URLprefix'])
 								|| isset($this->fdd[$k]['URLpostfix'])) {
 							/* It's an URL

    
    

Appendix II

Displaying Images

To show images in the displayed rows requires a field in the database that
can be turned into an image URL.  It could be a complete URL or just part
of one.

To show an image use a field something like this:


$fdd["pdd_image_url"] = array(
	'name'=>'image url',
	'select'=>'T',
	'type'=>'string',
	'maxlen'=>100,
	'nowrap'=>false,
	'sort'=>true,
    'HTML'=> '<img src="$value" height=60>',
);