pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
array fields in the database and their handling with php4
4+ messages / 4 participants
[nested] [flat]

* array fields in the database and their handling with php4
@ 2003-10-26 15:04 Matthias Weinhold <Matthias.Weinhold@gmx.net>
  2003-10-26 16:10 ` Re: array fields in the database and their handling with php4 Michael Glaesemann <grzm@myrealbox.com>
  2003-10-27 08:59 ` Re: array fields in the database and their handling with	php4 Adam Witney <awitney@sghms.ac.uk>
  0 siblings, 2 replies; 4+ messages in thread

From: Matthias Weinhold @ 2003-10-26 15:04 UTC (permalink / raw)
  To: pgsql-php

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)



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: array fields in the database and their handling with php4
  2003-10-26 15:04 array fields in the database and their handling with php4 Matthias Weinhold <Matthias.Weinhold@gmx.net>
@ 2003-10-26 16:10 ` Michael Glaesemann <grzm@myrealbox.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Michael Glaesemann @ 2003-10-26 16:10 UTC (permalink / raw)
  To: Matthias Weinhold <Matthias.Weinhold@gmx.net>; +Cc: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: array fields in the database and their handling with	php4
  2003-10-26 15:04 array fields in the database and their handling with php4 Matthias Weinhold <Matthias.Weinhold@gmx.net>
@ 2003-10-27 08:59 ` Adam Witney <awitney@sghms.ac.uk>
  1 sibling, 0 replies; 4+ messages in thread

From: Adam Witney @ 2003-10-27 08:59 UTC (permalink / raw)
  To: Matthias Weinhold <Matthias.Weinhold@gmx.net>; pgsql-php


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.




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: array fields in the database and their handling with php4
@ 2003-10-26 18:56 zen31329@zen.co.uk
  0 siblings, 0 replies; 4+ messages in thread

From: zen31329@zen.co.uk @ 2003-10-26 18:56 UTC (permalink / raw)
  To: pgsql-php; +Cc: Matthias Weinhold <Matthias.Weinhold@gmx.net>

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 data in 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' table workers_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








^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2003-10-27 08:59 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-26 15:04 array fields in the database and their handling with php4 Matthias Weinhold <Matthias.Weinhold@gmx.net>
2003-10-26 16:10 ` Michael Glaesemann <grzm@myrealbox.com>
2003-10-27 08:59 ` Re: array fields in the database and their handling with	php4 Adam Witney <awitney@sghms.ac.uk>
2003-10-26 18:56 Re: array fields in the database and their handling with php4 zen31329@zen.co.uk

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox