How To Create A MySQL Database With Privileges

A MySQL Database, Together With PHP Scripts, Can Enhance Your Website

In this section I will show you how to create an empty MySQL Database, for those occasions whereby a PHP Script for example needs one in order to function properly. PHP Scripts normally initialize (fill in) the MySQL Database entries for you, hence why I am only showing you how to create an empty MySQL Database.

This example is also useful if you want to set up an empty MySQL Database for general data purposes such as for Membership details (name, email address and so on). If you want to know how to create fields (data/title entries) for an empty mysql database you should read the next section called Create MySQL Tables.

A MySQL Database is just like a database you might create in Microsoft Office Access or Excel. It is written in a scripting language called SQL (Structured Query Language), which means you can create a mysql database by typing its mysql instructions into a text editor only. You can also use the MySQL applications inside your CPanel control panel.

A database is made up of a table of cells (squares/boxes like in MS Excel or other spreadsheet applications) which contain Title cells (i.e. Name and Email Address) and Data cells (i.e. Mr John Smith and johnsmith@whatever.com). In each set of data cells you insert a new customer's details for example. And it is the same with a mysql database. A Blog mysql database might be filled with many tables (table of cells). One for blog users, one for blog comments, one for blog posts and so on. In this scenario the mysql database keeps a track of what is going on in that blog.

You could have many tables in a private mysql database (Table1: Members. Table2: Products) - The Title cells could be Product Name, Bought, Sold, Price and Discount with the first Data cells (row) containing Shoes, 10, 5, £10, None and the second Data cells (row) containing Slippers, 25, 19, £5.99, 10%.

The first thing you need to do is login to your web space (website) Control Panel and then click on the MySQL Databases link. This will open the MySQL Account Maintenance control panel (Fig 1.1) that is split into two sections - Databases and Users.

Fig 1.0  Click on the MySQL Databases link to open the MySQL Account Maintenance control panel




Fig 1.1  Enter a name for the new MySQL Database and then click on the CREATE DATABASE button

When the MySQL Account Maintenance control panel (window) appears it will display any previously created Accounts (MySQL Databases, User Names and Passwords). For example. It is normal for a web host to have set up a service, on your behalf w/out your permission, that required a MySQL Account. It is also normal for a website designer to have set up a MySQL Database for you, for whatever reason(s). Regardless of this though, in this example I am showing you from scratch. Therefore, there are no MySQL Accounts/Databases set up yet.

A MySQL Account is created as follows. First you need to create an empty MySQL Database, to store the actual data in, by filling out the NEW DATABASE edit box with the name of your MySQL Database. In this example (Fig 1.1 above) I am creating an empty MySQL Database for a blog, so I will name it blog. When you have typed in your desired database name click on the CREATE DATABASE button to continue (Fig 1.1 above). Doing so will bring up the following acknowledgement.

Fig 1.2  The empty MySQL Database called ???_blog has been created - Click on GO BACK to continue

After clicking on the CREATE DATABASE button (Fig 1.1 above) your are shown a confirmation window to confirm that the empty MySQL Database has been created (Fig 1.2 above). Simply click on its GO BACK button to go back to the MySQL Account Maintenance control panel.

The next part to creating a MySQL Account is to create a User Name & Password. The user name and password, once created, can be assigned to any one of your MySQL Databases. For example. If you have four MySQL Databases (i.e. blog, forum, customers and products) you can create a separate user name & password for each MySQL Database or you can have just one user name & password for all four MySQL Databases. So you could assign user name john to all four MySQL Databases or just assign john to the blog and forum MySQL Databases.....and then assign two other user names to customers and products.

In this example I am creating the User Name john with the Password myblog2010 because it will be assigned to the MySQL Database called blog. Creating the user name and password uses the same procedure as used for creating the MySQL Database. Simply type a user name into the USERNAME edit box and a password into the PASSWORD edit box before clicking on the CREATE USER button.

Fig 1.3  Click on the CREATE USER button to create the User Name & Password




Fig 1.4  The User (User Name) john and Password myblog2009 have been created - Click on GO BACK to continue

In Fig 1.3 above you may of noticed the MySQL Database called blog is now called ?_blog. This is because each MySQL Database, once created, is normally prefixed with your Web Hosting Account user name (in this example I have hidden my details) followed by an underscore (thus: ?_). The same applies to the MySQL Database User - It will be given the (?_) prefix as well (Fig 1.5 below).

So if my Web Hosting Account user name was clothes the MySQL Database would be called clothes_blog and the MySQL Database user would be called clothes_john.

At this point an empty MySQL Database and User (User Name & Password) have been created, but they are not connected/assigned to each other yet. To do this, and to complete the MySQL Account procedure, you first have to select a User from the USER drop-down (Fig 1.5 below), if you need to select another user and if there is more than one user of course. In this example ?_john is selected by default (normal behaviour) because it was the last user to be created and also because it is the only user.

Once the User has been selected you then have to select a MySQL Database from the DATABASE drop-down menu, if you need to select another MySQL Database and if there is more than one MySQL Database of course. In this example ?_blog is selected by default (normal behaviour) because it was the last MySQL Database to be created and also because it is the only MySQL Database. When you have chosen a user and database click on the ADD button to continue.

Fig 1.5  Click on the ADD (ADD USER TO DATABASE) button to assign the User to the MySQL Database

After assigning a user to a database the next window to appear asks you to set the Privileges for that user. Privileges such as: Can the user DELETE a record (data) in the MySQL Database? Can the user INSERT a record (data) into the MySQL Database? And so on. Put a tick next to each privilege you want to grant the user. In this example, and always, I leave the default (normal) setting of ALL ticked to grant all privileges to the user. You should do the same in normal scenarios.

When you are happy with the privileges you have selected (ticked) click on the MAKE CHANGES button in order to set/assign those privileges for the user, therefore creating the MySQL Account. In this example, User ?_john (with Password myblog2010) will be assigned to the ?_blog MySQL Database with ALL privileges enabled (granted).

Fig 1.6  Click on the MAKE CHANGES button to complete the set up of the MySQL Account




Fig 1.7  The MySQL Account (Database Name, User Name, Password and Privileges) has been created

Now that a MySQL Account has been created, with one MySQL Database and one User Name & Password, its details can be used by a PHP Script for example. PHP Scripts normally ask you to edit a config.php file that must contain the name of a MySQL Database and a User Name & Password. If a PHP Script needs to know what your Access Host is it is listed in the phpMyAdmin control panel and is normally localhost. Access Host is how the PHP Script connects (has access) to your website.

If you need to delete a MySQL Database and/or a User simply click on the DELETE button next to each one. Deleting a MySQL Database on its own will disconnect/unassign its user(s) whereas deleting a User on its own means the MySQL Database will be minus one user - If all users are deleted the MySQL Database will be without a user of course.

After creating the MySQL Account and clicking on the GO BACK or HOME button (depending on your cpanel version) you can then use the phpMyAdmin control panel to edit your database - create/delete Tables (Table of cells), fill in relevant Data entries (records) and so on.

Fig 1.8  Click on the phpMyAdmin link to manage (edit) your MySQL Database(s)

Fig 1.9  Inside the ?_blog database viewing its Wordpress Blog Data (wp_posts table entries)

The above is my ?_blog database with a set of wordpress blog Tables inside it, denoted by the wp_ prefixes. Clicking on one of those tables (table links) will allows me to see the structure (data entries/records) for that table.

If you only have one MySQL Database with your web hosting package you can install a blog and forum for example using the same database. You do not need two databases because each installation (blog installation and forum installation) normally uses it own prefix (i.e. wp_, yabb_, phpbb_ etc). Ideally though; You want to give each installation its own database, especially in cases whereby you want to delete a database but cannot because it is storing another installation's tables for example.

Remember. MySQL can be complex, even to someone with programming experience, so be sure you know what you are doing before clicking on the phpMyAdin link. Otherwise you may trash an important MySQL Database or have a blog without a database for example that then may leave a control panel tool useless, broken and/or redundant.