30 Oct 2007 MichaelCrawford   » (Master)

Ask Advogato: PHP and MySQL?

I want to write a small web application for use on one of my websites. I'm happy to Read The Fine Manual, but I ask your advice on which parts are most pertinent to my problem. The reason I don't want to just figure it all out for myself is that there is a dauntingly large quantity of documentation available online, so I don't quite know where to begin.

If you don't want to respond here, you can either email me at (Harvest This, Spammers!) michael@geometricvisions.com or post in my Kuro5hin diary, where the topic has its own individual page.

While I have done some web application programming before, it's been a long time, and it was all done in Java with Enhydra. Enhydra's a great application server, but it's vastly more powerful than my simple needs require. Also my web hosting service actively supports PHP and MySQL, so I wont need to ask the hostmaster to install anything.

It's been so long since I've done any SQL that I've completely forgotten it, and I've never designed the schema (the layout of tables and fields) of an SQL database before.

There is also the issue of security, a topic which has evolved vastly since my last web programming job back in '99.

I did work through the simple PHP tutorial at php.net, and feel that it would probably work just fine for what I want to do.

I think my database would only require a single table with a dozen or so fields.

I'd like to have a web page with a form on it. One of the form fields would accept the user's email address. When the form is submitted, it would be added as a new record to the table, with each form field being placed into its own field in the record. There would be some additional fields as well.

There would be an integer field whose value would indicate whether the record was new, had been downloaded, or was old. Newly-added records would be set to new.

Periodically I would log into a password-protected web page and download a text file containing all the new records. The text file would be formatted as either tab-delimited, CSV (comma separated values) or maybe XML. There would need to be some way to prevent user-entered data from screwing up this file, for example if a user entered a comma in one of the form fields.

Once the download has succeeded, the included records would be marked as downloaded. I think it would be best if I were to manually order this, rather than having it done automatically when the download completes.

Again on a password-protected page, I would be presented with an HTML table of the downloaded records, and I could manually mark them as old. I would want a way to either mark them all at once, or individually. Gmail has something like this, where one can check individual emails, or check all the emails displayed on the page.

When this happens, the email address from each record would be used to send the user a confirmation email; this would require my PHP script to either talk SMTP directly, or maybe pipe some text into /usr/bin/mail.

Note that this would not be used for spamming my users; the page containing the form would inform them that the confirmation mail was going to be sent, and that would be the only mail I'd ever send in an automated way.

If you think there's a better way to do this than PHP and MySQL, I'm completely open to it, however, I'm very loyal to my hosting service (I've been with them for ten years), and I hate to bug the hostmaster to go out of his way for anything.

Thanks for any help you can give me -- michael@geometricvisions.com

Latest blog entries     Older blog entries

New Advogato Features

New HTML Parser: The long-awaited libxml2 based HTML parser code is live. It needs further work but already handles most markup better than the original parser.

Keep up with the latest Advogato features by reading the Advogato status blog.

If you're a C programmer with some spare time, take a look at the mod_virgule project page and help us with one of the tasks on the ToDo list!