Dreamweaver CS3 has the ability to connect to your ISP hosted MySQL database, but even for the experienced developer establishing the connection for the first time is not always straightforward or intuitive. This document is intended to provide some time saving guidance to help you achieve that Dreamweaver to MySQL database connection without the pain.
It is assumed that you have your local site defined and saved, in which case the "Local Info" section of your Dreamweaver Site Definition should look something like this :
It is also assumed that you have successfully set up ftp access to your ISP webspace, in which case the "Remote Info" section of your Dreamweaver Site Definition should look something like this, bearing in mind that there are other formats of FTP login and you must substitute those that are applicable to your own ISP :
Now things start to get a bit more interesting. In order to connect to an ISP hosted MySQL server, we have to set up the "Testing Server" section of our Dreamweaver Site Definition. Initially only the first two options will be visible. In the first we need to tell Dreamweaver the type of Server we will be using, in this case "PHP MySQL". Secondly we need to tell Dreamweaver how it will access our web space. Select "FTP", and the remaining options will appear so that this section now looks something like this :
When you get to this stage you will notice that Dreamweaver provides default values in most of these fields, and that these are mainly established from the information you have already provided in the "Remote Info" section.
Here is the catch. Have a look at the "URL prefix" field (Highlight 1), the original default Dreamweaver supplied value in this case was :
By default Dreamweaver has simply used the "FTP Host" address that you have provided and then appended the "Host Directory". There is therefore an unwanted appended "public_html" to what is actually required, and in this case there is also an unwanted "ftp" instead of "www". This simple "bug" is enough to prevent Dreamweaver from ever connecting to your MySQL server, and this field is probably the first place to look for a problem if you have connection difficulties.
So why is this a problem, and what is going on?
In order to allow Dreamweaver to communicate with your MySQL database server while connected by FTP, Dreamweaver requires to copy a couple of dynamic PHP scripts into a Dreamweaver created directory in the root of your webspace. It is the "URL prefix" setting which Dreamweaver uses for this task, and it must therefore point to your site root rather than your public_html directory.
These two files are stored in a directory called "_mmServerScripts". The first file, "mysql.php" , we don't need to worry about. The second file, "MMHTTPDB.php", however is specific to the local machine that you are using for your development work. If you try to make a Dreamweaver database connection from another machine, it will fail, until you manually delete this file using whatever FTP software you choose, therefore allowing the second machine to recreate the file which will now be specific to that second machine. Not convenient if you regularly work from more than one location, or if more than one person is working on a project. Perhaps not a bug, but a limitation that needs to be remembered.
Having reached this stage we are now ready to establish a database connection.
We need to select the "Databases Tab" of the "Application" pane. In order that this is available for use, we must have a "php" page opened for editing, or create a new one if required. Now click on the "+" button to create a new Database Connection.
You should now be looking at a pop up dialogue which looks something like this :
The "Connection name" can be anything you choose to provide meaningful information of your purpose.
The "MySQL server" setting (highlight 1) needs to be set to "localhost". Now your first reaction to that is going to be that this is not correct, the MySQL server does not reside on my local development machine. Remember those php scripts that Dreamweaver needed to copy into the root of your webspace? Those files provide an interface between your Dreamweaver FTP connection and your MySQL database server. This means that everything is being processed in your own webspace, which is indeed "localhost". Many ISP companies will only allow localhost connections for security reasons, and by using this method, Dreamweaver very cleverly provides connectivity in these circumstances.
The "User name" setting (highlight 2) is the user name for your MySQL database, and is often the same as the actual database name. Note that in many ISP cases this will not be the user name you typed in when creating your database, but may have acquired a prefix automatically assigned by your ISP database creation software.
The "Password" setting (highlight 3) is the password you entered when creating your MySQL database.
The "Database" setting (highlight 4) is the name of the database you wish to connect to. Note that in many ISP cases this will not be the database name you typed in when creating your database, but may have acquired a prefix automatically assigned by your ISP database creation software.
You can now click on "Test" in order to establish if you have a working connection, and by clicking "OK" your settings will be saved for future use.
At this stage you should be able to see any tables in your database listed in the "Databases" tab of the "Application" pane. In Dreamweaver 8 there is a bug which prevents this display if the database name contains a non-alphanumeric character, and this can often be the case where your ISP has prefixed your database name. An update bugfix for Dreamweaver 8 is available from the Adobe website. This bug is not present in Dreamweaver CS3.
This concludes the purpose of this document, and you are now ready to create forms for data display, entry or editing, using options available on the "Bindings" and "Server Behaviors" tabs of the "Application" pane.
Please note that if you connect in this way to a "live" site, with an established and working database, then anything you do in testing is going to impact on that "real" data. For this reason it is often wise to test on an alternative database set up for the purpose.
Have fun.