Chapter 14:
Working With Databases

Creating a Database

The menu on the left is a list of all the users that have been created. The menu on the right is a list of the databases that have been created. Select a database from one menu and a user from the other. Check the box just to the right of the word "Privileges:". It should say ALL just to the right of the checkbox.

Now click the button marked "Add user to Db". That's it! You're almost done with this screen. Remember, if you already had a database and a user created - and had given the user access to the database, you would have been able to skip to this point right here: Scroll all the way down until you see a text link marked "phpMyAdmin". Note: This link is often not blue and not underlined until you mouse over it! Click the link.

There is one odd thing about cPanel and phpMyAdmin you should know: The database name you choose will always be prefixed with your system username and an underscore. So if you chose "burger" as the name of your database and "bubba" was the username you use to access cPanel, your database will actually be named "bubba_burger". This is important so I'll say it again: The name of the would be bubba_burger - not burger. The same holds true for the username.

OK. Now look on the left-hand side of the page that just came up. Each database will be listed there. There will be a number in parentheses that shows how many tables the database has. Initially, there will be none.

Just under the database name and slightly indented, are the names of the tables inside the database. There will be little icon to the left of each table name. If this is a new database, it won't have any tables yet. As soon as you create tables, they will appear in the list.

The rest of what's on this page won't make any sense to you unless you've already used phpMyAdmin a lot. For the sake of fairness, I'll assume that you haven't. Personally, I find it to be a truly useful tool - I "don't leave home without it."

Click on the name of your database in the list on the left. Now we're getting somewhere! If there were tables in this database, you'd be seeing a summary of them now. This is probably new to you. So I'll just skip over that for the moment.

Across the top of the page are a series of tabs with links: Structure, SQL, Export, Search and Query. The one that concerns us now is SQL. So go ahead and click that one.

The page changes again and now you should see a large text box with the words "Run SQL query/queries on database bubba_burger [Documentation] :" above it. (I was kidding about bubba_burger, you'll see the actual name of your database.) Whenever a script installation (from a kind and thoughtful programmer) includes a CREATE_TABLE command, this is where you paste it in. To run this CREATE_TABLE command, click the Go button.

Later on, you may want to run SQL queries on a specific table. You can do that from phpMyAdmin by clicking on a table (left side of the screen) instead of a database. Then you click the SQL tab/link to get the Run box to come up. After I teach you how to write SQL queries, you'll find this very helpful. You'll be able to look at your data privately and extract information that you don't want other people to have!

The second method involves sending SQL commands directly. This method is mostly for people with dedicated servers hosting their web site. IF you can run a PHP script that will make a connection to the MySQL database engine itself, it would work like this:

Step one is to make a connection to an account which is allowed to create databases. The function used for step one looks like this:

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

Please understand that the line above is an example only! You would need to specify an actual hostname or "localhost" where it says "host", your real username on the system where it says "user" and your system password where it says "password." The user will most likely need "root" or "super-user" access to the server in order to run the command.

After you login to the system (either locally at a terminal or remotely using telnet or SSH) you need to start the mysql server (if it's not already running) by typing:

safe_mysqld & and pressing the "Enter" key

Now you can use a utility program called mysqladmin.

Now start the "mysql monitor" by typing mysql and pressing Enter.

You should now see the mysql> prompt.

Type use mysql at the prompt and press Enter.

To create a user, type this SQL command at the prompt:

insert into user(host, user, password) values ('localhost', 'username', password('password'); (using the real username and password you want this user to have!) and press Enter.

To make sure that this user is "activated" immediately, type this command:

mysqladmin flush-privileges and press Enter.

At this point you should have both a database and a user with full access to that database. You can log off the terminal or end the remote telnet or SSH session.

From this point on, you can do whatever you like with your database. All the power of PHP scripting and MySQL access is available to this user - and you alone should know the username and password. Protect that access information!

Let me give you a little background on what's happening here. MySQL uses a special database all its own that keeps track of the users, their privileges, etc. When you make a connection to this database, you are logged in as a user with admin privileges. Unless you're running a dedicated server, it's not likely that you'll have full access to an account like this.

Here's another thing: If you are renting a dedicated server, you or one of your employees may have to do all the "system administration" work needed to keep the server up and running. Be sure that you or that employee know exactly how to do all that stuff - it's beyond the scope of this book.

But not to worry! Most hosts that let you create MySQL databases provide a script for this purpose. phpMyAdmin is a very common interface script. It gives you all the control you need over your databases, tables and data in a nice graphical format with menus, buttons and text boxes.

A lot of PHP scripts that you buy (and even some of the free ones) have an installation script or procedure that creates all the database stuff for you. Now if you're going to create such a script or set of scripts, you may have to write the code that creates the database and its tables. That's why I wanted you to know how it works.

Copyright © 2004 Steve Humphrey