Installing a Database

Now that you have a general idea as to what a database is and you understand some of the concepts that drive database functionality, it's time to actually choose and install one. Just like the server-side technology that you picked, the database you choose should coincide with your business practices, budget, scalability needs, feature wants, and more. Dreamweaver supports any ODBC-compliant database; unfortunately, we can't cover them all in this book, so for development purposes, the decision has been made to support the following three databases:

• Access: Access is Microsoft's database solution for developers and small companies who desire to build and/or house data within a small yet reliable store. Access supports queries, keys, security, and relationship management but falls short of supporting important database features such as stored procedures.

• MSDE/SQL Server 2000: SQL Server is the enterprise alternative for smaller and/or open source databases such as Access and MySQL. SQL Server 2000 and its successor SQL Server 2005 (coming soon) support important database functionality such as stored procedures, views, keys, security, relationship management via diagrams, and more. The downside is that SQL Server is an enterprise level database and as such costs a lot of money. If your organization plans on buying SQL Server at some point in time, you can always develop using the free, development version of SQL Server in MSDE. Microsoft Desktop Engine or MSDE is a free alternative for SQL Server developers who want to harness the power and flexibility of SQL Server now but plan to upgrade later.

  NOTE

  As of this writing, Microsoft is working on SQL Server 2005. If you want to take a crack at working with the newest database solutions available from Microsoft, you can download the 2005 Express Edition for free at http://lab.msdn.microsoft.com/express/sql/default.aspx.

  
  

• MySQL: Gaining a lot of steam over the years is the free, open source database known as MySQL. MySQL provides a free, robust alternative to costly databases options such as SQL Server. The upside is obvious: its cost. The downside becomes obvious with its use. Tool support, while available, is inconsistent and at times tools overlap their functionality. Furthermore, unless you plan on downloading and working with the beta version of MySQL 5.0, important features such as views and stored procedures are not supported. Additionally, relationship management and referential integrity are not supported in MySQL. It's important to note that the PHP model in Dreamweaver only supports MySQL. As it relates to this book, if you choose the PHP server-side technology, you must use MySQL.

TIP

Although we've made the decision to support MySQL for PHP development in this book, in reality, Dreamweaver can connect to many more. Interakt has a product by the name of PHakt that allows you to connect your PHP applications in Dreamweaver to virtually any database. More information on that product can be found at the following URL: http://www.interaktonline.com/Products/Free-Products/PHAkt/Overview/.

Of course, there's nothing stopping you from installing all three options. Access is file-based which makes it no different than having Word, Excel, or even Dreamweaver open. MSDE and MySQL run as independent Windows services which means that they'll never overlap and data is always guaranteed to be stored independently of the other storage mechanism.

NOTE

Again, because of size constraints, examples in this book use Access. Although references are always made to other database products, the core functionality of the Dorknozzle project is added under the assumption that you're using Access. For the most part, database integration in Dreamweaver is handled exactly the same, regardless of which datatbase you use. With that said, after you've defined the database in Dreamweaver (covered in Chapter 25, "Working with Dynamic Data") working with database data is exactly the same regardless of the database you choose.

Installing Access

Access is Microsoft's database solution for developers and small companies alike who desire to build and/or house data in a small yet reliable store. Because Microsoft Access is cheap and easily attainable (either from Microsoft's website or as an installation option with Microsoft Office), it's usually the perfect choice for discussion and use in books such as this one.

For the most part, Microsoft Office includes Access. If you've already installed Microsoft Office on your computer, but didn't install Access at the same time, you'll need to add it to your installation. The following assumes that you have Microsoft Office 2000, XP, or 2003 handy and that you will be installing from that CD:

1.	Navigate to the Add or Remove Programs menu located within the Control Panel.
2.	Select your Microsoft Office installation from the Programs menu and select Change.
3.	When the Microsoft Office Setup dialog appears, select Add/Remove Features and click Next.
4.	Select Run from My Computer from the Access program menu.
5.	Click Update. You are prompted to insert your Microsoft Office CD, so make sure that it is handy. Access is now installed.

That's all there is to it. You are now ready to begin working with Access.

TIP

Of course, if you don't have Microsoft Office 2000, XP, or 2003, you're not completely out of luck. You can download and install a 45-day free trial from Microsoft's website at http://www.microsoft.com/office/access. Also on the website, you can find the latest updates, news, and purchase information for Microsoft Access.

The Access File Manager

Although Access doesn't work within the framework of what is considered a DBMS, its built-in file manager does offer some similarities. Remember that Access databases are self-contained MDB files that, when double-clicked, open within the Microsoft Access program also shown in Figure 23.11.

Figure 23.11. Access database files are self-contained MDB files that open in Microsoft Access.

Using this file manager, you can create tables, columns, data types for columns, and even add rows with data to the tables. Furthermore, you can perform standard database operations such as create queries, manage security restrictions, back up a database, and more.

If you'd like to explore the Dorknozzle Access database, simply browse to the Database folder for the Dorknozzle project you plan on using and double-click the Dorknozzle.mdb file. We'll explore this with more detail later in the chapter.

Installing MSDE

SQL Server 2000 is Microsoft's database solution for medium to large companies and enterprises. It is quite a bit more expensive than Access, generally requires its own dedicated "database server," and at times requires the hiring of a certified database administrator (DBA) to maintain. However, it also offers a robust and scalable solution for larger web applications.

I will assume that if you're reading this book, you probably don't want to invest in something as massive as SQL Server 2000 and that your needs are better suited to something free but just as powerful for testing and development purposes. If this is the case, then Microsoft's SQL Server Desktop Engine or MSDE is perfect for you. MSDE is Microsoft's free database alternative to SQL Server 2000. It functions and stores data exactly as SQL Server 2000 does but is meant for development purposes only. After you get your feet wet with MSDE, you might like to think about upgrading to a more robust and licensed database in SQL Server 2000.

To download and install MSDE, follow these steps:

1.	Browse to the MSDE website located at http://www.microsoft.com/sql/msde/downloads/download.asp. From this page, select your preferred language and click Go. Scroll down to the bottom of the page you are redirected to and download the MSDE2000A.exe file to your computer.
2.	When the executable has been downloaded to your computer, double-click the file to begin the installation.
3.	The first screen that appears is the license agreement; click I Agree to continue.
4.	You are asked to provide the path to the location where the installer package will unpack the required setup files. Accept the default C:\MSDERelA path and click Finish. The files are unpacked to the MSDERelA folder within the root of C:\.
5.	Open the Command Prompt by selecting Run from the Start menu; type CMD and click OK.
6.	In the Command Prompt, change to the directory where you extracted the files by typing the CD command as follows: CD C:\MSDERelA
7.	Run the following command to set up MSDE: Setup.exe /qb+ INSTANCENAME=NetSDK DISABLENETWORKPROTOCOLS=1 SAPWD=<YOUR PASSWORD> It's extremely important that you type a unique password for the SAPWD parameter. MSDE needs this value to begin the installation.
8.	MSDE now installs. When it finishes, close the Command Prompt.

Now that you've successfully installed MSDE, you're ready to start the service. The easiest way to start the MSDE service is to launch the service manager. You can do this by following these steps:

1.	Click Start, Programs, Startup and choose the Service Manager option. The SQL Server Service Manager opens, similar to Figure 23.12. Figure 23.12. The SQL Server Service Manager allows you to start and stop MSDE.
2.	Type YOURCOMPUTERNAME\NETSDK in the Server drop-down list box (where YOURCOMPUTERNAME is the network name of your computer), and click Refresh Services. MSDE should then connect, and the green triangle should appear.
3.	Click the Start/Continue icon to start MSDE.
4.	Close the SQL Server Service Manager.

After you close the Service Manager, it continues to run out of sight in the system tray. You'll notice a small icon that looks like a cylinder with a play icon on top.

Web Data Administrator

To use MSDE effectively, you'll need to download and install the Web Data Administrator. Web Data Administrator is a free DBMS provided by Microsoft that allows you to manage your instance of MSDE locally and remotely using a web-based interface. What's nice about Web Data Administrator, aside from the fact that it's free, is that it lets you manage databases, tables, columns, column types, stored procedures, and more.

To download and install Web Data Administrator, follow these steps:

1.	Navigate to http://www.microsoft.com/downloads/details.aspx?FamilyId=F0D03472-5E6C-459E-A6D8-6745A729C3C9&displaylang=en and click the download button to begin the download of the WebDataAdmin.msi file.
2.	After you've downloaded the file, double-click the .MSI file to install Web Data Administrator.

after it's installed, you can access the Web Data Administrator through your browser at the URL http://localhost/SqlWebAdmin. Before you can use it however, you must enable what is known as SQL Mixed Mode authentication. This process involves making a small change to the registry, but don't be put off. If you follow these instructions exactly, you won't do any harm:

1.	Click Start and choose Run.
2.	In the Run dialog, type regedit and press Enter to open the registry editor.
3.	Expand the HKEY_LOCAL_MACHINE node in the left pane. Expand the SOFTWARE node. Expand the Microsoft node. Expand the Microsoft SQL Server node. Expand the NETSDK node. Select the MSSQLServer node. Find the key (in the right pane) called LoginMode.
4.	Double-click the LoginMode key and change its Value data from 1 to 2, then click OK.
5.	Close regedit and restart your computer.

Open Web Data Administrator in the browser using the URL http://localhost/SqlWebAdmin. You'll be asked for the login, password, and server name for your instance of MSDE similar to Figure 23.13.

Figure 23.13. Web Data Administrator requires you to provide the user name, password, and server instance.

Type sa in the User Name text box and type the password you supplied during the installation of MSDE in the Password text box. If you're unsure what the name of your server is, double-click the database engine icon in the system tray. The name of your server is located in the Server drop-down menu. Enter this value into the Server text box of the login screen for Web Data Administrator.

Click Login; you will see a list of the default databases currently available in MSDE.

Now that you're logged into Web Data Administrator, the next step is to import the pre-built Dorknozzle database into MSDE. Of course, the import process can be handled directly from Web Data Administrator. To import the pre-built Dorknozzle database, follow these steps:

1.	In the Web Data Administrator screen, click the Import link located in the navigation menu on the left. The Import Database page appears.
2.	Click the Browse button, locate the dorknozzle.sql file (located in the Database folder for the specific Dorknozzle folder you plan on using) and click Open.
3.	Click the Import button to import the Dorknozzle database. This process can take several seconds. When finished, a green success message appears.

To see the imported database, click the Databases link. As you can see from Figure 23.14, the Dorknozzle database is now contained within our MSDE instance.

Figure 23.14. The Dorknozzle database is now imported into our instance of MSDE.

If you find some time, explore the features of Web Data Administrator. Practice creating databases, tables, columns, and so on. Doing so will help you get acquainted with Web Data Administrator's interface and its functionality.

Installing MySQL

Gaining a lot of steam over the years is the free, open source database known as MySQL. But don't let the free or open source references fool you. MySQL has proven over the years to be as robust, secure, and scalable as any enterprise alternative including Microsoft's SQL Server, Oracle, or IBM's DB2.

If your goal in reading this book is to side with the open source movement, then MySQL is your best bet and is available as a free download from http://www.mysql.com. To download and install MySQL, follow these steps:

1.	Go to http://dev.mysql.com/downloads and click the MySQL 4.1 link. You are redirected to a page that allows you to select your operating system.
2.	On the operating system selection page, scroll down and find the Windows downloads; then click the Pick a Mirror link in the Windows Essentials (x86) section. You are redirected to the registration page.
3.	If you choose to register, go ahead and do so. Otherwise, click the No Thanks, Just Take Me to the Downloads! link. You are redirected to the mirror selection page.
4.	Click a desired mirror and begin downloading the MSI installer.
5.	After you've downloaded the installer, click the mysql-essential-4.1.11-win32.msi file to begin the installation.
6.	Click Next to accept the legal disclaimers and terms of usage.
7.	Accept C:\mysql as the default directory for MySQL and click Next.
8.	Select the Typical Installation option and click Next.
9.	When the files have been installed, click Finish.

That's it! You've successfully installed MySQL. To ensure that the installation was successful and that the database server is running, click Start, Settings, Control Panel, Administrative Tools, and Services. In the Services window, scroll down and find the MySql option. If the status reads started, the database server is indeed started and the installation was successful. If it's listed but not started, right-click the option and choose the Start option from the context menu.

You're now ready to begin developing web applications that use MySQL as the data storage mechanism. Before you begin however, you'll want to install the free MySQL DBMS MySQL Administrator. Let's discuss that next.

MySQL Administrator

To use MySQL effectively, you'll need to download and install MySQL Administrator. MySQL Administrator is a free DBMS that allows you to manage your instance of MySQL using a slick and easy-to-use interface. What's nice about MySQL Administrator, aside from the fact that it's free, is that it lets you manage databases, tables, columns, column types, security, and more.

To download and install MySQL Administrator, follow these steps:

1.	Navigate to http://dev.mysql.com/downloads and click the MySQL Administrator link located in the Graphical clients section. You are redirected to an operating system selection page.
2.	Under the Windows downloads section, choose the Pick a Mirror link for the Windows (x86) option. You are redirected to a mirror selection page.
3.	Choose a mirror to begin downloading the .MSI installer file.
4.	When you've downloaded the mysql-administrator-1.0.20-win.msi file, double-click the installer to begin the installation for MySQL Administrator.

After you've installed MySQL Administrator, you will see a shortcut on your desktop. Double-click that shortcut to open the MySQL Administrator login similar to Figure 23.15.

Figure 23.15. MySQL Administrator requires you to provide the user name, password, and server instance.

Unless you specified a login and password during the installation, you can type localhost in the Server Host text box and type root in the Username text box. Leave the Password text box empty and click OK to open MySQL Administrator.

Now that you're logged into MySQL Administrator, the next step is to import (Open Source uses the term restore) the prebuilt Dorknozzle database into MySQL. Of course, the restore process can be handled directly from MySQL Administrator. To restore the prebuilt Dorknozzle database, follow these steps:

1.	In MySQL Administrator, click the Restore category located in the navigation menu on the left. The Restore pane, defaulting to the General tab, appears.
2.	Click the Open Backup File button located at the bottom of the page. The Open dialog appears.
3.	Locate the dorknozzle.sql file located in the Database folder for the DorknozzlePHP project and click Open. Fields that were previously disabled in the open dialog are now enabled.
4.	Keep all the default settings on the General tab and click the Start Restore button.
5.	MySQL begins the restore process. Click Close when the restore process finishes.

To see the imported database, click the Catalogs link located in the navigation menu on the left side. As you can see from Figure 23.16, the Dorknozzle database is now contained within our MySQL instance.

Figure 23.16. The Dorknozzle database is now restored into our instance of MySQL.

If you find some time, explore the features of MySQL Administrator. Practice creating databases, tables, columns, and so on. Doing so will help you get acquainted with MySQL Administrator's interface and its functionality.

For the most part, you've downloaded and installed everything you need to get started with PHP and MySQL without the use of Dreamweaver. However, because we plan on using Dreamweaver to build PHP pages that tie into a MySQL database, you'll need to perform some minor tweaks on the php.ini file as well as download some additional files to get Dreamweaver to work correctly with PHP and MySQL.

Configuring PHP to Work with Dreamweaver

Although it might seem that you've just done everything you need to do to get PHP and MySQL working with Dreamweaver, think again. You must make two minor adjustments for Dreamweaver to recognize your MySQL database under the PHP server model:

• Download and unpack the PHP 5.0.4 zip package. This exposes a new "ext" folder that contains the php_mysql.dll file. Dreamweaver needs this .DLL to communicate with your MySQL database through PHP.

• Configure the php.ini file to read the "ext" folder as well as the php_mysql.dll file.

Understanding the goal of this section, let's now walk through the steps. First, you want to download and unpack the PHP 5.0.4 zip package. You can do this by following these steps:

1.	Navigate to http://www.php.net/downloads.php in your browser.
2.	On the page, select the PHP 5.0.4 zip package. You are redirected to a mirror selection page.
3.	Select a mirror and begin downloading the zip package.
4.	After it is downloaded, open the zip package and extract the files to the C:\php folder, overwriting any files along the way.
5.	After you've successfully unpacked and replaced the files in the C:\php directory, your directory structure will resemble a much more extensive one similar to Figure 23.17. Figure 23.17. Unpack the files in the PHP 5.0.4 zip package into C:\php, overwriting the necessary files along the way.

Look in the C:\php\ext folder. Halfway down the list, you'll have a php_mysql.dll file. This is the file that Dreamweaver needs to communicate with MySQL through its interface. Before that communication can be established, however, you'll want to configure the php.ini file. The php.ini file is a configuration file tapped by Dreamweaver before actually communicating with the php_mysql.dll file. If the php.ini file doesn't make reference to that .DLL (or if the reference is commented within the .INI file), communication won't be established. To configure the php.ini file now, follow these steps:

1.	Open the php.ini file located in C:\Windows. By default, the file should open in Notepad. To use line numbering, try opening the file in Dreamweaver.
2.	Scroll down to line number 450 and change the line extension_dir = "./" to read extension_dir = "c:\php\ext".
3.	Scroll down to line number 578 and uncomment the ;extension=php_mysql.dll line by removing the leading semicolon.
4.	Save the php.ini file and close it.
5.	When you modify php.ini, you must reset IIS: Restart IIS by choosing Start, Run, and typing the iisreset command. Click OK to restart IIS.

That's it! You're now ready to begin using the MySQL database under the PHP server model in Dreamweaver. More on that in a couple of chapters.