Setting up your database

Before we can begin using Dreamweaver to connect to our database, we need to create it first.  On the phpMyAdmin opening page is a text box for you to enter a new database name and create it.  Enter the name dmxzone and click on the Create button

The database is created and you are immediately taken to the page for that database:

This is an empty database without any tables, so we'll need to create a table to work with.  Into the form enter the name of the table you want to create and the number of fields that your table will have.  To keep things simple we'll create a basic lookup table of countries.  Call the table countries and give it 3 fields.  Click on Go and you are presented with a form of 3 rows to fill in the details for each field.  Our field will have a primary key, a country code and a country name:

The first column is for the field names, the second column for the field type. MySQL has all of the field types that you would expect, from number types like integer and float to text types to date and time types.  The MySQL documentation contains a full list of the field types as well as their min and max values and sizes. 

Our primary key is an integer, our country code is a 2 character long text field and the name is a variable length, up to 40 characters.  The Attributes column allows you to specify whether the field is binary, signed or unsigned.  You can then specify whether the field can contain null or not and what its default value is.  The Extra column is where you specify if you want the column to be auto incremented - each record will have a successively larger value.  This is the same effect as the AutoNumber field type in Microsoft Access.  The last columns allow you to specify whether the field is a primary key, if it's indexed or if it should contain unique values.  The final column allows you to specify that the field is indexed for full-text searching, something that was introduced with MySQL 3.23.23.

Once you have entered the information you can click Save and phpMyAdmin will save your table and show you confirmation, along with the SQL used to create the table:

From within the phpMyAdmin interface we can now alter our table structure, insert new data, edit existing data, delete records, run custom SQL commands or export the data.  Let's quickly add a few records so that we have something to work with in Dreamweaver.  Click on the Insert tab near the top of the screen and you will be presented with a form to enter your data:

The Function column provides a list of SQL functions that you can use when inserting that columns data.  Functions like password encryption, text manipulation and date/time formatting are provided.  We'll just be entering straight data so we can leave these out. 

Let's add the United Kingdom with a country code of UK. The radio buttons below allow you to choose whether you will insert another record after this one or go back to the main database page.  We'll insert another, so select Insert another new row and click Go.  As always, phpMyAdmin displays a message as well as the SQL that was used to commit your change.  Add South Africa with a country code of ZA and we can go back to the main page.

The one last thing that we should do before switching over to Dreamweaver is to create a user to access this database.  We don't have to - we have our root username that has access to all the databases, but it's neater and more secure if we create a username for each of our databases that has rights to only that database.  From the drop down box on the left select (databases)& and you will be taken back to the main phpMyAdmin page.  Click again on Users and we can create our new user.  Our user will only connect from localhost, will be called dmxzone with a password of dreamweaver.  Leave all the other check boxes unchecked and click on Go.

The reason for leaving the Privileges empty is that if we set them on this page, our user would have those privileges for every database on our server.  Once we have created the user we will assign the user privileges to just the dmxzone database.  Once the user is created you will first have to Reload MySQL so that the server knows about the new user you just created.  Then look for the user dmxzone in the list of users and click on the Grant link next to the dmxzone user.>

Change the selection from Any database to Database and select the dmxzone database from the list.  Then uncheck all of the privileges except Select, Update, Insert and Delete.  This will allow the user to perform normal database operations, without having access to any administrative functions like reloading the server, creating tables or granting other users privileges.  Click on Go to add the privileges, then Back to get to the main page and reload the server.  You're now ready to create the connection in Dreamweaver.