http://www.macromedia.com/support/dreamweaver/technotes.html

This TechNote describes a few of the important factors involved in
creating a successful MySQL database connection when using the PHP
server model in Dreamweaver. It will also cover some basic MySQL user
account settings. It's assumed that you have installed and configured
MySQL on a local or remote computer.

Errors will occur within Dreamweaver if each point listed below is not
completed correctly. A common error that can occur when testing a MySQL
connection in Dreamweaver is "An unidentified error has occurred."

Configure a MySQL user account for the web application itself
Create a PHP MySQL Site Definition in Dreamweaver
Setup a MySQL database connection in Dreamweaver

Note: This TechNote is meant to be a basic getting started guide. You
should consult the MySQL documentation and other third-party resources
to tailor the MySQL account settings to your specific security
requirements. To download and install MySQL, visit the MySQL website or
consult some of the third-party MySQL websites listed in Dreamweaver
websites (TechNote 12607).

MySQL configuration
The default installation of the MySQL database system contains two
databases named mysql and test. The mysql database contains six tables
that store information about privileges. We will discuss two of these
tables: the user table and the db table.

The user table stores information about who can connect to the MySQL
server and whether the user has any global level privileges. Because
privileges in the user table affect all databases on the MySQL server,
usually administrators will have some Ys (yes) in the privilege fields
while most standard users have only Ns (no). The db table specifies the
databases on the MySQL server that users are allowed to access, and is
where most of the standard users' privileges are stored.

Note: There are many graphic interface utilities available to help
visually manage MySQL databases, however, this TechNote uses the native
MySQL command line client.

Whether you install MySQL on a UNIX, Windows or Mac OS X machine, the
command prompt window can be used to administer MySQL. In Windows, the
command prompt is opened by selecting Start > Programs > Command Prompt.
(On some systems, the Command Prompt may be located under Accessories in
the Start > Programs menu.)

Change to the mysql\bin directory by entering the following commands at
the command prompt:

> cd\
> cd mysql\bin

During the MySQL installation, MySQL creates an account called root with
no password, which can be used to log into the database. It is highly
recommended that a password is assigned to this account since root has
full control over the MySQL databases. To assign the root account a
password run the following command, which will set the root password to
new-password. You should replace new-password with a password of your
choice that is harder to guess.

> mysqladmin -u root password new-password

It is recommended that separate MySQL accounts are created for each PHP
web application. You can create as many MySQL accounts as you wish and
assign different rights and permissions to each account. Web application
users do not need the same level of privileges as the root account.

To create a separate user account for your web application, connect to
MySQL and log in with the superuser account using one of the following
methods. In the example below, the account with superuser privileges is
the root account. MySQL should prompt you to enter a password when you
press the Enter key:

> mysql --user=root --password
or

> mysql -uroot -p

Once logged into MySQL, you will create a user called dbuser (the name
is arbitrary) for the web application. Below, are four examples of ways
to set up this new user. In all four examples, a new user named dbuser
is created. The four privileges granted to this user are SELECT, INSERT,
UPDATE and DELETE on any table in the employees database(this database
will be referred to as emp in the following examples). The password
myPassword will be encrypted in the MySQL database. In this example,
dbuser can only access the database from localhost.

GRANT SELECT, INSERT, UPDATE, DELETE ON emp.* TO dbuser@localhost
IDENTIFIED BY "myPassword";


In this example, dbuser can only access the database from mySite.

GRANT SELECT, INSERT, UPDATE, DELETE ON emp.* TO dbuser@mySite
IDENTIFIED BY "myPassword";


In this example, dbuser can only access the database from
mySite.myDomain.com.

GRANT SELECT, INSERT, UPDATE, DELETE ON emp.* TO
dbuser@... IDENTIFIED BY "myPassword";


In this example, dbuser can access the database from any host.

GRANT SELECT, INSERT, UPDATE, DELETE ON emp.* TO dbuser@"%" IDENTIFIED
BY "myPassword";




After running the GRANT statement(s), switch from the emp database back
into the mysql database by running the following command:

>use mysql

Execute the following SQL statement to observe what changes have been
made to the db table in the main mysql database: SELECT Host, Db, User,
Select_priv, Insert_priv, Update_priv, Delete_priv
FROM db
WHERE User='dbuser';


Here is how the db table will look if all of the GRANT statements listed
above are run: Host Db User Select_priv Insert_priv Update_priv
Delete_priv
localhost emp dbuser Y Y Y Y
mySite emp dbuser Y Y Y Y
mySite.
myDomain.com emp dbuser Y Y Y Y
% emp dbuser Y Y Y Y


Execute the following SQL statement to observe what changes have been
made to the user table in the main mysql database:

SELECT Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv
FROM user
WHERE User='dbuser';

Here is how the user table will look if all of the GRANT statements
above had been run: Host User Select_priv Insert_priv Update_priv
Delete_priv
localhost dbuser N N N N
mySite dbuser N N N N
mySite.
myDomain.com dbuser N N N N
% dbuser N N N N


Note: For security purposes, the dbuser account in the user table should
not be modified unless the account needs administrative rights similar
to the root or the MySQL administrator account. If these privileges are
granted, the dbuser will have access to the system database.

MySQL automatically reads the user and db tables when it starts, and
when GRANT and REVOKE statements are issued. If you make any manual
changes to the user and db tables, reload the tables to process the
changes using this command:

> flush privileges;

Setting up the PHP / MySQL Site Definition in Dreamweaver
A successful connection to a MySQL database in Dreamweaver depends on
correct Site Definition entries when defining the site. Below, is a
sample PHP / MySQL Site Definition that uses a Linux PHP server running
on a machine identified as mySite.myDomain.com. MySQL is running on
another machine identified as mysql1.myDomain.com and Dreamweaver is
running on a local workstation. FTP is used to transfer files between
the workstation and the Linux web server.

Local Info:

Site Name: mySite
Local Root Folder: C:\mySite\
Remote Info:

Access: FTP
FTP Host: mySite.myDomain.com
Host Directory: /htdocs/
Login: webadmin
Password: *********
Testing Server:

Server Model: PHP / MySQL
Access: FTP
FTP Host: mySite.myDomain.com
Host Directory: /htdocs/
Login: webadmin
Password: *********
URL Prefix: http://mySite.myDomain.com/

Create a MySQL database connection in Dreamweaver
Once the MySQL user account has been set up and the site has been
defined you can connect to your MySQL database in Dreamweaver. Using the
above settings, here are example settings for the MySQL Connection
dialog box in Dreamweaver:

Connection Name: choose a name (e.g. connEmp)
MySQL Server: mysql1.myDomain.com
User Name: dbuser
Password: myPassword
Database: enter the name of your database or click on the Select button
to choose from a list of MySQL databases running on the server.

Note: For the MySQL Server field you must enter localhost if PHP and
MySQL are running on the same machine.

MySQL utilities
There are third-party tools to help configure and manage a MySQL
database without having to know SQL. These tools are helpful to those
who prefer to work with databases through a visual interface rather than
a command line interface. These tools can be downloaded and installed on
the machine running the MySQL database or the local workstation. Some
popular tools include: PHPMyAdmin, MySQL Control Center (MyCC), EMS
MySQL Manager, urSQL, PremiumSoft MySQL Studio, and MySQLGUI.

Additional information
For more details on MySQL, please refer to the MySQL documentation or to
the third-party MySQL sites listed in Dreamweaver websites (TechNote
12607).

For more information on unidentified errors, please refer to
Unidentified error occurs when testing a PHP / MySQL connection
(TechNote 16515).

For general MySQL and PHP troubleshooting tips, please see Testing your
PHP application server (TechNote 16383).