Connecting to and Creating a Database

I l @ ve RuBoard

When you worked with text files in Chapter 10, Files and Directories, you saw that you first had to create a file pointer while opening the file. This pointer then acted as a reference point to that open file. A similar process is used when working with databases. First, you have to establish a connection to the database server (in this case, MySQL). This connection will then be used as the access point for any future commands. The MySQL syntax for connecting to a database is:

 $Link = mysql_connect ("host", "user",  "password");

The link is established using three arguments: the host, which is almost always localhost; the user name; and, the password for that user name . These last two parameters will dictate what database permissions you have.

Database permissions is a bit more complicated than file permissions but you need to understand this: different types of users can be assigned different database capabilities. For example, a DBMS administrator can create new and delete old databases (within your DBMS you may have dozens of databases), but a lower-level administrator may only be able to create and modify tables within a single database. The most basic user may just be able to read from, but not modify, tables.

Your ISP will most likely give you the second type of accesscontrol over a single database but not the DBMS itselfand will establish the initial database for you. If you are working on your own server or have administrative access, you have the capability to create new databases.

The code for creating a new database is:

 mysql_create_db ("databasename", $Link);

Notice how the $Link value established when connecting to a database is used to continue working with the database, just as a file pointer is referenced to read from or write to the file.

After executing any command to the database it is considered good form to close the link, just as you would close an open file:

 mysql_close  ($Link);

For the first database example of this chapter, you will create a new database, which requires that you have administrator access. If your ISP restricts your access, they should create the initial database for you upon request, so you can skip ahead to the next section, Creating a Table.

To connect to MySQL and create a new database:

Create a new PHP document in your text editor.
Begin with the standard HTML header (Script 11.1).
```
 <HTML><HEAD><TITLE>Creating a Database</TITLE><BODY> 
```
Script 11.1. Creating a new database consists of three steps: linking to the database, using the mysql_create_db() function, and then closing the link. I make it a habit to establish all database dependencieshost, user name, password, database nameas their own variables to make them easier to change later.
Create a PHP section of the script and set the database variables.
```
 <?php $Host = "localhost"; $User = "user"; $Password = "password"; $DBName = "NewDatabase"; 
```
By assigning these values to variables, you can more easily alter the script to work with other databases at a later date. If you are working on an ISP's or Web host's server, they should provide you with the user name and password.

When creating the database name, do not use spaces, just as you would not use spaces in variable or function names .
Connect to the database.
```
 $Link = mysql_connect ($Host, $User,   $Password); 
```
This script will attempt to connect to the MySQL DBMS on the server, using the host name, user name, and password. If the user name and password you entered do not match preset database privileges (for example, if you are using the wrong user name or password), you will get an error message at this point in the execution of the script.
Attempt to create the new database and print a message indicating the result of the attempt.
```
 if (mysql_create_db ($DBName, $Link)) {    print ("The database, $DBName, was      successfully created!<BR>\n"); }else {    print ("The database, $DBName,      could not be created!<BR>\n"); } 
```
If the script was successfully able to create the new database, you will only see the successful message (Figure 11.2). If, for some reason, it was not able to create the database, you will see a number of MySQL errors as well as the "could not be created!" error generated by this conditional (Figure 11.3).

Figure 11.2. This is the HTML form I'm using to enter data into the database. The input here will be stored in the Feedback table in the NewDatabase database.

Figure 11.3. Notice how PHP automatically escaped the apostrophe in my comment so that it won't cause troubles when submitting the query to the database. Without the apostrophe escaped, the SQL would think the last column consisted only of "Now I" and an error would result.
Close the MySQL link, then close the PHP section, and the HTML.
```
 mysql_close ($Link); ?></BODY></HTML> 
```
It is not strictly necessary to close the MySQL link as the server will automatically do so once the PHP script has stopped running, but I would recommend that you include this step anyway, as a matter of form and efficiency.
Save your script as CreateDB.php, upload it to the server, and test it in your Web browser.

Tip

PHP has built-in support for most databases including: dBase, FilePro,mSQL, MySQL, Oracle, PostgreSQL, and Sybase. If you are using a type of database that does not have direct supportfor example, Access or SQL Serveryou'll need to use PHP's ODBC (Open Database Connectivity) functions along with that database's ODBC drivers to interface with the database. See the Database Resources section of Appendix C, PHP Resources, for more information.

Tip

The combination of using PHP and MySQL is so common now that there are two terms you may run across identifying servers configured with both PHP and MySQL: LAMP ( Linux operating system, Apache Web server, MySQL DBMS, PHP ) and WAMP ( Windows operating system, Apache Web server, MySQL DBMS, PHP ).

I l @ ve RuBoard

Figure 11.2. This is the HTML form I'm using to enter data into the database. The input here will be stored in the Feedback table in the NewDatabase database.

Figure 11.3. Notice how PHP automatically escaped the apostrophe in my comment so that it won't cause troubles when submitting the query to the database. Without the apostrophe escaped, the SQL would think the last column consisted only of "Now I" and an error would result.