|
Welcome to Host Your Site’s DBManager tutorial. This tutorial assumes that you already have
MySQL and the DBManager installed onto your Virtual Server. In this tutorial we will create a very simple
and small database using the GUI interface the DBManager provides. 1.1 Accessing DBManager First, access your DBManager in the following manner: Of course, you would replace the domain.com with your virtual
server domain name, and your username and password would be the same
as your control panel. Once you
are inside of your DBManager, you should see a screen such as this one:
1.2 Basic Features In the left frame you will see your domain name and the port number that MySQL is using to listen for connections. If you had a database or more than one database created, a small ‘plus’ symbol would appear next to the domain name, and if you clicked on it, the listing would expand and the databases currently running would be shown. In the right hand frame, the following information will be displayed:
If the status is stopped, you can start the MySQL daemon by
simply clicking on the icon next to the server’s name in the left frame
of your browser. When running,
the status should be listed as “OK”.
In this tutorial, we will create a small database that will
contain people’s names, email addresses, phone numbers, and an ID number
that will be assigned to people in the database.
This database can have quite a few practical uses to someone
running a commercial website, and this is a very simple example of the
kind of database that can be put into use.
A database is simply a collection of organized data. A relational database organizes data into tables,
which makes the collection of this data much easier to categorize. The tables will consist of fields which will
have different records inserted into them.
First let us create a database using the DBManager’s GUI interface.
After you login, click on the server name.
In the right frame you will see the area where the database creation/administration
is done. The right frame will be divided into four main
sections under the MySQL server name:
In the section that holds the database listing (above), click
on the hyperlink labeled “create database” which will bring you to the
following screen:
You simply need to type the name of the database you want to
create in the field labeled “name” and click the gray button below it
labeled “save”. In this example,
we will create a database called “test”.
This will bring you back to the screen with the database listings,
and you should see your database listed.
In the screenshot below you should see the database called “test”.
Click on the hyperlink of your database, which in this case
is “test” and this will bring you to the following screen:
There will be a listing for Tables that are currently set up
in your database. Click on the
hyperlink on the right of the table listing which reads “New Table”
and a new screen will be presented.
You will be required to enter not only the name of the table,
but you will also need to enter in one of the fields that you will be
using in your table. In this example, we will create the table “Customers”
and the initial field being created will be “name”. Take a look at the screen shot:
Under the “New Table” heading you will see the table name field.
The table we are creating is going to be called “Customers” so
that is what you will enter inside the field.
The field called “type” is the type of table your data will be
stored in. ISAM used to be the
old default, but MyISAM tables are the new default for MySQL and hold
a number of advantages over ISAM table, so select MyISAM as your table
type. We will leave the advanced
options blank, and skip down to the first field and create the “name”
field. The fields are what will
hold the data such as names, email addresses, etc.
Select “name” as your initial field and then go down to the field
labeled “type”. For the “Data
Type” we will select “VarChar” which will enable us to use 1-225 characters
and will store the data as a variable length string instead of a fixed
length. For the Maximum length, we will use “25” characters
although you could have set it to 255 characters. Lastly, we will check the box labeled “Allow
Nulls” which will accept a null value into the name field. Click save and the table and initial field
will be created. You will be
returned to the following screen:
Now you will see the table “customers” under your table listing.
It will show the type of table it is, the time it was created,
and the last time it was updated. Clicking on the hyperlink “Customers” will
generate a page that shows the status of the table, and what fields
currently exist in that table.
Notice that next to the server name, you will have a list of
options allowing you to create a new database or user. Across from the database name “test” you will
have a list of options that are used to manipulate your database including
creating new tables. Beside
your table called “Customers” you will have the option to manipulate
the table. Now, we’ll finish entering our fields that
will be used with the table “Customers”.
Under the Fields listing, click on the hyperlink labeled “New
Field” and you will come to the following screen:
A section labeled “New Field” will appear and it is the same
as the previous section we used to enter in our field labeled “name”. In the field labeled “Field” type in “email”.
This field we are creating is going to store customers’ email
addresses. Email addresses can
vary in size depending on the customer’s username and domain name.
For this reason, we want to select “VARCHAR” as the Data Type.
For the maximum length we will choose “25” although you could
make this much larger to accommodate larger email addresses. Click the gray button labeled “Save” and the field will be created.
We will repeat the process to create the
field for phone numbers. The
field name will need to be “phone_numbers” since the field names do
not allow for spaces. As for the Data Type you will select “VARCHAR”
so that you can use numbers and dashes. Set the field for “Maximum Length” to 12 so that this will give
enough room for the area code and phone number. Last, we are going to have a field for “ID” created so that each
customer in the database will have an identification number assigned
to them. We will also create
the field so that as new records are added, this particular field will
automatically assign a new number to the customer.
To do this click on the “new field” link as you have done before,
and use the name “ID” as the field name.
For the Data Type, select “INT” since numbers will be used and
leave the maximum length blank to use the default.
Check the box labeled “primary key” and this option will make
sure that no two ID’s for any of the records will ever be the same.
Also, check the box labeled “Auto Increment” which will ensure
that the field is automatically updated as new entries are made. Now all the fields will be created and your
screen should represent the following:
Now with all the fields created, we will add records to the
database. In the above screen,
across from the table “Customers” you will see some hyperlinked options. We will select “New Records”. After selecting that option, the following
screen will appear:
For our example we will enter our first record using the following
customer data: Name: John Doe Email: john@somewhere.com Phone Number: 555-555-5555 In order to properly edit the fields, you will need to place
a check mark next to the field name that you
are updating or adding. Once
the checkmarks have been placed next to the appropriate fields, the
information should be entered as shown in the screen shot below:
Please note that the ID field is left blank, this is because
it was already set to increment automatically. Once the information has been entered, click on the gray button
labeled “save” and the records will be entered into the appropriate
fields. The screen will then
show the record as it was entered into the database:
You now have the option of entering other records into the
table by clicking on the link labeled “New Record” and repeating the
whole process again. It is very
easy to create a database using the MySQL DBManager and this tutorial
does not even scratch the surface of what can be done using the DBManager
interface. 2.7 Selecting data
from your database To select data from your database, click on the database and then the table you want to select from. From there you will need to click on ‘Execute SQL Statement’. The SQL statement to select data is as follows: Select [record] from [table] where [field]=’[item]’ The
SELECT statement retrieves data from the database. To retrieve the data,
you specify a field list, a table list, a list of fields to sort by,
and the sort order. The parts of an SQL statement are called clauses.
A basic SELECT statement has up to four clauses. For example: The
options used with the WHERE clause are ‘=’ or ‘LIKE’. You would use
‘%’ as a wild card when using LIKE in the WHERE clause. The following
SELECT statements include = and LIKE: Perhaps
you want to find all Student ID’s for lastname of Thompson and Thomas. Try this: Or
you want to find all Student ID’s with the first name of Thomas. The
ORDER BY clause of the SELECT statement controls the order of the records
returned by the query. 2.9 Editing data
SQL INSERT
statements add one or more new rows to a table. The INSERT statement
has two variations. The first
variation adds one row by assigning values to a specified list of columns
in a specified table. The values you want to insert follow a VALUES
statement. You put parentheses around both the field list and the values
list. UPDATE statements
change data in one or more columns and in one or more rows. The UPDATE
statement is dangerous, however because if you forget to specify conditions,
your database will happily update all the rows in the table. You should
always specify a WHERE condition when updating data. The UPDATE statement
has the following syntax: The DELETE statement
is the simplest of all, but quite powerful. You can use the DELETE statement
to delete one or more rows in one or more tables. The III. Common Errors
If you see this in the dbmanager status (on the website):
Can't connect to MySQL server on 'domain.com' (111)
the domain may have a resolution issue or is not pointed to us...try this:
open /www/conf/dbmanager.conf for editing change the domain.com in
$MysqlTool::servers{1}->{'server'} = 'domain.com'; to $MysqlTool::servers{1}->{'server'}
= 'localhost';
If you see this in dbmanager status (on the website):
Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)
try this:
start the mysql daemon :) If the daemon is currently running, then stop the daemon and
start it again. This error is sometimes caused by trying to start the mysqld
when it’s already started and /tmp/mysql.sock is removed. By stopping
and then starting it again, you are recreating the sock file. If you see something like the following in the apache error_log:
at
/usr/lib/perl5/site_perl/5.005/MysqlTool.pm line 116
Or this:
Can't
locate strict.pm in @INC (@INC contains: /usr/lib/perl5/i686-linux/5.00404
/usr/lib/perl5 /usr/lib/perl5/site_perl/i686-linux /usr/lib/perl5/site_perl
.) at - line 3.
Or this:
usr/lib/perl5/site_perl/5.005/i686-linux/auto/DBI/DBI.so:
undefined symbol: __b |
