master - detail

**Erwin Moller** · Jul 16 '05, 11:40 PM

Re: master - detail

Hi Joop,

I am not completely sure I understand your problem.
If I don't forget the rest. :-)

Joop wrote:
[color=blue]
> Hello everyone,
>
> I'm new on this list and have been working for quite a while on a PHP app
> that will make an HTML frontend for a PostgreSQL dbms. Works fine so far
> but I'm having a little trouble with master - detail screens. My db
> (roughly) has a hierarchical structure, something like
>
> BAND -> RECORD -> SONG
>
> When I display the record, band should be displayed on top of the screen.
> When song is displayed, band and record should be displayed. (the real app
> has more than 3 layers, but the idea is the same).
>
> At the moment I use something like storing current band, current record in
> a serialized _SESSION variable. It works, but not very smoothly because
> PHP (or actually HTTP) has no idea of history, descent (the current screen
> doesn't know what the previous screen was).
>
> So I'm struggling a bit with this. Are there any "standard" solutions for
> this master - detail setup? I found a lot on macromedia, but I don't use
> that, it's just Vim on Linux.[/color]

Nothing 'standard' about it. Just normal relations in your database, so just
'good programming practice'.

Let me explain:
You have a certain depth in your structure, right?
And I guess you have certain PHP-scripts that display information on a band,
or a record, song, etc. Right?

In that case I do not see the problem retrieving the information in the
hierarchy.

Let's take your example: BAND -> RECORD -> SONG

Supose you call a page showSongDetails .php?songid=456

where the songid in your db is 456

If your database is set up in a comprehensive way, the table that stores
songid (lets call it tblsong), also stores a reference (foreign key) to
your recordtable (tblrecord).

Same goes for tblrecord to tblband.

In that way you can say for every song, of which band it is, simply by:
SELECT bandid, bandname, etc etc FROM tblband WHERE
(bandid=(SELECT bandid FROM tblsong WHERE songid=$songid) );

in which $songid must of course be replaced by 456

Hope that helps.

Good luck,
Erwin
[color=blue]
>
> TIA for any pointers / help![/color]

**Joop** · Jul 16 '05, 11:40 PM

Re: master - detail

On Tue, 02 Sep 2003 14:16:27 +0200, Erwin Moller wrote:
[color=blue]
> Hi Joop,
>
> I am not completely sure I understand your problem. If I don't forget
> the rest. :-)[/color]

No, you understood it perfectly but I oversimplified the question, let me
try again.

<big snip>
[color=blue]
> In that way you can say for every song, of which band it is, simply by:
> SELECT bandid, bandname, etc etc FROM tblband WHERE
> (bandid=(SELECT bandid FROM tblsong WHERE songid=$songid) );[/color]

I only used the band -> record -> song setup as an example and a bad one,
sorry. Here's a better one (almost the real situation).

I have these tables

customer (key customer-id)
employee (foreign keys customer-id and person-id)
person (key person-id)

the relations are:

customer >-<< employee >>-< person

so a customer can have 0 or more employees and a person can be 0 or more
employees (can have 0 or more jobs). There's more, an employee can call in
sick 0 ore more times and a doctor can store 0 or more medical records on
a person, but the core is cust / emp / person.

In my PHP app, there are two routes that lead to employee, via customer
and via person. When coming from customer the screen should have customer
+ employee fields, when coming from person it should have person +
employee fields. So my app has to determin where it's coming from, and
that information table is not in a table. That's why I use session
variables now, but the nature of HTTP (stateless) and browsers make this
difficult to handle.

Hope this is a bit more clear. Has anybody else done this kind of thing in
PHP? TIA!

**Erwin Moller** · Jul 16 '05, 11:40 PM

Re: master - detail

Joop wrote:

Hi Joop,

(Ben jij nederlands? / Are you Dutch?)
I am. :-)
[color=blue]
> On Tue, 02 Sep 2003 14:16:27 +0200, Erwin Moller wrote:
>[color=green]
>> Hi Joop,
>>
>> I am not completely sure I understand your problem. If I don't forget
>> the rest. :-)[/color]
>
> No, you understood it perfectly but I oversimplified the question, let me
> try again.
>
> <big snip>
>[color=green]
>> In that way you can say for every song, of which band it is, simply by:
>> SELECT bandid, bandname, etc etc FROM tblband WHERE
>> (bandid=(SELECT bandid FROM tblsong WHERE songid=$songid) );[/color]
>
> I only used the band -> record -> song setup as an example and a bad one,
> sorry. Here's a better one (almost the real situation).
>
> I have these tables
>
> customer (key customer-id)
> employee (foreign keys customer-id and person-id)
> person (key person-id)
>
> the relations are:
>
> customer >-<< employee >>-< person
>
> so a customer can have 0 or more employees and a person can be 0 or more
> employees (can have 0 or more jobs). There's more, an employee can call in
> sick 0 ore more times and a doctor can store 0 or more medical records on
> a person, but the core is cust / emp / person.[/color]

Okay, that is clear.
[color=blue]
>
> In my PHP app, there are two routes that lead to employee, via customer
> and via person. When coming from customer the screen should have customer
> + employee fields, when coming from person it should have person +
> employee fields. So my app has to determin where it's coming from, and
> that information table is not in a table. That's why I use session
> variables now, but the nature of HTTP (stateless) and browsers make this
> difficult to handle.
>[/color]

You can handle this in 2 'main' ways.
But I guess you figured that out yourself already. :-)
Maybe it help you when somebody else comments on it.

Let me clarify:

1) Just create 2 PHP-pages.
One for the route from customer (cust_empl.php) ,
One for the route from person (pers_empl.php) .

This has the big advantage that you logically devide the routes and have the
possibility to do extra graphics/information/etc depending on the route.
I can imagine that you have to create more info depending on the route in
the future. In this way things are implemented easier/cleaner than with
if/then/else constructs of course.

And if a big chunk of the data is the same: Just make an include of it
(empl_view_incl .php) and keep the (breadcrumps??) logic out of it and in
the separate files (=cust_empl.php and pers_empl.php).

2) Store the state in _SESSION as you described and use already.

The fact that HTTP-protocol is stateless is excactly WHY sessiondata is
invented in the first place.
You use probably the _SESSION-array already to store other information I
guess???

So nothing wrong with your current approach I think.
I would advise you to stick to it unless one of the reasons in 1) is
compelling enough to switch.

[color=blue]
> Hope this is a bit more clear. Has anybody else done this kind of thing in
> PHP? TIA![/color]

Hope this helps.
I am sure you can find literature for both approaches if you look for it.
I have done a lot of coding like this and have a very pragmatic approach:
Sit back, think it over, and pick whatever solution makes the most sense to
you.

Regards and good luck,
Erwin

master - detail

master - detail

Comment

Comment

Comment