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