MySQL Tonight

I think PHP Example with GoDaddy post is pretty much done. I just added a new section into the middle of it. To find the new material, look for October 7 in brackets. We can move onto MySQL.

I had swapped the hard disk in my laptop for something bigger which is good except that all the software had to be reinstalled including Adobe Photoshop. So I start looking for its CD which is nowhere to be found. I must have lost it during the move. This is why you see me drawing diagrams by hand. I am wishing I had my Photoshop right now to make some drawings. I'm going to have to call Adobe for help if I can't locate the CD over the weekend.

So far, we have an HTML file which, with the help of CSS, forms the mail order shoe page. Part of the problem with that approach is that everything is hardcoded in the HTML file with nothing coming out of a database. This is where MySQL comes in. We can store the shoe information in a database with an item number, brand name, name of shoe, sizes available, description of shoe and its price. Then when the end user clicks on the thumbnail for strappy evening sandals, we pull all the data from the database. Of course, most of you knew that. I'm not trying to teach you about databases per se but how a database is set up with your web hosting provider. Yesterday, I had signed up with Godaddy to work on this example. So we go to their UI to create a database, as follows:

From Hosting Control Center > Databases > MySQL > Create Database, enter the following:
database/user name: womenshoes
password: xxxxx

After I hit return, a screen comes up saying that the Status is "Pending Setup" which means that we are relying on Godaddy to complete the creation of the database. There is your first significant difference between shared hosting and the kind of server that I use for my sites. If you are working with MySQL directly on your own server this operation of creating a database is instantaneous.

I don't want the wait to derail us. There are multiple ways to create a database (as well as the tables within a database). One way obviously was through Godaddy's control panel but Bob Parson's gone fishing so you and me are on our own tonite. So, I'll switch over to my regular server and create the same database over there.

In order to create a MySQL database we'll need administrative privileges, which I have with my regular server because I have the root password. But on Godaddy, only Bob knows the root password so we are at his mercy. Once Bob creates the database and gives us a MySQL user account for accessing that database then we can create our tables through PHP. How long he's going to take I have no idea because I've been hitting refresh and Godaddy's still giving me "Status Pending".

In addition to the database itself, there are the tables within the database. You can create MySQL tables directly in a number of ways:

• through the MySQL command line prompt
• through a web based tool such as PHPMyAdmin
• with the MySQL Administrator program which is an app that you can run on your PC
• through your application code such as your PHP program

What I usually do is to create the database through the PHPMyAdmin tool and then I create the tables from within the PHP program. Since Bob is twiddling his thumbs, let's go over to my server to create this database.

I got in and brought up phpMyAdmin. MySQL maintains a set of databases but it also maintains a set of users. That way you can assign a user account that's only given access to a single database so you don't have to use root password from your PHP programs. You can use a less privileged account. If someone ever gets that password they can only manipulate that database and not your entire MySQL installation.



Once logged in, you come to this screen. Choose "Privileges"



Choose "Add a new user"





I used the same name (womenshoes) that I did in Godaddy. I choose localhost because I only want this database to be accessible on the local machine and not across the internet. I check "create database with the same and grant all privileges" so that this will create the MySQL user account and database both with the name "womenshoes".

It's done. Let's go see what Bob is doing.

He is still "Pending Setup".

Everything from here on will be done from PHP although some people do use tools like PhpMyAdmin to actually create their tables. Instead, I'll directly program it from my PHP code. I'd keep writing but I'm falling asleep at the keyboard so better continue later tomorrow.