2. PHPRunner General questions
  3. relate detail to master on 2 keys
This topic is locked

relate detail to master on 2 keys

 		1/5/2008 9:23:48 PM
PHPRunner General questions
D
 dmcconnell author

In the Datasource Tables section of PHPRunner, how can I set the relationship of a detail table to a master table on two fields, instead of just the primary key?
D
 		dmcconnell author 1/8/2008

Anyone have any ideas?
J
 		Jane 1/9/2008

Hi,
unfortunately you can't use complex foreign keys in PHPRunner.

Please give me more detailed description of what you need to achieve and I'll help you to find a workaround.
D
 		dmcconnell author 1/9/2008

Thanks Jane,
I have 3 tables as follows (MySQL):
licenses

id (int)

license_no (varchar:25)

date (date)

...
Products

id (int)

license_no (varchar:25)

title_id (varchar(20)

...
Usage

id (int)

license_no (varchar:25)

title_id (varchar:20)

license_used (int)
Licenses is the main table. It relates to products on the license_no field. I could have multiple rows in the products table that tie back to a single license in the licenses table. The usage table needs to relate back to the products table on license_no + title_id. It could have several rows of usage info for each row in the products table. In the products table, title_id will not be unique, as there will be many different licenses with the same title_id, but license_no + title_id should be unique. Hopefully that makes sense, please let me know if you need further details.
Thanks,

David
J
 		Jane 1/9/2008

Hi,
you can add condition to where clause in the List page: Before SQL query event on the Events tab.

Here is a sample:

global $conn,$strTableName;

$str = "select title_id from Products where license_no='".$_SESSION[$strTableName."_masterkey1"]."'";

$rs = db_query($str,$conn);

if ($data = db_fetch_array($rs))

$strWhereClause = whereAdd($strWhereClause," title_id='".$data["title_id"]."'")
D
 		dmcconnell author 1/9/2008

Okay, I added that and now I'm getting an error:
Parse error: syntax error, unexpected '}' in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\bsalicense\include\usage_events.php on line 32
Also, will this work-around show the correct info on the AJAX popups?
Thanks,

David
J
 		Jane 1/11/2008

David,
sorry for my fault.

Here is the correct code:

global $conn,$strTableName;

$str = "select title_id from Products where license_no='".$_SESSION[$strTableName."_masterkey1"]."'";

$rs = db_query($str,$conn);

if ($data = db_fetch_array($rs))

$strWhereClause = whereAdd($strWhereClause," title_id='".$data["title_id"]."'");


To make AJAX popups working you need to edit generated ..._detailspreview.php file manually. Or you can turn off AJAX popups in the include/dbcommon.php file:

$useAJAX = false;