Creating a database
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:
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:
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:
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:
Figure 1 (again). Go to the phpMyAdmin home page
Depending on your version of phpMyAdmin, either click the Privileges link, or the Privileges tab:
Or
Figure 4. Privileges link or tab
Click the add user link:
Figure 5. Add user link
Fill in the user’s name, host, and password and click the Go button:
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:
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:
Figure 1 (again). Go to the phpMyAdmin home page
Then go to the privileges screen:
Or
Figure 4 (again). Privileges link or tab
Click the edit user icon:
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:
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:
Figure 11. Connection OK
Here’s what happens if MySQL is 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:
Figure 13. Wrong password
If the database name is wrong? Here it is:
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
localhostand 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:
Figure 14. Control panel database icons
Click the “MySQL databases” icon:
Figure 15. Control panel database icons
Type in the name of the new database and click the button:
Figure 16. Create database
You’ll get some feedback, like this:
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.
Why does Hostgator do this?
Remember that many sites run on the same server. That what’s “shared hosting” means. There’s one copy of MySQL that everyone shares.
Several people might create a database names books. How is the server to tell them apart?
Oh, I see. Hostgator changes the names, so they are all different.
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:
Figure 15 (again). Control panel database icons
To create a user, scroll down to the Add User form, and fill it in:
Figure 18. Create a user
You’ll get feedback like this:
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:
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:
Figure 21. Setting privileges
Use the ones shown in the figure.
You’ll get feedback like this:
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.
