master - detail

[Joop]

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.

TIA for any pointers / help!

[Erwin Moller]

Hi Joop,

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

Joop wrote:

> 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.

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

>
> TIA for any pointers / help!

[Joop]

On Tue, 02 Sep 2003 14:16:27 +0200, Erwin Moller wrote:

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

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

<big snip>

> 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));

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]

Joop wrote:

Hi Joop,

(Ben jij nederlands? / Are you Dutch?)
I am. :-)

> On Tue, 02 Sep 2003 14:16:27 +0200, Erwin Moller wrote:
>

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

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

>> 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));

>
> 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.

Okay, that is clear.

>
> 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.
>

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.

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

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