Thread: array fields in the database and their handling with php4

Hey,

i have a table 'mitarbeiter' in my database, column 'Abteilung' is an
array{'Abteilung1, Abteilung2'}, because some people work in more than
one section. Now i will print the results from a query like

SELECT name, abteilung[1], abteilung[2],FROM mitarbeiter  WHERE name =
D. Benner;

to an html-table via php in this form:

Name        | Benner
-------------------
Abteilung 1 | abt_1
-------------------
Abteilung 2 | abt_2

I don't no how to handle the array-field in the database? Any hints?

Greetings from cold germany Matthias

--
Die Asiaten haben den Weltmarkt mit unlauteren Methoden erobert - sie
arbeiten während der Arbeitszeit.
        -- Ephraim Kishon (eigentlich: Ferenc Hoffmann)

Hi Matthias,

On Monday, Oct 27, 2003, at 00:04 Asia/Tokyo, Matthias Weinhold wrote:
> i have a table 'mitarbeiter' in my database, column 'Abteilung' is an
> array{'Abteilung1, Abteilung2'}, because some people work in more than
> one section.

I'm not an expert by any means, but I guess I would recommend using a
different set up for this. I don't know if using an array is the best
way for this situation. I remember reading somewhere that arrays should
really only be used for data that is naturally in an array form. (Plus,
I don't know how to use arrays either! :)

If you can still change the database, I'd recommend making another
table 'abteilung' and drop the 'abteilung' column from 'mitarbeiter'.

The new set up would be this:
table mitarbeiter (name)
table abteilung (name references mitarbeiter(name), abteilung)

Then a query that would return a table with name and abteilung would
look like

SELECT m.name, a.abteilung FROM mitarbeiter as m, abteilung as a where
m.name = 'D. Brenner';

I'm using 'name' as the primary key for mitarbeiter. If you've got a
different primary key (like 'id'), reference that instead of 'name' in
table 'abteilung'.

If you only want one row per name in the query result, I think you
could make a function that would return a list of all abteilung for a
given 'name'. However, you can do this kind of formatting in PHP just
as easily.

When you read the results in via PHP, you can take the name from the
first row to make the first row of your html table. Then take the
'abteilung' from the first (and possibly subsequent) rows of the result
to make the second (and subsequent) rows of the html table.

I'm sorry I can't give you specific help on arrays. Hopefully this will
help.

Michael

Matthias Weinhold <Matthias.Weinhold@gmx.net> wrote :

Hi!

In deference to database normalisation (and a personal liking for explicit data structuring), I would represent this
datain SQL via three tables along these lines (apologies - my german is next to non existant, so in English).. 

Workers (WorkerID - PK, int, Name - varchar)

Sections (SectionID - PK, int, Title - varchar)

Workers_Sections (WorkerSectionID - PK, int, WorkerID - FK to Workers.WorkerID, SectionID - FK to Sections.SectionID)

So, each person and each section is represented by one row in their own 'type' table, and a third table links the two
together,allowing for a many-many relationship. For each section which a person works in, they would have a row in
Workers_Sections.

To get a person and all of their sections:

SELECT w.name, s.title
FROM workers w
INNER JOIN workers_sections ws on ws.workerid = w.workerid
INNER JOIN sections s on s.sectionid = ws.sectionid

Or alternately, you could retrieve the person you want, and then simply get all of their sections using the 'glue'
tableworkers_sections and the sections table. This might be more logical, depending on how you process the data in PHP. 


Hope this helps - Cheers!

--Matt

> Hey,
>
> i have a table 'mitarbeiter' in my database, column 'Abteilung' is an
> array{'Abteilung1, Abteilung2'}, because some people work in more than
> one section. Now i will print the results from a query like
>
> SELECT name, abteilung[1], abteilung[2],FROM mitarbeiter  WHERE name =
> D. Benner;
>
> to an html-table via php in this form:
>
> Name        | Benner
> -------------------
> Abteilung 1 | abt_1
> -------------------
> Abteilung 2 | abt_2
>
> I don't no how to handle the array-field in the database? Any hints?
>
> Greetings from cold germany Matthias
>
> --
> Die Asiaten haben den Weltmarkt mit unlauteren Methoden erobert - sie
> arbeiten während der Arbeitszeit.
>         -- Ephraim Kishon (eigentlich: Ferenc Hoffmann)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

I'm not quite sure if this is what you are after, but if you are asking how
to use a PostgreSQL array in php then it just comes in as a string, I then
use this function to turn it into a php array

function pg_array_php($array)
 {
  $new_array = explode(",", ereg_replace("[{-}]", "", $array));

  return $new_array;
 }




> Hey,
>
> i have a table 'mitarbeiter' in my database, column 'Abteilung' is an
> array{'Abteilung1, Abteilung2'}, because some people work in more than
> one section. Now i will print the results from a query like
>
> SELECT name, abteilung[1], abteilung[2],FROM mitarbeiter  WHERE name =
> D. Benner;
>
> to an html-table via php in this form:
>
> Name        | Benner
> -------------------
> Abteilung 1 | abt_1
> -------------------
> Abteilung 2 | abt_2
>
> I don't no how to handle the array-field in the database? Any hints?
>
> Greetings from cold germany Matthias


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.