timothius

1:19 am on Jan 25, 2008 (gmt 0)

Hello everybody! First time I've posted in the Database forum.

Here is my situation: I have designed and am in the process of creating a mid-sized website that will serve up a database of poems. (Eventually it will become fairly large) I've seen sites that do what I'm trying to do, but I'm planning on doing it much better than I've seen it done before.

I have created a MySql database and created the necessary tables which include an author, poem, and collection table. I also learned the basics of PHP and will be using that do connect to the database and work with the data.

My question(s):How do I import, manage and edit the poems in my database most efficiently?

I tried using the sql command LOAD DATA INFILE, and that works very well for most information but the poems table, because the field that contains the actual poem (a TEXT datatype) takes a long time to turn into a string and edit it onto one long, long line. It would also be a super big pain to edit (say if there was a typo) And to do this for say, 1000 poems would be awful. It would be so much easier to copy and past into .html files... (which I don't want to do, as I want to build a dynamic site)

So with the previous information in mind,

Is there any good software that other sites use to easily connect to their database and input and edit large amounts of text? Or do I have to program a admin-access-only php pages that will do that stuff?

Sorry the question is kinda long... hope I didn't scare anyone off! Thanks for your help in advance!

Beverly

10:26 pm on Jan 25, 2008 (gmt 0)

I'm hoping you didn't scare anyone off either, cuz I'd sure like to see some answers on this one.

ceestand

11:22 pm on Jan 25, 2008 (gmt 0)

Well I would create a secure admin page anyway as you'd be able to add poems anywhere you had web access.

Have you thought about XML? If you like working in static files, but want the site to be dynamic, you could copy them all into XML files and edit them that way, then write a script to crawl the directory and parse the XML into the DB for use on the site.

timothius

6:18 pm on Jan 28, 2008 (gmt 0)

Yes, I do want to build an administration section of the site for adding new poems and authors.

Because of the sheer magnitude of the site, I don't want to have to deal with XML files...

Anyone else?

phranque

you can manage a list of flat files and meta data in a spreadsheet. generate a csv or tsv file from the spreadsheet. write a script to process that file. each row is a poem and one of the fields is the file name containing that poem. open and read the poem file into a variable and use that in your insert sql statement along with other fields for author, title, etc...

timothius

2:59 am on Jan 29, 2008 (gmt 0)

Decent idea, though I would really prefer to a database driven CMS. That would seem to me scalable and speedy in the long-term future of the site. I'm trying to work with dadabik to see if I can get a decent behind the scenes administration area where it's easy to add poems, author bios and such. If anyone have anymore good ideas please let me know! :)

jatar_k

3:09 am on Jan 29, 2008 (gmt 0)

couldn't you just use phpmyadmin to manage the mysql, at least to start as you are building your custom admin bit

not too hard to build a couple admin pages, basic to begin with

add new record - just build your form and then worry about the insert script

update record - make a page with a list of records adn an edit link you can use the same form and just load old data based on a GET string

as long as you can trust yourself you don't need much error checking right away

then just use htaccess to protect it for now

timothius

3:27 am on Jan 29, 2008 (gmt 0)

That's a little more like what I'm talking about:

Let me rephrase my question - I need an easy data-entry and editing system (or content management system. It needs to especially work well with LONG text datafields. For instance, the author bio as well as the actual poem is a datatype of TEXT in mySql. Since it has to be broken into paragraphs, it makes it very hard to edit using just a long string. (Very frustrating as well...) Do you know if PHPadmin is accomadating like that?

jatar_k

3:31 am on Jan 29, 2008 (gmt 0)

phpmyadmin is just a mysql management program

if you build it yourself a textarea using nl2br would make your paragraphs

you really could have an insert script done in an hour or so. You could probably do the insert and update in a couple hours even with beginner level php skills. It doesn't have to be pretty, it just has to work.