Database Operations


Now that you have chosen a platform on which to develop pages, the next step is to determine what database you want to use for the back-end.

Tip

The user-facing portion of your website is generally referred to as the front-end, whereas the database and code that is processed by the server are generally referred to as the back-end.


There are quite a few relational database management systems on the market today, spanning the spectrum when it comes to price, portability, and power. Those databases that are extremely powerful usually (but not always) come with a high price tag. But how much you need to pay and how powerful you need the database to be depend on the expected usage of your web application. Will you be competing with eBay? Then you probably want to fork out the dollars for a high-end database such as SQL Server or Oracle. Are you creating a catalog that will serve your customer base of 500 companies? Then you would probably be better off saving yourself some coin and going with a MySQL database or Microsoft Access database.

Tip

Before I get lambasted with emails telling me that I shouldn't compare MySQL with Access because it far outperforms Access, let me state that my comparison here was one of pricing. For those who aren't familiar with MySQL and are looking for a very powerful database management system that is low cost, definitely look into MySQL. The biggest drawback of MySQL, however, is that there is no commercial support for the application, something that corporate America hasn't quite grown comfortable with.


Regardless of which database you choose, you need to create a communication conduit between your website and the database called a connection.

The Database Connection

The next step in developing a database-driven site is to build a connection to the database. All database connections are managed via the Databases panel, shown in Figure 29.3.

Figure 29.3. The Databases panel is where all connections are stored.


If the proper steps have not been taken to facilitate the creation of a database connection, Dreamweaver lets you know the steps that you need to take before you can proceed. If you have already created the ODBC connection to the database in the ColdFusion Administrator in Chapter 21, "Putting It All Together: Configuring ColdFusion for Database Connectivity," your Databases tab should look like Figure 29.4 and you are ready to build a connection.

Figure 29.4. All the requisite steps have been completed and Dreamweaver can create a connection to the database.


Note

When you created a connection to the database in Chapter 21, you created a connection between the database and the ColdFusion server. The second connection that needs to be created within Dreamweaver is a connection between your pages and the ColdFusion server.


Because the Retro's Cycles site is using ColdFusion, Dreamweaver automatically generates a list of data source names (DSNs) that were built with the ColdFusion Administrator. As you can see in Figure 29.4, the dsnRetrosCycles DSN is already in the list, so we would be ready to move on to the next step.

If, however, you are using a server platform such as ASP or PHP, you need to create either an ODBC DSN on your machine or a custom connection string by clicking on the plus sign in the Databases panel and choosing the appropriate connection method.

Note

For instructions on setting up a DSN in Windows, check out this Macromedia TechNote: http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_19072 For instructions on setting up a custom connection string, check out this Article: http://livedocs.macromedia.com/dreamweaver/mx2004/using/wwhelp/wwhimpl/common/html/wwhelp.htm?context=Using_Dreamweaver&file=28_con17.htm


The Search Form

Although search forms aren't necessarily required for displaying dynamic data, they are frequently used to pass criteria to a Results page, so it's worth taking some time to look over and understand them before you jump into the dynamic pages. Figure 29.5 shows a basic form with a single text field and a Submit button.

Figure 29.5. A simple search form.


By selecting the form and taking a look at the Property inspector (see Figure 29.6), you can see that the form is named fmSearch and, when submitted, passes its content to the search_results.cfm page, using the GET method.

Figure 29.6. The form's properties.


In addition, clicking on the text field and taking a look at the Property inspector tells you that the text field is named tfSearch (see Figure 29.7).

Figure 29.7. The text field's properties.


This means that when the user types Honda in the text field and clicks the Search button, the contents of the form are passed to the search_results.cfm page via the querystring (because you are using the GET method). To accomplish this, the search form constructs a URL that contains the form data and looks something like this:

http://www.retroscycles.com/searchresults.cfm?tfSearch=Honda

This URL now presents you with the opportunity to extract data from the database that matches the search criteria and display it on your pages.

Note

The difference between the GET and POST methods is the fact that GET passes the contents of the form via the URL to the target page, which means the contents of the form are visible in clear text. POST, on the other hand, passes the data behind the scenes, which means the data is not visible in clear text. Both have their pros and cons, but a good rule of thumb is to use GET unless your data should be protected (as with credit card numbers, for example) or you are passing large amounts of data. In those cases, POST is the better choice.


Constructing a Recordset

After you have your database connection created and tested, the next step is to learn how to retrieve content from the database. Each relational database uses a derivative of the Structured Query Language (known as SQL and pronounced "see-quel" or "ess-cue-el," depending on who you are talking to).

SQL can be used to specify what data should be retrieved from a database and even perform actions such as inserting, updating, or deleting data.

Note

The syntax and use of SQL is beyond the scope of this book, but even without a complete understanding of the SQL language you can still use Dreamweaver to have interactions with databases. If, however, you are planning on developing dynamic applications, I would highly recommend that you spend some time learning about SQL and its applications.


In the case of retrieving data, SQL enables you to specify limitations on the types of data that you want retrieved. For instance, a query that you might perform might ask the application to retrieve all the database fields from the tbInventory table where the Make field is equal to the value that has been submitted via the search form. After the query is executed, all the records that match the criteria are returned in the form of a recordset. Think of a recordset as a subset of the entire database that contains only the specific information that was requested.

Luckily, Dreamweaver makes creating recordsets extremely easy. To build a new recordset, open the Server Behaviors tab on the Application panel by choosing Window, Server Behaviors. Click the plus sign and choose Recordset from the menu. As shown in Figure 29.8, the Recordset dialog box provides an easy-to-use interface for gathering data from the database.

Figure 29.8. The Recordset dialog box enables you to extract data from your database.


The first step in creating the recordset is to give it a name. As with other page objects that we have discussed, it's a good idea to give your recordsets names that mean something. So a recordset that retrieves all the search results might be named rsSearchResults.

The next step is to choose the data source from which you want to extract data. This drop-down contains all the database connections found in the Databases panel. Because a successful connection was created to the Retro's Cycles database, you could choose the dsnRetrosCycles database. If the database were secured with a username and password, they would need to be entered so that the recordset could access the data.

Next, you need to specify from which table the data should come. Because you want to search the inventory, the tbInventory table would be selected.

Finally, you can apply any filters to ensure that only the data you want is retrieved. In this case you would choose to select all the columns in the table, and set the Filter field so that Make is equal to the URL parameter tfSearch (see Figure 29.9). This tells the recordset to extract the value of tfSearch from the URL (also called the "querystring") and compare it to the data stored in the Make field.

Figure 29.9. The recordset has been created and is ready to test.


To see whether or not the recordset is retrieving only the data that you have asked for, click the Test button and Dreamweaver asks you to enter a test value. If you type Honda and click OK, you can see that Dreamweaver displays the first 25 records in the recordset that have the Make field set to Honda. As you can see in Figure 29.10, all the motorcycles in the results are Hondas.

Figure 29.10. The recordset contains the correct data.


If you are proficient in using SQL and want to expand on your SQL queries or use some of the advanced functions within SQL, you can click the Advanced button and customize the query to meet your needs by using the Advanced view (see Figure 29.11).

Figure 29.11. The Advanced view of the recordset enables you to build your own custom SQL queries.


To finish building the recordset, click OK and the recordset is added to the Server Behaviors panel and the Bindings panel.

Adding Dynamic Data Bindings

After you have created a recordset that contains the appropriate results, the next step is to add data bindings to your page. Data bindings are placeholder blocks of code that, when rendered in the browser, are replaced by the actual data from the database. The available data bindings are located in the Bindings panel, shown in Figure 29.12.

Figure 29.12. The Bindings panel shows all the available recordsets and their corresponding data bindings.


To add a data binding to your page, expand the recordset from which you want to choose data by clicking the plus sign and then dragging the placeholder you want from the panel to the page. After the placeholder is in place, Dreamweaver indicates that it is dynamic by highlighting it (see Figure 29.13).

Figure 29.13. The data binding has been added to the page.


Tip

You can also add data bindings to your page from the Server Behaviors panel. With the panel open, click the plus sign and choose Dynamic Text from the menu. In the Dynamic Text dialog box that is opened, select the binding that you want and click OK.


Using Server Behaviors to Avoid Errors

When using dynamic data, one of the most common mistakes developers make is to forget to account for the rare occurrence that a recordset does not contain any records. This might occur because of a typo in the SQL query, or because the type of data requested just doesn't exist in the database.

For instance, suppose you were building an events calendar for your company. The calendar contained hyperlinked dates that, when clicked, displayed all events that were scheduled to occur on that date. Sounds great, right?

But what happens when a user clicks on a date when nothing is scheduled? You might automatically think that nothing would display, right? Unfortunately, that's wrong. What happens, instead, is that the application server tries to display something that doesn't exist and it gets confused. As a result, your browser throws a nasty error like the one shown in Figure 29.14.

Figure 29.14. This browser error occurred because the requested data didn't exist in the database.


To avoid errors such as this one, the best thing to do is to define two regions in the page. The first region displays the contents of the recordset if it is not empty. The second region displays a block of text that lets the user know that there are no results.

Tip

A good way to accomplish this is to create a table in the area of your page that displays the content. The table should have one column and two rows. The top row displays the data if the recordset is not empty, and the bottom row displays your text alerting the user that no data matches the request.


The tools used to accomplish this are called server behaviors and are found on the Server Behaviors panel, shown in Figure 29.15.

Figure 29.15. The Server Behaviors panel.


To display a region only when the recordset contains data, highlight the data or table region that you want displayed, and click the plus sign on the Server Behaviors panel. From the menu, choose Show Region, If Recordset Is Not Empty. From the Show Region If Recordset Is Not Empty dialog box (see Figure 29.16), choose the correct recordset and click OK.

Figure 29.16. Select the recordset that governs the server behavior.


When the server behavior is applied, Dreamweaver places a border around it and an indicator tab to show which region is affected (see Figure 29.17).

Figure 29.17. The server behavior has been applied to the region.


Next, you need to create the area of the page that displays only if the recordset contains no data. To do this, add the appropriate text to the page that lets the user know that no data was found and then select the text or table row. Next, click the plus sign on the Server Behaviors panel and choose Show Region, If Recordset Is Empty from the menu. From the Show Region If Recordset Is Empty dialog box (see Figure 29.18), choose the correct recordset and click OK.

Figure 29.18. Again, select the recordset that governs the server behavior.


With these regions in place, you can be sure that your visitors see only your message, as opposed to the messy browser error message.

Using the LiveData View

After you have added your data bindings to the page, you can see what they would look like when rendered in the browser by clicking the Live Data View button on the Document toolbar (see Figure 29.19).

Figure 29.19. The Live Data View enables you to see what your data would look like when rendered. Because no search term was passed to the recordset, it is empty and the appropriate data is visible.


The data displayed in the Live Data View is the first record found in the recordset and is meant to give you an idea as to whether or not your design is functioning as expected. To fully test the page, be sure to preview it in your browser.



Special Edition Using Macromedia Studio 8
Special Edition Using Macromedia Studio 8
ISBN: 0789733854
EAN: 2147483647
Year: 2003
Pages: 337

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net