Create a MySQL database and user account

MySQL is not a single database, but a database management system. You create individual databases within MySQL. The superuser, root, has complete control over all databases, so it's necessary to set up user accounts with restricted privileges to prevent unauthorized users gaining access to other people's data.

In this section, you'll use the phpMyAdmin front end to MySQL to create a database, import sample data, and create a MySQL user account. The instructions assume that you have installed XAMPP on Windows, MAMP on a Mac, or that you have created your own PHP testing environment with a web server, PHP, MySQL, and phpMyAdmin.

This section covers the following topics:

• Create a new database and import data
• Create a MySQL user account

Create a new database and import data

To perform this task:

1. Verify that your web server and the MySQL server are running: Enter http://localhost/phpmyadmin/ into your browser address bar and load the page.

   If you installed XAMPP in Windows, enter root as the username, and then your MySQL root password when prompted.

   You should see a phpMyAdmin welcome screen similar to the one shown in Figure 10.

   

   Figure 10. The phpMyAdmin main screen

   Note: The default list of databases shown in the menu on the left of the screen is slightly different in XAMPP and MAMP, but this is not important.

   Note also that at the time of writing this article, both XAMPP and MAMP were still using version 2 of phpMyAdmin, even though version 3 had been released several months earlier. Version 3 is required only for MySQL 5.1. Otherwise, the functionality is identical, although there are some differences in the user interface.

2. Enter adc_demo in the text box labeled "Create new database" and click the Create button.

   Note: When choosing names for databases and database columns (fields), do not use spaces or hyphens; use only alphanumeric and underscore characters (for example, feedback_demo). Although phpMyAdmin accepts such characters, they require special handling and are likely to cause problems. It's also a good idea to use lowercase throughout, because the Windows version of MySQL automatically converts names to lowercase and treats them as case-insensitive. Most PHP sites are hosted on Linux servers, which are case-sensitive. If your names include uppercase characters, everything will break when you upload your web application from Windows to a Linux server.

   Note: You can ignore the Collation pop-up menu, unless you are using a language other than English. Collation determines the sort order of records. The default setting is fine for English.

3. When phpMyAdmin reports that the adc_demo database has been created, click the Import tab at the top of the page.
4. In the File to import section, click the Browse button alongside the Location Of The Text File text box and select adc_demo.sql from the ZIP file that you downloaded on page 1 of this tutorial.
5. Click the Go button at the bottom of the page. This imports dummy data into the database.
6. Leave phpMyAdmin open, as you will continue using it in the next section.

Note: The dummy data consists of just two tables: customers and products, which should now be listed in the phpMyAdmin menu on the left of the screen. You can inspect the data by clicking the table name, and then selecting the Browse tab at the top of the page. Each table contains only two records. The purpose is simply to demonstrate how to import data with phpMyAdmin.

Create a MySQL user account

The root superuser should be used only for administrative tasks inside phpMyAdmin, because it has the power to wipe out all of your data. After data has been deleted, you cannot restore it! There is no equivalent of the Windows Recycle Bin or Mac Trash in MySQL. So, the next task is to create a MySQL user account with restricted privileges. The following instructions show you how to create an account that has access only to the adc_demo database.

1. Click the icon that looks like a little house at the top left of the phpMyAdmin screen to return to the original welcome screen (shown above in Figure 10).
2. Click the Privileges link on the welcome page.
3. Click Add a new user (roughly halfway down the page).
4. In the User name text box, enter adctest.
5. From the Host drop-down menu, select Local. This enters localhost in the text box alongside, restricting the user to your local test environment.
6. Type a password in the Password field, and retype it in the field immediately below.
7. Leave the rest of the page unchanged, and click the Go button at the bottom of the page.
8. The next screen confirms that the new user has been added. The Global privileges section gives the user account the same privileges on all databases. For the purposes of this tutorial, you're going to limit the user to the feedback database, so scroll down to the Database-specific privileges section.

9. Select the database name from the pop-up menu, as shown in Figure 11.

   

   Figure 11. Limiting a MySQL user account to a specific database

   Note: The underscore in the database name is preceded by a backslash. In certain circumstances, the underscore can be used as a wildcard character in MySQL, and the backslash is needed here to ensure that the underscore is treated as a literal character. However, this is an exceptional case. Normally, you should not precede the underscore in database, table, or column names with a backslash.

10. This opens a page where you can set the user's privileges for the selected database. For a web application, only four privileges are normally required: SELECT, INSERT, UPDATE, and DELETE. Select them and click the Go button (see Figure 12).

    

    Figure 12. Setting user privileges for a web application

You now have a MySQL user account ready to access the feedback database. In the next section, you'll learn how to create a MySQL connection in Dreamweaver.

Page 6 of 7

Previous Page

Next Page