Multi-line data elements in a field

Trying to tickle out each line as a record entry

[ghoffman]

I have enough experience with PHP and PostGreSQL to get myself into trouble. And my latest project has me stumped. I've created a website database to index the articles published over 25 years in a literary journal. I followed proper database design except for one field, Subject/Keyword, where I've allowed the indexer to put in multiple lines of text. It works great to call up an article by any line of text, especially when using LIKE in the SELECT statement. However, now I want to get a list of the Subject/Keyword entries in alpha order, so that I can have people browse the Subject/Keywords and then retrieve articles based on the chosen item. I can get a list by simple SELECT, but I can't group on those entries that have more than one line, because the aren't the same as any entry that has only one line.
If I can tickle out each of the elements, I could build a second table that would give me the the list I want. Should I do this entirely in PostgreSQL, with a SELECT INTO a new table and some server-side parsing to get what I want? Or should I bring it down to a web page and then let PHP build the list? If I could do the latter, I wouldn't need to put it back into a new table.
Any suggestions? RegEx? Explode? Counting newlines?
I'm already losing sleep over this and I'm supposed to be on vacation.
Thanks for any ideas.

[coopster]

Welcome to WebmasterWorld, ghoffman.

I'm trying to picture an example of the field's content ... can you show one here?

[ghoffman]

Sure. One of the articles has this in the Subject field:
Peabody, Elizabeth Palmer
Dall, Caroline Healey
Bacon, Delia S.
each on a separate line. It's great to call up the article by any of these names, but if I want a list of names, I can't get an alpha list by normal SELECT method.
Gary

[LifeinAsia]

A better design would be to have a lookup table of article and subject. In other words, if the above example is for Article 1:
ArticleID¦Subject
-----------------
1¦Peabody, Elizabeth Palmer
1¦Dall, Caroline Healey
1¦Bacon, Delia S.

[ghoffman]

Here's another entry:
Stowe, Harriet Beecher
drama
theatre
The Christian Slave
Uncle Tom's Cabin

[ghoffman]

Yep, I agree. But creating the structure for these multiple entries was not in me at the time. So what I want to do is now build the lookup table from the entries in the field. I think if I just dump out the field and import it back into another table, I'll have my lookup. I don't need the article ID because I'll use the entry as a search term to find all the articles that have a given subject/keyword in that field.

[LifeinAsia]

I'll use the entry as a search term to find all the articles that have a given subject/keyword in that field.

You could, although it can be a bit less efficient. As long as your DB is small and properly indexed, it probably won't make a major difference.

Is this DB static, or will you be adding new rows to it?

[ghoffman]

Here's another:
African American
black
oratory
Truth, Sojourner
Lee, Jarena
Prince, Nancy
Cary, Mary Shadd
Harper, Frances Ellen Watkins
Remond, Sarah Parker

[ghoffman]

The article table has 500 records and we'll add around 15 twice a year. I would have to build the subject list each time we add an issue. I may peel off the field into its own table like you suggested.

In the meantime, how should I separate out the various lines in those fields that have more than one line?

[LifeinAsia]

You'll just have to parse out each one, using <cr><lf> (or whatever line break delimiter PostGreSQL uses). I've never used PostGreSQL or PHP, so I can't tell you the specifics of how to do it.