Content management system
(Original: Kevin Yang Translation: December 04, 2001 13:28) So far, we have seen some database-driven webpages: These pages can display information obtained from a MySQL database when they are requested. However, until now, we haven't seen such a solution, which is how to easily manage a large website such as SitePoint.com. Of course, our joke database is quite good, but when we want to manage directories and authors, we always return to the mysql command line to use complex SELECT statements and INSERT statements, as well as those table names and column names. To transform a website that is just a website in the database into a fully database-driven website, we need to add a content management system. Such a system typically contains a series of web pages that should be used only for users who are authorized to be updated. These pages provide a database-managed interface. Through this interface, users can easily browse and change the information stored in the database without having to pay attention to those desirable SQL syntax. In fact, we have finally begun to establish such a content management system in Chapter 4, when we allow the website to add jokes using a web form and you can delete jokes through a "deleted joke" (if you finish The words of the challenge). But all of these features are on a page visible to the visitors. And in fact you may not want anyone to add some materials that may be useless to your website without your authorization. And you don't want anyone to delete jokes from your site. To overcome these "danger", you need a restricted website management page so you can avoid exposing your data to everyone, and you can still use SQL query statements to directly manage the contents of the database. In this chapter, we will expand our skills of our joke management system to effectively use our improvements to the database in Chapter 5. In particular, we will allow the site of the site to manage the author and directory and specify the author and directory for the corresponding joke. As we explained earlier, these management pages must be protected by an appropriate access. Place the corresponding PHP file into a .htaccess file containing an authorized user is a better method. For information on this, you can check the relevant documents of your web server. From this chapter, we will use some considerable PHP files, and we will omit some of these details due to space limitations. Home In the end of Chapter 5, our database includes data tables that describe the following three things: jokes, authors and jokes. Please note that we assume that every author has only an email address, so we don't have a separate description of the Email address. Therefore, our content management system's homepage will contain the following three connections to manage these three items:
JMSjoke Management System
Manage JokeSmanage Authorsmanage Joke Categories Manage Author Let us start from Authors.php, which allows managers to add new authors, delete, and edit existing author. If you are interested in the idea of multi-function page, you may want to put all the code in the same file authors.php. Because if you do it, the code code will be quite long, so this file is separated in our example. The first thing is that we want to present to managers' lists that have been stored in all authors in the database. From the perspective of the code, this is nothing from all the jokes we listed in the database. Because we need to delete and edit the existing authors, we contain these functions after the name of each author. Just like the "delete joke" connection in the final challenge in Chapter 4, contains this author's ID, which can know which author we want to edit or delete. Finally, we also provide a "establish a new author" connection to guide a form, which is the same as our "add joke" made in Chapter 4. Manage Authors Manage Authorscreate New Author's MANAGE AUTHORSCREATE
$ cnx = mysql_connect ('localhost', 'user', 'password');
MySQL_SELECT_DB ('Jokes'); $ authors = mysql_query ("Select ID, Name from Authors);
IF (! $ authors) {
echo ("
Error Retrieving Authors from Database! "." Error: ". MySQL_ERROR ()); exit ();} while ($ author = mysql_fetch_array ($ authors)) {$ ID = $ author [" ID "]; $ name = $ Author ["name"]; echo ("
$ name "" [edit | "." delete] ");}?> return to front page Delete author deleteauthor.php Used to remove the author of the specified ID from the database. As we see, this can be easy. It is to be achieved by a delete command, but it is a bit more complicated. I have to remember that one AID column in our Jokes table is the author of the joke. When the author is removed from the database, we must delete other data sheets. Provide this author place. If we don't do this, the next author is added, his ID may be the same as the author of the author of the author of the author, and the joke that is deleted by the author will also correctly point to the new author. We have two options to handle this situation: When deleting an author, delete all the jokes belonging to this author. When a author is deleted, the AID of all jokes belonging to this author is set to Null, to mark the author does not have the author. Because most of the authors do not want us to use their jokes without signing, we use the first solution. This also makes it eliminates the NULL value of the AID column without having to show our joke library.
Delete Author
$ Cnx = mysql_connect ( 'localhost', 'user', 'password'); mysql_select_db ( 'jokes'); // Delete all jokes belonging to the author // along with the entry for the author $ ok1 = mysql_query ( ". Delete from Jokes WHERE AID = $ ID "); $ ok2 = mysql_query (" delete from authors where id = $ ID "); if ($ ok1 and $ ok2) {echo (" Author deleded successful ");} else { Echo ("Error Deleting Author from Database!". "Error:". MySQL_ERROR ());}?> return to authors list Add Author's next is NewAr.php, which allows managers to add new author to the database. Again again, this is very similar to what we added in Chapter 4. Add New Author
IF ($ SUBMIT): // A New Author Has Been Entered // Using The Form Below. $ dbcnx = @Mysql_Connect ("Localhost", "Root", "MyPasswd"); mysql_select_db ("jokes"); $ sql = "INSERT INTO Auto Authors Set". "" EMAIL = '$ Email' "; if (MySQL_QUERY) {Echo (" New Author Added ");} else {echo (" Error adding new author: ".mysql_error ()." ");}?> Add another authorreturn to authors list
Else: // allow the user to enter a new author?>
Editing the author's left is Editauthor.php, which should provide an interface to edit a detailed information of an author already existing. This page is actually very similar to NewAr.php, just here, the form field will initially contain data stored in a database, in addition, we will use a UPDATE command when submitted, and no longer an insert command. There is another small trouble here. To initialize the value of the form in a value stored in the database, this page will definitely get relevant information by the $ ID value passed from Authors.php and store it into the PHP variable (such as $ Name and $ Email). The code of our form should look like this:
Please note that we use a form of hidden domains here, use the author of the author to pass the author's ID when submitted. But considering the author's name may be "the jokester" (with quotation marks). PHP scripts will produce such code:
Obviously, this is a problematic HTML. We must pass the quotation marks in a backslash "ignore" name so that the web browser knows that this is part of the value of the property, not the beginning or end of the attribute value:
PHP provides a function called AddSlashes to move to add a backslash (such as quotation marks and apostrophe) where it is required. Use this function to all variable values, you can use the variable value from the database to use it as an HTML attribute value, without problems due to the existence of quotation marks. $ name = addslashes ($ name); the same problem also exists before we use these variables in the SQL query. Let's take a look at the SQL INSERT commands below: mysql> Insert Into authors set-> name = 'jennifer o'reilly', -> email='jen@hotmail.com '; very obvious, the author's surname No. will result in problems. Why didn't we worry about this problem before? PHP has an excellent feature called "Magic Quotes", we can set in your php.ini (or php3.ini) file:
Magic_quotes_gpc = on this setting tells PHP to automatically use the addslashes function when any variable is passed in the request of the page. GPC means "get, post and cookies", which is three methods when information is requested as a page. Now, because we inserted all values in the database, it is passed as part of the form, and the PHP's Magic Quotes feature will automatically add a slash. However, when you get a value from the MySQL database, we can't get the help of the Magic Quotes feature, so we must manually add slash to avoid problems that may bring because of the presence of quotation marks, apostrophes, or other special symbols. We solve the problem of annoying special symbols, but other symbols that appear in the author will lead to problems. E.g,
It is defined as an identifier of HTML, which will output the destruction. There is a function to convert it into a "secure" format. This function is htmlspecialchars, which is almost the same as ADDSLASHES:
$ text = htmlspecialchars ("Can Be Dangerous!"); Echo ($ text); // output: Can Be Dangerous!
Considering these issues, we can now build editauthor.php.
Management catalog
When you compare the author and joke catalog in the role of the database, you will find that they are very similar. They all have a data sheet, they all contact a group of jokes through a certain way. Therefore, the directory can use the almost the same code bike developed by the author, but we must take into account an important difference.
When a directory is deleted, we can't delete jokes belonging to this directory at the same time, because those jokes may have other directorys. We can check all jokes to see if they belong to other directories, but only delete jokes that are not other directories, but this approach is time, in fact, we can allow our database to jokes to not belong to any directory . These jokes may be invisible to our visitors of our website, but they are kept in the database, we can specify the directory for them in the future.
In this way, delete a directory, we also delete all records related to this directory in the Jokelookup table:
... // Delete all joke lookup entries for the // category along with the entry for the category $ ok1 = mysql_query ( "DELETE FROM JokeLookup WHERE CID = $ id");. $ Ok2 = mysql_query ( "DELETE FROM Categories WHERE ID = ID "); ... In addition to this details, directory management is the same as the author's management.
Manage jokes
In addition to adding, deleting and modifying jokes in our database, we also need to specify directory and author for our joke. In addition, our jokes may be much more than the number of authors or directories. Therefore, like all jokes in the author and directory will be a difficult list of difficult management, it is not easy to find the jokes we want to find. We need more convenient way to browse our joke library.
Because in different times, we may know the laugh of the joke, the author or a paragraph of a paragraph, so we support all these methods from the database to find jokes. This form will prompt the manager to determine the directory and author for the joke you want. The code is like this:
Manage Jokes Manage Jokescreate New Joke
$ dbcnx = @Mysql_connect ("localhost", "root", "mypasswd"); mysql_select_db ("jokes"); $ authors = mysql_query; $ cats = mysql_query ("SELECT ID, Name from categories ");?>
Return to Front Page
Note that the string of strings output by the ECHO function is to refer to the wrap, which will increase the readability of the HTML code output from this script.
One of the following problems is to generate jokes in a satisfactory specified standard by JokeList.php. Obviously, this will use a SELECT query, but the format of this query must depend on the input we just in the form. Because the establishment of this SELECT statement is a fairly complex process, let us first analyze Jokelist.php.
First, let us first prepare:
Manage Jokes Management Jokesnew Search
$ dbcnx = @mysql_connect ("localhost", "root", "mypasswd"); mysql_select_db ("jokes");
Now, we begin to define some strings. When these strings are combined together, they will generate a SELECT query that is not entered in our form:
// The Basic STATEMENT $ SELECT = "Select Distinct ID, Joketext"; $ from = "from Jokes"; $ Where = "Where id> 0";
You may be fascinated by the WHER clause above. We must establish our SELECT statement based on the constraints selected by the form. Such constraints require us to add something in the From and WHERE clauses of the SELECT statement. However, if not specified constraints (that is, the manager wants to list all the jokes in the database), then we don't need a WHERE clause at all! Because it is quite difficult to add a non-existent WHERE clause, we need a WHERE clause that "nothing." The request jokes.id is greater than zero is a good idea, because the characteristics of MySQL AUTO_INCREMENT (it is used to specify the value of this column) so that the integer value greater than zero is always selected. The next task is to check the constraints that may be set in each form (the author, directory, or search text) and adjust our SQL accordingly. First, let's handle the author of the possible settings, "Any Author" option in the form, we originally set to "" (empty string), so if the value of this form (stored in $ AID) is not equal, ", This means that the author is designated, we will adjust our query accordingly:
IF ($ AID! = ") {// An author is success $ where. =" And Aid = $ AID ";
The operator is used to add a new string back to an already existing string. Now, we add the conditions of the AID in the Jokes table in the WHERE clause to match the author ID ($ AID) selected in the form.
Below, let's deal with the designated authors:
IF ($ CID! = ") {// a category is selected $ from. =", Jokelookup "; $ where. =" and id = jid and custom = $ CID ";}
Because the directory associated with the specific joke is stored in the Jokelookup table, we need to add this table to the query and add this table name to the $ from variable to create a connection. For complete connections, we also need to specify the ID column (in the Jokes table) must match the JID column (in the JokelookuP table), the implementation is to add this condition in the $ where variable. Finally, we need a CID column (in the Jokelookup table) Match our directory ID ($ CID) selected in the form.
Handling search text is quite simple. We only need to use the SQL of the SQL learned in the second chapter:
IF ($ SearchText! = "") {// search text specified $ where. = "And Joketext Like '% $ SearchText%'";
When our SQL query is established, we can use it to get our jokes and display them together with the corresponding editing and deleted connections (just like we do with the author and jokes). In order to increase readability, we show jokes in a format of an HTML table:
?>
Joke textOptions $ jokes = mysql_query ($ where); if (! $ Jokes) {echo ("
"); echo (" Error Retrieving Jokes from Database! "." Error: ". MySQL_ERROR ()); exit ();} while ($ joke = mysql_fetch_array ($ jokes)) {echo ("
"); $ ID = $ Joke [" ID "]; $ JOKETEXT = $ JOKE [" Joketext "]; Echo (" $ Joketext "); echo ("
"" Edit | "." "" "" Delete] "); echo ("
");}?>
After processing JokeList.php, let us handle newjoke.php connected to Jokes.php. This page is very similar to NewAuthor.php, Newcat.php very similar. However, in addition to the text of the joke, this page also needs to allow managers to specify an author and directory. Because of this existence, we have interested in studying this code carefully.
In the code of NewAr.php, we can see that we have added a code in front of the form of the form to handle the provision of forms (do not have to be, but so far, this is our style). Let's first take a look at the code of the form.
We first get the list of all authors and directories in the database:
Else: $ dbcnx = @mysql_connect ("localhost", "root", "mypasswd"); mysql_select_db ("jokes"); $ authors = mysql_query ("select id, name from autom authors); $ cats = mysql_query (" SELECT ID, Name from categories ");?> Then we build our form. We first need a standard text input domain to enter the joke:
A drop-down list is not enough for the selection directory, because we want to allow managers to specify multiple directories for a joke. In this way, we need to use a multi-selection box - each of them corresponds to a directory. The name of this multi-selection box will be CAT1, CAT2, CAT3, and more. Multi-selection box will be marked as the name of the directory.
Place In Categories:
While ($ cat = mysql_fetch_array ($ cats)) {$ CID = $ CAT ["ID"]; $ cname = $ cat ["name"]; echo ("" "$ cname");}?>
Finally, we end our forms as usual:
Handling this form is not a simple matter, so we will introduce this code in detail. Adding a joke to Jokes is quite simple. Because an author must be specified, we must ensure that the $ AID contains a value. This prevents managers from choosing the "Select One" option in the author drop-down list because this selection corresponds to "" (empty string).
IF ($ SUBMIT): // A New Joke Has Been Entered // USING THE FORM.IF ($ AID == "") {echo ("You Must Choose An Author". "for this Joke. Click 'Back'" "and try again."); exit ();} $ dbcnx = @mysql_connect ("localhost", "root", "mypasswd"); mysql_select_db ("jokes"); $ sql = "Insert Into Jokes Set". "Joketext = '$ JOKETEXT'," "" "" "" AID = '$ AID' "; IF (MySQL_QUERY ($ SQL)) {Echo (" New Joke Added ");} else {echo (" Error Add New Joke: ". MySQL_ERROR (). "" ";} $ jid = mysql_insert_id (); the last line of the above code We used a function we haven't seen before: mysql_insert_id. This function returns Mysql's auto_increment feature as the value of the last inserted record specified. In other words, we get the ID of the newly inserted joke, below we will use it.
Add records to Jokelookup to join the Jokelookup as soon as the selected multi-select box is not that simple. First, we don't know how a multi-selected box will pass variables to PHP; We also need to handle this situation: We can't preview how many options have a form of a form (because we don't limit the number of directories in the database).
A multi-selection box If you are selected, it will pass its value to a PHP variable, and if you are not selected, any value will not be delivered. In our form, we didn't define the value of the multi-selected box. Multi-select box of undefined values will still pass a value to the corresponding variables when selected. Because PHP takes into account the string value "true" can be used as a condition as a condition, and an empty string (or not specified) will correspond to the variable "false", we can only use multiple box variables to verify multiple boxes Whether is selected.
For the case of processing, we don't know the number of boxes, the best way is to explain the code. First, we get a list of all directories in the database, including their ID:
$ cats = mysql_query ("Select ID, Name from Categories);
Because when you build multiple boxes, we use the same list, which makes us realize that we can use it here to process. We use a While loop to gradually handle this list:
While ($ cat = mysql_fetch_array ($ cats)) {$ CID = $ CAT ["ID"]; $ CNAME = $ CAT ["name"];
For each directory in the list, we want to use the value of the corresponding multi-selection box to decide whether to add new jokes to this directory. The current problem is that the naming of the multi-selection box depends on the ID of the corresponding directory. We must therefore use the ID name of the directory to establish a variable name. Below is the code:
$ VAR = "Cat $ CID"; // The name of the variableif ($$ var) {// the checkbox is checked
Two US dollars are not a printed error. The value of the variable named $ VAR will be "carat #", here, # is the ID of the corresponding directory. And $$ VAR value will be equal to $ CAT #. This is a fairly embarrassing feature of PHP, called "variables of variables", in which case it is very effective. In summary, we use $ VAR in the IF statement to complete the record to Jokelookup when you are selected in the multi-selection box: $ sql = "Insert Ignore Into Jokelookup". "SET JID = $ JID, CID = $ CID"; $ ok = mysql_query ($ SQL); "Joke Added to Category: $ CNAME");} else {echo ("Error Inserting Joke". "INTO CATEGORY $ CNAME:" .mySql_ERROR () "" ""}} // end of if ($$ var)} // end of while loop?> Add another Jokereeturn to Joke Search
Using Ignore in Insert Inquiry is just a precaution. Memolive, when we define the Jokelookup table, we set the JID and CID columns to the primary key of this table. If because unknown reasons, the JID / CID being inserted is existing in the table. Attempting to insert it again, it is usually caused to cause an error. After adding Ignore in the command, re-inserting the same association is simply ignored by MySQL without an error. This situation may not occur at all, but considering it is definitely safer.
The rest of the two files: editjoke.php and deletejoke.php are quite similar to what we manage the author and directory, and only need small adjustments. EDITJOKE.PHP must provide a multi-selection box for the directory of the author's drop-down list, just we must assign them in the database based on the corresponding values of the selected jokes in the database. For deleteJoke.php, we need to delete selected jokes from the Jokes table, but also need to delete records related to the joke from the JokelookuP table. But we won't spend a detailed study because two documents do not involve any other technique.
Conclusion
There are still some details of our content management system that have not been resolved. For example, you can't provide a list of jokes that don't belong to any directory - when the jokes in our database are increasing, this feature will be used earlier. We may also need to sort the jokes in different conditions. The implementation of these features also requires some other SQL skills, and we will learn it.
If you ignore these small details, now we have a system that allows people who do not have any SQL or database knowledge to easily manage our joke database! Plus a series of PHP pages to show jokes to visitors, this content management system allows us to create a database-driven Web site that can be maintained by a person who has no database knowledge.
In fact, our site only needs to use a special knowledge (excluding the use of a web browser): content format. You don't want to be strange to this statement, for example, if someone wants to enter a joke containing several paragraphs. In our current system, you can enter HTML code directly in the "Create New Joke" form. So why is this unacceptable?
Let us return to the foreword, one of the most important features of a database-driven Web site is to add content to it, do not need to be familiar with HTML. If you need to use a joke to use HTML knowledge, this violates our initial principles.
In Chapter VII, we will see how to use PHP's characteristics to provide a simple way to format content without the need for managers to understand the details of HTML. We will also restore the "Submit Your Own Joke" connection to secure us to the content provided by the visitor. (Responsible editor Yubei
lvye@staff.ccidnet.com)
