Account features: MySql

Introduction

MySql is a relational database system running on Positive Internet
Company servers. Each account has the capability to have a MySQL
database. If you requested a database when you signed up for your
account it will be ready to use. If you would like to request for the
database to be set up after signing up please do so by e-mailing
support@positive-internet.com. If you don't yet need the facilities of
a database that integrates with your website, or have a simpler
solution, then you can safely ignore the feature until such time as you
do.

Uses

Using a MySQL as the "back-end" of your website is appropriate, for
example, if you wish to set up a site which includes product catalogues, records
visitor information or manipulates, filters and presents any tables of dynamic
data. Of course, a relational database is not the only way to do these things
- you could manually edit HTML pages, write scripts to read from "flat
file" data sources for example - but for anything slightly more involved,
getting to grips with MySQL is well worth the effort. What's more, with an ODBC
driver (freely available - see below), you can manage your database on our server
from within Microsoft Access or Excel right from your desktop!

The manual and required knowledge

As its name suggests, MySQL uses the Structured Query Language. If you
have had experience with Microsoft SQL server, Oracle or most other modern
database systems, you will have encountered it. If not, there are several
good books on the subject. Even if you don't use MySQL, a knowledge of SQL
looks good on a CV! Assuming you have a working knowledge of general SQL,
the exhaustive MySQL
manual illustrates the specifics. It makes good reading on long train
journeys. As long as the scenery isn't too enticing.

Apart form SQL, you should also know about CGI scripts. Positive servers are
installed with the necessary DBI Perl modules to access your database and present
the information through a webpage. See below for information on their use. PHP,
a language whose forte is integrating database information into HTML documents,
is also installed and available for immediate use. Again, information is available
below.

Your database

When we create your database we set up an empty database in which
you may create your own tables. The database's name is the same as your
login name. The username to access the database is the same as your
primary account's login name. The database's password is the same as
your primary account's original password. Please Email us if you want
it changed or you can change it at the command shell. Changing your
login password will not change your database's password. Databases
created before 1st Jan 2004 will usually exist on the same server as
your web hosting account (localhost), databases set up after Jan 1st
2004 will exist on a seperate dedicated database server. (
mysql.positive-internet.com )

Entering MySQL shell

There are several ways to access your database. One of the quickest (if most
rudimentary) is through the MySQL shell. Through it, you can issue any SQL query
and see its results. To use the MySQL shell, you must use ssh to access the command shell on your primary
account. If the last statement was meaningless to you, you have other parts
of this manual to read yet!

Then, as an example, let's assume your Positive Internet login name is fishcom.
As explained above, your database is therefore also called fishcom. So at Unix
command prompt, type the following and press return:

mysql -p database

The -p option tells mysql to ask you for a password, which is necessary because
we set up all databases with passwords. Again, this password will be the same
as your original primary account's password, unless you have asked for it to
be changed.

Instead of database, you should type the name of the database you wish to access.
This will usually be the sames as your Positive login name.

You will be prompted for a password. It won't appear on screen of course. Type
it in and press return.

If you have typed in the correct password, you will see something like this:

Reading table information for completion of table and column names

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 484 to server version: 3.22.4-beta
Type 'help' for help.
mysql>

The mysql> is the shell's prompt, beckoning you to type SQL queries at your leisure.

Entering SQL queries at the MySQL shell

Of course, we're not going to go into the full semantics of SQL here -
the MySQL manual
is for that! Remember that all queries should end with a semicolon. Bearing
this in mind, you can split your query over as many lines as you wish -
it will only be acted upon when the final semicolon is entered and the return
key pressed.

If the query returns data (a select query, for instance), the information will
be presented. Unfortunately, if there are more columns than room to display
them, everything wraps rather unattractively. A solution which often works is
to copy and paste the output into Windows Notepad and make sure wrapping is
turned off. Or select fewer columns in your select statement! For complex queries
with lengthy output, there are better solutions than the MySQL shell (discussed
below).

If the query acts upon the table but doesn't return any data (for example,
a create, alter, update or delete statement), the number of affected rows and
columns is output. In this example, we create a new table called fish with two
columns:

mysql> create table fish (

-> name varchar(25) primary key,

-> cost decimal (12,2) not null,

-> description mediumtext,

-> index name(name),

-> index cost(cost)

-> );

Query OK, 0 rows affected (0.02 sec)

The bold text above is what was typed in. The rest is MySQL's response. This
whole query could have been typed on one line, but it was neater to press return
after the first parenthesis and then after each column or index definition.
While you're still in the middle of a multi-line query, the MySQL shell reminds
you by indenting the line and prompting you with ->