Database Coding and Platform Choices

Many of the hacks in this book rely on your ability to set up a database and connect to it with code. A database table looks something like an Excel spreadsheet, with rows (records) and columns (fields). Table P-1 shows a simple products database table.

Table P-1. An extremely simple table with three fields (columns) and as many records (products) as you wish to store in it

ID	Description	Price
0001	Acme Widget	$37.94
0002	Industrial, Co. Wicket	$12.88
0003	Krusty Brand Tongue Depressor	$0.40

Here, each record corresponds to a single product. The data is divided into three fields: a unique numeric ID ( ID ), a product description ( Description ), and the unit price ( Price ). You'll not only need to choose a database application with which to create your tables and manage your data, you'll need to include code (specific to the platform you choose) to connect to your database.

Most of the database-enabled hacks in this book cite a Structured Query Language (SQL) query to retrieve data from a database or store data back into it. In order to put these hacks to use, you'll have to customize the code for use with your server and database platform.

There are two general platforms commonly used to host web sites: Windows and Unix/Linux. These two systems can provide similar functionality, but they do so in completely different ways. The problem is that some of the more advanced code, especially code that accesses databases, might work on one platform but not the other. For instance, Windows servers have a built-in web server capable of interpreting VBScript or JavaScript that is executed in Active Server Pages (ASP). On the other hand, Unix/Linux platforms typically use the Apache web server, which can understand Hypertext Preprocessor (PHP) code (i.e., code with a .php extension). Of course, you can run ASP pages on Unix/Linux platforms using ChiliSoft ASP, and you can run PHP scripted pages on a Windows machine by installing the Windows version of the Apache web server.

Once you've chosen a server platform, you'll need to choose a database technology that works with that system. For instance, Windows servers will likely be integrated with a Microsoft Access, MSDE, or Microsoft SQL database, whereas Unix/Linux servers will likely be using MySQL, Postgres, or Oracle.

It almost goes without saying that a dynamic web site (dynamic in that the content is created on the fly) will be much more powerful with the benefit of a relational database management system (RDBMS). The examples that require a database were tested against Microsoft's SQL Server 2000 or better, but with some small modifications the examples will work with any popular RDBMS, such as MySQL or Oracle.

Many of the advanced hacks in this book reference a recordset in their instructions, so you'll need to do something like the following to deploy those hacks. This code creates a recordset named rsProducts using VBScript for ASP:

 1. connStore="DRIVER={Microsoft Access Driver (*.mdb)};DBQ="   C:/InetPub/wwwroot/ database/dbPayPal.mdb   ") 2. set rsProducts = Server.CreateObject("ADODB.Recordset") 3. rsProducts.ActiveConnection = connStore 4. rsProducts.Source = "   SELECT item_name FROM tblProducts   " 5. rsProducts.Open( ) 6. Response.Write(rsProducts.Fields.Item("item_name").Value) 

Line 1 defines the location of the database and specifies the database driver. Line 2 initiates a new recordset named rsProducts . Line 5 actually executes the database query, and line 6 sends the contents of a field to the output (in this case, the item_name column returned from the database is displayed).

To put this code to use, replace the SQL statement on line 4 with the SQL query shown in the hack you wish to use.