It seems a shame to have spent so much time and effort on a content management system that's so easy that anyone can use it, if the only people who are actually allowed to use it are the site administrators. Furthermore, while it's extremely convenient for an administrator not to have to edit HTML to make updates to the site's content, he or she must still transcribe submitted documents into the "Add New Joke" form, and convert any formatted text into the custom formatting language we developed above—a tedious and mind-numbing task to say the least.
What if we put the "Add New Joke" form in the hands of casual site visitors? If you recall, we actually did this in "Publishing MySQL Data on the Web" when we provided a form for users to submit their own jokes. At the time, this was simply a device that demonstrated how INSERT statements could be made from within PHP scripts. We did not include it in the code we developed from scratch in this chapter because of the inherent security risks involved. After all, who wants to open the content of his or her site for just anyone to tamper with?
But new joke submissions don't have to appear on the site immediately. What if we added a new column to the Jokes table called Visible that could take one of two values: Y and N. Newly submitted jokes could automatically be set to Visible='N', and could be prevented from appearing on the site if we simply add WHERE Visible='Y' to any query of the Jokes table for which the results are intended for public access. Jokes with Visible='N' would wait in the database for review by a content manager, who could edit each joke before making it visible, or deleting it out of hand.
To create a column that contains one of two values, of which one is the default, we'll need a new MySQL column type called ENUM:
mysql>ALTER TABLE Jokes ADD COLUMN
->Visible ENUM('N','Y') NOT NULL;
Since we declared this column as required (NOT NULL), the first value listed in the parentheses ('N' in this case) is the default value, which is assigned to new entries if no value is specified in the INSERT statement. All that's left for you to do is modify the administration system to allow hidden jokes to be shown. A simple check box in the 'Add Joke' and 'Edit Joke' forms should do the trick.
With new jokes hidden from the public eye, the only security detail that remains is author identification. We want to be able to identify which author in the database submitted a particular joke, but it's inappropriate to rely on the old drop-down list of authors in the "Add New Joke" form, since any author could pose as any other. Obviously, some sort of user name/password authentication scheme is required.
To store a password in the Authors table, simply add another column. You can then require an author to correctly enter his or her email address and password when they submit a joke to the database. You'd want to implement the same login procedure before you allow an author to modify his or her details (name, email address, etc.). You might even like to give each author a "control centre" of sorts, where he or she could view the status of the jokes he or she has submitted to the site.