While it’s not my preferred method for connecting to a MySQL server, writing queries, or displaying results (for reasons I will discuss shortly), using DreamWeaver’s “point-and-click” tools to create a MySQL connection does have the benefit of being fast and relatively easy to understand, making it a useful starting point for exploration of MySQL.
If you want to use DreamWeaver's built-in methods for including MySQL data on a page, you first need to make sure that your DreamWeaver site is set up completely correctly: the smallest error in site configuration, even one that didn’t have any consequences before, will matter now. (I’m assuming for the purpose of this exercise that you have access to a MySQL server, and have already created a database table with some information.)
Next, create a new PHP page and save it in your site folder. This will be the page on which we display the results of our database query. Getting to the point of displaying live database information on a web page will require three steps:
- Providing DreamWeaver with information about our MySQL server, including a username and password for authorization.
- Creating a query that interacts with a database table. (Extracting, inserting, or modifying data).
- Displaying this data on the page.
On your new PHP page in DreamWeaver, switch into Design Mode. (While this step isn’t technically necessary, it will eliminate some possible distraction, and serve as an insightful final reveal at the end.)
We’re going to work backwards in this lesson, telling DreamWeaver that we want to put database information on a web page and then stepping through the procedures necessary to get there. From the DreamWeaver menu, choose Insert / Data Objects / Dynamic Table.
(“Dynamic” is just DreamWeaver’s way of saying “the information that is displayed comes from (or will be inserted into) a database”.)
A window will pop up checking that you have completed all the steps necessary to get to this stage. If your site is set up correctly, everything in the list should be ticked off except for the last step, Create A Recordset. (“Recordset” is Dreamweaver’s term for a MySQL query). Click on that last link now.
The window that now appears asks you to name the recordset. The name you use can be anything, so long as it follows the naming convention rules. The window then asks for for a connection, which you must create: click on Define, and then, in the window that appears, New.
You should now see the MySQL Connection window. Again, the name for your connection can be anything that follows the rules.
For the MySQL server name:
- If you are on a web host that provides MySQL, the server name should have been provided to you: it is usually the same as the domain name, but does not have to be.
- If you’re trying to connect to MySQL running on your own machine (i.e. you’re running MAMP. XAMPP or some other local server setup) the server name will be localhost. You may need to add the port number to this: if so, the full information will be localhost:8888
- If you’re in one of my classes, the server is webapp.ict.sait.ca (Note that you cannot reach this server from outside the SAIT campus).
You also need to enter your username and password for connecting to the database:
- If you are on a web host that provides MySQL, the username and password is usually the same as those that you use for FTP, but does not have to be.
- If you’re trying to connect to MySQL running on your own machine (i.e. you’re running MAMP. XAMPP or some other local server setup) the username will probably be root, with no password required.
- If you’re in one of my classes, use the username and password you have been provided with.
You’ll want to test this connection with the Test button, and then choose the database table you want to query information from. Keep with the basic query suggested by DreamWeaver for now.
That should produce a basic dynamic table on your PHP page: the data within braces and highlighted in light blue in the page shown in Design Mode is DreamWeaver’s representation of data from the database, which will not be completed until the page is seen in a browser. (You should go ahead and do that now, using File / Preview In Browser).
Now switch to Code View; you’ll also want to open up the File window at the same time. You’ll see several things:
- The generated page uses an
include, placed via a
require_oncefunction, that is given the name you provided for the connection above.
- This script is in a Connections folder; as an include, I would move it into the includes folder that should be part of your site structure.
- The PHP closes and then immediately opens again. This is inefficient: PHP lines that are next to each should be in the same tag.
- The Connections script contains the username and password to the site in plaintext: for security purposes, you should put a .htaccess file that denies visitors into the folder, preventing them from reading this file.
- The PHP code produced is long and not terribly well documented; in reality, you could write this same script in three lines of code. (DreamWeaver is doing the same thing every WYSIWYG editor does, and over coding everything).
- Once you start editing this code DreamWeaver may protest that you’re “breaking” it, meaning that the point-and-click tools I’ve shown you may no longer continue to make changes to the code.
Enjoy this piece? I invite you to follow me at twitter.com/dudleystorey to learn more.