2. PHPRunner General questions
  3. [SOLVED]  Master - Detail Problem
This topic is locked
[SOLVED]

 Master - Detail Problem

 		10/2/2011 06:25:11
PHPRunner General questions
J
 Jepsen author

I have a problem that I cannot find a solution to. I am playing with PHPR 6.0 to get to know the new version, but my problem is not related to 6.0
I have 2 tables.
Master table:
Field 1: Period_name

Field 2: start_yr

Field 3: end_yr
Detail Table:
Field 1: date

Field 2: event
So now I want my master table to find all events happening inbetween start_yr and end_yr
Anybody who can help?
C
 		cgphp 10/2/2011

Create and alias for one of the fields of the master table. For example:

SELECT

Period_name,

start_yr,

end_yr,

end_yr as link_to_detail,

...

...

FROM master_table


Set the new alias (link_to_detail) as Custom field on the list page and enter this code:

$value = "<a href='detail_table_name_list.php?start=".$data['start_yr']."&end=".$data['end_yr']."'>Details</a>";


In the "List page: Before SQL query" event of the List page of the detail table enter this code:



if(isset($_GET['start_yr']) AND isset($_GET['end_yr']))

{

$strWhereClause = whereAdd($strWhereClause, "YEAR(date)>='".$_GET['start']."'");

$strWhereClause = whereAdd($strWhereClause, "YEAR(date)<='".$_GET['end']."'");

}
J
 		Jepsen author 10/2/2011

Christian
Thanks for your reply. I will try this now. I see what you are doing and it is quite smart.
Only drawback here is that I loose the details on the list page. The master-detail funktionality in PHPR. However, i may just have to live without it.
I will report back how it works
C
 		cgphp 10/2/2011

To keep the master-detail functionality you need a link between the two tables and a lot of code to validate the entries for the master and the detail table.
J
 		Jepsen author 10/14/2011

I have tried all sort of things and I ended up with a solution which is not too bad.
My mastertable is called perioder and my details table is called maerkedage.
master table "perioder" have 2 dates (years), start and stop and my details table have one date "dato"
And I want to display all maerkedage where dato is inbetween start and stop.
In view page I have made an event before display like this:

global $conn;
$strSQLExists = "select cat, begivenhed, DATE_FORMAT(dato, '%d-%m-%Y') as fdato from vhs._maerkedage where year(dato)>={$values['start']} AND year(dato)<={$values['slut']} ORDER by dato asc";

$rsExists = db_query($strSQLExists,$conn);
$vis_maerkedage="";

while ($maerkedag = db_fetch_array($rsExists)) {

$vis_maerkedage .= "<P><strong>{$maerkedag['fdato']} - {$maerkedag['cat']}</strong>
{$maerkedag['begivenhed']}</P>
";

}
$xt->assign("vis_maerkedage", $vis_maerkedage);


That gets the data and formats it like I want it.
I then inserts the data on the view page exactly where I want it by inserting like this:

{$vis_maerkedage}


And when finished it looks like this: http://www.vaabenhistoriskselskab.dk/testm/perioder_view.php?editid1=79
Pretty simple actually, but it took a while to figure it out.