In part one of this two part article we took a look at a simple way to create a fully functional browser-based HTML editor. In this article Mitchell shows us how to put it to good use, creating a database driven news system using the WYSIWYG editor, ASP and SQL Server 2000.
Building a WYSIWYG HTML Editor Part 2/2 - Creating the database (Page 2 of 6 )
We're a hard working bunch at devArticles.com... we really are! I've been keeping everyone informed of our new site, Socket6 over the last couple of weeks. Socket6 is a developer news and "cool stuff" site, and in this article we're going to create something similar, which will display news posts by topic or by author. We will embed our HTML Editor into a web page that collects various details for news posts and we will also create forms to add topics and authors.
First off however, we need a database. I prefer to query "by hand", so open Query Analyzer and enter the following code to create our database:
USE MASTER GO CREATE DATABASE myNews GO
We've just created our new database called myNews. Erase the code in the query analyzer window and enter this code, which will create the news table for our database:
USE myNews GO CREATE TABLE newsPosts ( postId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, title VARCHAR(50), topics VARCHAR(50), newsPost TEXT, authorId INT ) GO
As you can see, our news table is fairly simple and contains just four fields:
postId: A unique numerical identifier that we will use to reference each post.
title: The title of the news post.
topics: The Id's of the topics under which this article can be shown. We're about to create the topics table, and each topic in this field will be separated by a comma, for example 3,5,6,8. We can then use SQL's LIKE or IN functions to get news posts under specific topics.
newsPost: The actual news post. It's a text field so we don't have any restrictions on the size of the post.
As mentioned above, each news post can be categorized on a per-topic basis. The topics table is simple, and you should create it like this:
CREATE TABLE newsTopics ( topicID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, topic VARCHAR(50) ) GO
Nothing special about the newsTopics table. It contains a numerical identifier and the actual topic name. The last table we need is the author's table, which we can create with the following TSQL code:
CREATE TABLE newsAuthors ( authorId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, name VARCHAR(50), email VARCHAR(250), website VARCHAR(100) ) GO
Each author will have their name, email address and the URL of their web site stored. In this article I'm assuming that each author also runs their own website, so we will post their URL with each news post to give them credit for it, just like on Socket6. Our database is now setup and we're ready to add some records to each of our tables. To make things easier, we will create two simple forms to add both topics and authors to the database.
In this article I'm assuming that SQL Server 2000 is installed on the same machine as your IIS web server and that you're using the default sa user name. If your situation is different, just change the variables in the file described below.
Before we create our files, let's create a file that will be included by each of our pages. It will contain the details of our database. Create a new file called db.asp and save it in a directory that IIS can process. Add the following code to it:
The code above simply creates our database connection string, and we can include this file whenever we want to connect to a database so that we don't have to hard code the connection string into every page. Now, to add the topics. Create a new page called topics.asp and start by entering the following code into it:
<% dim action action = Request.Form("action") if action = "addTopic" then AddNewTopic() else ShowTopicForm() end if sub ShowTopicForm() end sub sub AddNewTopic() end sub %>
Topics.asp relies on a form variable called action to tell the script what to do. By default, action will be empty, so the ShowTopicForm sub-routine will be displayed. ShowTopicForm will display a form where we can add a new topic. It will also contain a hidden form variable called action, which will be set to addTopic, thus making topics.asp call AddNewTopic instead of ShowTopicForm when the form is submitted. Anyhow, here's what the ShowTopicForm sub-routine looks:
sub ShowTopicForm() 'Display a simple HTML form to get topic details %> <html> <head> <title> Add News Topic </title> </head> <body bgcolor="#FFFFFF"> <form name="frmNews" action="topics.asp" method="post"> <input type="hidden" name="action" value="addTopic"> <h1>Add Topic</h1> Topic Name: <input type="text" name="topicName" maxlength="50"> <br><br><input type="submit" name="submit" value="Add Topic >>"> </form> </body> </html> <% end sub
It outputs a simple HTML form that looks like this:
To keep things simple, I will only use simply error checking throughout this article. If you're implementing this tutorial into a production environment then be sure to include your own rigid error handling mechanisms.
Once the form is submitted, topics.asp will see that the hidden form variable action is equal to addTopic and will call the AddNewTopic sub-routine, which looks like this: sub AddNewTopic() 'Add the new topic to the database dim topicName dim objConn set objConn = Server.CreateObject("ADODB.Connection") topicName = Replace(Request.Form("topicName"), "'", "''") objConn.Open dbConnString objConn.Execute "insert into newsTopics(topic) values('" & topicName & "')" Response.Write "<b>New topic added OK</b>" objConn.Close set objConn = nothing end sub
For topics.asp to function correctly, there's just one more thing we need to do. We need to include db.asp by putting the following line right at the top of our topics.asp script: <!-- #INCLUDE file="db.asp" --> Run topics.asp in your web browser and add the following topics:
You'll now have seven topics sitting in your newsTopics table. We add authors to the newsAuthors table in exactly the same way that we add topics, so I'm going to skip it here. Once you've downloaded it, add the following three fictional authors with the authors.asp page:
Name: Fred Jones Email: firstname.lastname@example.org Website: http://www.fredjones.com
Name: John Smith Email: email@example.com Website: http://www.somesite.com
Name: Tim Black Email: firstname.lastname@example.org Website: http://www.timblack.com
OK, we've got our topics and author's tables setup and ready to go. We now need to make use of our WYSIWYG HTML editor, which is what we will do on the next page.