Creating a database

See more about:

Where are we?

You’ve seen how PHP programs can send SQL statements to MySQL. You’ve seen two sample applications. Time to get your hands dirty.

This lesson’s goals

By the end of this lesson, you should:

  • Be able to create a MySQL database on your local machine.
  • Be able to create a MySQL user on your local machine.
  • Be able to create a MySQL database on your hosting account.
  • Be able to create a MySQL user on your hosting account.
  • Be able to write PHP test programs to connect to the databases.

Creating a local database with phpMyAdmin

Start phpMyAdmin on your computer (see this explanation).

You’ll see the phpMyAdmin home page. If you get lost, click the home button to get back to it:

Go to the phpMyAdmin home page

Figure 1. Go to the phpMyAdmin home page

Type in the name of the new database on the phpMyAdmin home page, and click the Create button:

Creating a database

Figure 2. Creating a database

phpMyAdmin will make the SQL statement that creates a database, and send it to MySQL. You’ll see something like this:

Feedback from creating a database

Figure 3. Feedback from creating a database

That’s it! You’ve created a database.

There are lots of options you can choose. As usual, CoreDogs only talks about the really important stuff.

Exercise: Creating databases

Create two databases on your local machine:

  • dogtoys
  • dogrock

You’ll use them to install your own copies of the DogToys and DogRock applications.

(Log in to enter your solution to this exercise.)

Creating a local MySQL user

MySQL uses its own security system, with user names and passwords. When you create a database, the next thing is:

  • Create a new MySQL user.
  • Give the new user access to the database.

You don’t have to create a new user for each database, but it’s common practice.

Remember this:

MySQL user accounts have nothing to do with other user accounts!

MySQL accounts are used for MySQL access, and nothing else. Not FTP, or email, or Web access. Just MySQL.

Creating a local MySQL user

How you do it is a little different on different versions of phpMyAdmin. But the concepts are the same.

Go to the phpMyAdmin home page:

Go to the phpMyAdmin home page

Figure 1 (again). Go to the phpMyAdmin home page

Depending on your version of phpMyAdmin, either click the Privileges link, or the Privileges tab:

Privileges link

Or

Privileges tab

Figure 4. Privileges link or tab

Click the add user link:

Add user link

Figure 5. Add user link

Fill in the user’s name, host, and password and click the Go button:

Add user

Add user

Figure 6. Add user

Don’t set any global privileges for the user.

I often make the user name the same as the database name. You can type in your own password, or have MySQL generate one for you. If you do that, make sure you copy and paste the password to a file on your computer. You don’t want to lose it!

What’s that “host” thing? This is which computer(s) the user is allowed to connect from. The right value is almost always localhost, because your Web server and your DB server are running on the same machine. Type in localhost unless your hosting company tells you otherwise.

I’ve only ever run into one hosting company that didn’t use localhost. That was Dreamhost.

Giving a user access to a database

Once you have created the user, you can give the user access to a database. Scroll down to the “Database-specific privileges” section, and fill it in:

Add database privileges

Figure 7. Add database privileges

Enter the database you want to give the user access to.

<Begin alternate route>

You can also get to Figure 7 from the home page. Go to the home page:

Go to the phpMyAdmin home page

Figure 1 (again). Go to the phpMyAdmin home page

Then go to the privileges screen:

Privileges link

Or

Privileges tab

Figure 4 (again). Privileges link or tab

Click the edit user icon:

Edit user privileges

Figure 8. Edit user privileges

</End alternate route>

Now that you’ve give the user access to the database, you tell MySQL what that user is allowed to do:

Select privileges

Go button

Figure 9. Select privileges

Check the privileges in the figure.

You’re done.

Exercise: Creating database users

Create two database users on your local machine, one for each of the databases you created earlier. You can name them anything you like. I usually give them the same names as the databases:

  • dogtoys
  • dogrock

You’ll use them for your own copies of the DogToys and DogRock applications.

Choose your own passwords, and write them down!

Give the users access to the server at localhost.

Give the users access to their respective databases. Give them the following privileges:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • CREATE
  • ALTER
  • INDEX
  • DROP

(Log in to enter your solution to this exercise.)

Local testing

But did it work? When you create a new database and user, you should run some PHP to make sure it worked.

Here’s a test page.

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Strict//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title></title>
  </head>
  <body>
    <h1>PHP-MySQL connection test</h1>
    <?php
    //DB connection data.
    //Probably leave $host alone.
    $host = 'localhost';
    //Set $db to the name of your database.
    $db = 'dogrock';
    //Set user_name to the name of the MySQL user you made.
    $user_name = 'dogrock';
    //Set $password to the user's password.
    $password = '[Secret password here]';
    ?>
    <p>Trying to connect to MySQL.</p>
    <ul>
      <li>Host name: <?php print $host; ?></li>
      <li>Database name: <?php print $db; ?></li>
      <li>User name: <?php print $user_name; ?></li>
      <li>User password: (Secret)</li>
   </ul>
    <?php
    //Connect to the MySQL server.
    $db = new mysqli($host, $user_name, $password, $db);
    //Did it work?
    if ( mysqli_connect_error() ) {
      print '<p>Error! Could not connect to the database. ';
      print 'Error message: '.mysqli_connect_error().'</p>';
    }
    else {
      print '<p>W00f! It worked!</p>';
    }
    ?>
  </body>
</html>

Figure 10. Test a connection

Line 29 makes the connection. It needs four pieces of data:

  • Host name, e.g., localhost
  • User name, e.g., dogrock
  • User password
  • Database name, e.g., dogrock

You type in those values in lines 11 to 18.

Line 31 tests whether the connection worked or not. If not, it shows an error message. If there is no error, line 36 will tell you about it.

Grab this code, and put it somewhere on your local computer.

Here’s what happens when everything works:

Connection OK

Figure 11. Connection OK

Here’s what happens if MySQL is not running:

MySQL not running

Figure 12. MySQL not running

The error message is “Can’t connect to MySQL server.”

Here’s what happens if the password is wrong:

Wrong password

Figure 13. Wrong password

If the database name is wrong? Here it is:

Wrong database name

Figure 14. Wrong database name

Exercise: Testing your local database connections

Write PHP programs to test your two local databases:

  • dogtoys
  • dogrock

Copy the test code, and adjust the connection parameters.

If there’s a problem:

  • Use phpMyAdmin to confirm that the databases exist.
  • Use phpMyAdmin to make sure that the users exist, and they have privileges to access localhost and the databases.
  • Check the connection parameters in your PHP code.

(Log in to enter your solution to this exercise.)

Now you know how to:

  • Create a database on your computer.
  • Create a user with access to the database.
  • Test the connection.

What about your hosting account?

Creating a remote database with phpMyAdmin

When you create a database on your hosting account, you do things a little differently. The exact details depend on your hosting company. I’ll run through the procedures for Hostgator.

Log in to your control panel. Find the database icons:

Control panel database icons

Figure 14. Control panel database icons

Click the “MySQL databases” icon:

Control panel database icons

Figure 15. Control panel database icons

Type in the name of the new database and click the button:

Create database

Figure 16. Create database

You’ll get some feedback, like this:

Create database feedback

Figure 17. Create database feedback

The next thing is important!

The real name of the database might not be the name you typed!

Hostgator prepends (adds in front) your user name to the database name, and chops off extra characters, if there are any. So it your Hostgator user name is woof121 and you type dogrock for your database name, the real name of your database might be woof121_dogrock.

You would use woof121_dogrock as the database name in your PHP programs.

Renata
Renata

Why does Hostgator do this?

Kieran
Kieran

Remember that many sites run on the same server. That’s what “shared hosting” means. There’s one copy of MySQL that everyone shares.

Several people might create a database named books. How is the server to tell them apart?

Renata
Renata

Oh, I see. Hostgator changes the names, so they are all different.

Kieran
Kieran

Right! So if the user lab200 typed in books as the name of a database, the actual name of the database would be lab200_books. If the user pug77 typed in books as the name of a database, the actual name of the database would be pug77_books.

Copy and paste the real name of the database into your code.

Creating a remote MySQL user

Go back to the MySQL database page. Remember, to get there from the control panel:

Control panel database icons

Figure 15 (again). Control panel database icons

To create a user, scroll down to the Add User form, and fill it in:

Create a user

Figure 18. Create a user

You’ll get feedback like this:

Create a user - feedback

Figure 19. Create a user – feedback

But this is misleading. The control panel changes the user name the same way it changes the database name. So if lab200 creates the user dogrock, the user is really lab200_dogrock.

Hostgator’s control panel won’t change the password. If you type a password of secret_ThinG_12, then that will be the password you use in your PHP.

Now you need to give the user access to the database.

On the MySQL Databases page, you’ll see something like this:

Giving a user access to a database

Figure 20. Giving a user access to a database

Select the user and the database, and click the Add button.

Then you select which privileges the user has:

Setting privileges

Figure 21. Setting privileges

Use the ones shown in the figure.

You’ll get feedback like this:

Setting privileges - feedback

Figure 22. Setting privileges – feedback

Hooray! You’ve:

  • Created a database on your hosting account.
  • Created a MySQL user on your hosting account.
  • Given the user access to the database.

Remember: use the real names of the database and the user. You can see them on the control panel’s MySQL Databases page.

Remote testing

How to test that everything worked? Same as before. Put a file with this code on your hosting account:

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Strict//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title></title>
  </head>
  <body>
    <h1>PHP-MySQL connection test</h1>
    <?php
    //DB connection data.
    //Probably leave $host alone.
    $host = 'localhost';
    //Set $db to the name of your database.
    $db = 'dogrock';
    //Set user_name to the name of the MySQL user you made.
    $user_name = 'dogrock';
    //Set $password to the user's password.
    $password = '[Secret password here]';
    ?>
    <p>Trying to connect to MySQL.</p>
    <ul>
      <li>Host name: <?php print $host; ?></li>
      <li>Database name: <?php print $db; ?></li>
      <li>User name: <?php print $user_name; ?></li>
      <li>User password: (Secret)</li>
   </ul>
    <?php
    //Connect to the MySQL server.
    $db = new mysqli($host, $user_name, $password, $db);
    //Did it work?
    if ( mysqli_connect_error() ) {
      print '<p>Error! Could not connect to the database. ';
      print 'Error message: '.mysqli_connect_error().'</p>';
    }
    else {
      print '<p>W00f! It worked!</p>';
    }
    ?>
  </body>
</html>

Figure 10 (again). Test a connection

Replace the database name, user name, and password. Use the real names, like lab200_dogrock. Load the page in your browser, and see what you get.

Exercise: Create and test remote databases

Create two databases on your hosting account:

  • dogtoys
  • dogrock

Your hosting account’s control panel might adjust the names of the databases. Make sure you know the real names.

Create users for each database. Give them access to localhost. Write down the users’ passwords!

A few hosting companies don’t use localhost.

Give the users the following privileges on their respective databases:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • INDEX
  • CREATE
  • ALTER
  • DROP

Copy the test code, and adjust the connection parameters. Upload to your server, and run your test programs.

Put the URLs of your test pages below.

If there’s a problem:

  • Confirm that the databases exist. Make sure you know what their real names are.
  • Confirm that the database users exist. Make sure you know what their real names are.
  • Confirm that the users have access to locahost.
  • Check that the users can access their respective databases. Check their privileges.
  • Check the connection parameters in your PHP code.

(Log in to enter your solution to this exercise.)

Summary

In this lesson, you learned how to:

  • Create MySQL databases and users on your local machine.
  • Create MySQL databases and users on your hosting account.
  • Write PHP test programs to connect to the databases.

What now?

Now you have some databases. Time to add a table to each one.


How to...

Lessons

User login

Log in problems? Try here


Dogs