array fields in the database and their handling with php4
array fields in the database and their handling with php4
am 26.10.2003 16:04:49 von Matthias Weinhold
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=20
SELECT name, abteilung[1], abteilung[2],FROM mitarbeiter WHERE name =3D
D. Benner;=20
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
--=20
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
Re: array fields in the database and their handling with php4
am 26.10.2003 17:10:14 von Michael Glaesemann
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Re: array fields in the database and their handling with php4
am 26.10.2003 19:56:11 von zen31329
Matthias Weinhold 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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Re: array fields in the database and their handling with php4
am 27.10.2003 09:59:18 von Adam Witney
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.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org