mySQL Information

The MySQL option in your control panel automates some MySQL operations. This document explains some details of this powerful database platform.

Note: We do not provide free technical support for using MySQL, however you will find loads of useful information as well as additional resources that will assist you throughout the following links. This is a long page, as it contains a lot of info.

MySQL Overview

SQL stands for Structured Query Language. It is the most common language usedfor accessing a database. It has been in use for many years by many database vendors. Many consider it the best database language to use. It is used by the MySQL database feature inside your control panel.

Without going into the technical details, SQL is a language which consists of a set of commands that you issue to create, make changes to, and retrieve data from a database. Here are some SQL command examples.

These commands can be issued through a Graphical User Interface or by embedding them in a computer program that you write.

The MySQL Control Panel provided as part of your account is a GUI that works over the internet through your web browser. This makes it very convenient for administration of web based database applications. Setting up and managing your database will be done through the MySQL Control Panel.

To allow access to your database through your web site, you will need to create Common Gateway Interface scripts. These scripts are small computer programs which run on the web hosting server and are activated by clicking on a link ora button in a web page. This will allow users of your web site to interact with your web site in a more meaningful manner. Using CGI scripts and MySQL you can maintain account information on visitors, allow people to search and browse catalogs, and much more. See Using MySQL with CGI scripts for documentation on how to set up such scripts.

MySQL is an implementation of the SQL language developed by TcX. It is robust,quick, and very flexible. It provides all of the standard SQL datatypes and commands. MySQL is provided as part of your web site account at no additional charge. MySQL is pronounced “My Ess Que Ell.”

For step by step instruction on how to preform some common tasks see the Quick Actions section..

Detailed documentation, licensing information, and much more can be found atthe MySQL web site. Many books are available which describe SQL in detail. Ifyou plan on doing much database development, it is recommended that you review one or more of these.

Using The MySQL ControlPanel

The MySQL feature inside your Control Panel is where you manage your database,including desiging tables, adding, deleting, and updating records, all fromwithin your web browser.

When you first click on the MySQL feature, you will be asked to provide a namefor your database and a password, you can use the same username and passwordthat you use for your Control Panel if you so desire. Once the database iscreated, and you return to this feature inside your Control Panel it will thenbecome the Welcome page for your database. A tree view is on the left. The nameof your database and the version of MySQL are displayed to the right of thetree.

The Tree

The top entry in the tree, “Home,” will return you to the Welcomepage. Beneath that is your database name and a square with a plus or minus signin it. Clicking the square will show and hide the names of the tables in thedatabase in the tree. Clicking on the database name in the tree will displaythe main database management page. Clicking on one of the tables names in thetree will display the properties of that table.

The Main Database Management Page

This page displays a list of all the tables in your database and the number ofrecords in each. You can also execute an SQL statement, perform advancedqueries, dump the database, and create new tables.

The List of Tables

Next to each table name are links to various actions you can perform on atable.

Browse Display the records in the table 30 at a time. From the Browse page youcan edit or delete a record.

Select Build and execute a SELECT query on the table. Only those records whichmatch the criteria you provide will be displayed.

Insert Add a new record to the table. Enter the data in the fields provided.Various functions can be used to obtain the current time, generate randomnumbers, and more. Press the Save button to insert the record into the table.

Properties Display the fields in the table with their datatype and attributes.Table management functions for the table are also provided.

Drop Remove the table and its contents from the database. Once you do thisneither the table nor the data will be available.

Empty Delete all of the records in the table. Once you do this the table willstill exits but the data in the table will no longer be available.

Execute an SQL Statement

Any SQL statement can be executed on your database by typing it into thetextbox labled “Run SQL query/queries on database” and pressing the“Go” button. For help with SQL statements ???

Query by example

Advanced queries can be built and executed using a graphical interface.

View dump (schema) of database

Dumping of the database displays the structure and or data contained in thedatabase. You can then save this information to a file on your local computerfor archiving or to aide in the development of your database. The contents andformat of the dump are based on the radio button and check box selections youmake. See also View dump (schema) of table.

Create a new table

Create a new table by typing in the name of the table and the number of fieldsto be in the table and pressing the “Go” button. You will be shown apage which will allow you to set up the datatype and attributes of eachfield.

Examples of SQL Statements

Below a few examples are provided to give you an idea of what an SQL statementlooks like.

Though they have a specific structure and can perform complex operations, SQLcommands are fairly easy to understand.

For example,

CREATE TABLE Phonebook (
Id char(5),
Name char(50),
Telephone char(11)
);

creates a new table in your database named Phonebook that has three fields, Id,Name, and Telephone, which are characters strings of length 5, 50 , and 11,respectively.

The statement

INSERT INTO Phonebook (Id, Name, Telephone)
VALUES (‘AAAAA’, ‘Joe Smith’,‘800-555-1212’);
adds the data into the named fields as a new record of the Phonebook table inyour database.

The statement

SELECT Name, Telephone
FROM Phonebook
WHERE Id=‘AAAAA’;
searches the table Phonebook and finds the Name and Telephone number of thecustomer whose Id is equal to ‘AAAAA’.

These are, of course, simple statements. Much more complicated databases andqueries can be written using SQL, all of which are supported by MySQL.

Much of your database management will be done through the MySQL Control Panelprovided in your account administration pages and not by typing in commandssuch as above. Though not needed, a working undrstanding of how to read andwrite SQL statements is of great help. There are many books and web sites whichteach SQL and have many more examples. If you plan on doing much databasedevelopment, it is recommended that you review them.

Table Select

From this page you can perform a SELECT operation on the table.

The list box in the upper left contains the names of all the fields in thetable. Select from the list box the columns you wish to see in your resultset.

Any valid WHERE clause can be entered in the Add search conditionstext box.

The field names and a text box are listed again under the Do a ‘query byexample’ bullet. These can be used build a WHERE clause more easily thantyping the entire clause into the textbox above. Each entry becomes a conditionof the WHERE clause. The conditional operator used is LIKE which allows thewildcard operators % and _ to represent zero or morecharacters, and a single character, respectively.

SELECT Name, Telephone
FROM Phonebook
WHERE Id LIKE ‘Joe%’;

The wildcard characters can both appear in the same string and can appear morethan one as needed. Note that using no wildcard characters is equivalent tousing = instead of LIKE. LIKE is generally slower than= since MySQL must still check for wildcards characters. To use= or other conditions you must type them in the general WHEREclause textbox.

Press the Go button at the bottom of the page to process the select statement.If any records are found, they will be displayed in a table for you.

Perl While Loop Example

If your SQL query will return more than one record, you will need to place thefetchrow() call in a while loop.

my (@telephone);
my $i=0;
my $count;
while (@aRow=$sth->fetchrow())
{
$telephone[$i]=@aRow[0];
$i++;
}
$count=$i;

# @telephone can now be used to build an html table
# to display all the telephone numbers in the 518
# area code.

Using MySQL with CGIscripts

Using MySQL with Common Gateway Interface scripts will allow you to developmore interactive web sites. Examples of using CGI scripts with MySQL aresearchable catalogs, user account management, inventory tracking, andinformation management. Any time you have even small quantities of data whichare similar and/or which will change over time, a database solution will likelybe useful.

CGI scripting does require programming experience. If you are not familiar withCGI scripting, it is suggested that you begin with the basics of forms and nondatabase applications. There are many books available to teach you CGIprogramming in a number or languages. Here we will be focusing on how toprogram MySQL using Perl as the CGI scripting language.

A Quick Review of How CGIWorks

Normally clicking on a link in a web browser causes the web server to return astatic .html page. No matter who clicks on this link or how many times they doit, the resulting returned web page is always the same. To change a static.html page the site’s webmaster must edit the contents of the .html file.

On the other hand, a CGI script allows a link or a button in a web page to runa program on the web server. This program can do any number of things fromgetting the current date and time to performing a complex lookup and update ina database. In either case, the results are not the same everytime the link orbutton is pressed.

The process occurs something like this:

User clicks on a link in a web page (e.g.http://www.cgitest.com/cgi-bin/test.cgi).
The web server runs the program test.cgi.
The test.cgi program does what it is programmed to do.
The test.cgi program also builds a .html file in memory and sends it back tothe user’s browser.
It is the last two steps which make CGI scripts so useful. The program canperform what ever operations it needs to and it can then generate a .html pagebased on the results of these operations. When the CGI script is used with adatabase such as MySQL, many things are possible. Generally, the page returnedto the user’s browser contains the results of the database search. Or, if theuser had provided information through a form in the web page, the databaserecords were updated.

Using Perl to Access a MySQLDatabase

The programming language Perl can be used to access a MySQL database. It is thelanguage we will use for our examples. Access to MySQL using Perl requires thePerl DBI module. Both Perl and the DBI module are installed and available touse through your web site account.

The following code example sets up a connection the database to thewww.yourwebsite.com database, prepares and executes an SQL statement, storesthe result in a local variable, and then cleans up the connection.

# Use the DBI module
use DBI qw(:sql_types);

# Declare local variables

my ($databaseName, $databaseUser, $databasePw, $dbh);
my ($stmt, sth, @newRow);
my ($telephone);

# Set the parameter values for the connection
$databaseName=DBI:mysql:yourWebSite_com;
$databaseUser=yourLoginId;
$databasePw=yourLoginPassword;

# Connect to the database
# Note this connection can be used to
# execute more than one statement
# on any number of tables in the database

$dbh=DBI->connect($databaseName, $databaseUser,
$databasePw) || die Connect failed: $DBI::errstrn;

# Create the statement.
$stmt=SELECT Name FROM Phonebook
WHERE (Telephone LIKE ‘518%’);

# Prepare and execute the SQL query
$sth=$$dbh->prepare($$stmt)
|| die prepare: $$stmt: $DBI::errstr;
$sth->execute || die execute: $$stmt: $DBI::errstr;

# Get the first record
# If more than one record will be returned put
# the fetchrow in a while loop
@record=$sth->fetchrow()

# Get the value of the first field returned.
$telephone=$record[0];

# Clean up the record set and the database connection
$sth->finish();
$dbh->disconnect();

All queries follow the same basic formula. Simply replace the SELECT statementwith the INSERT, UPDATE, DELETE, etc. statement you wish to use. Note thatthese other queries do not return records. So, the fetchrow() and assignmentwhich follows should be deleted for then.

Many other operations such as joins, subqueries, grouping, and sorting are allsupported by providing a proper SQL statement in place of the one above.

To run/lookat mysql:
/usr/local/mysql/bin/mysql mysql -p$rootpass
[examples:]
mysql> show tables;
mysql> select * from user;
mysql> select * from db;
mysql> show columns from db;
mysql> select User, Password from user;
mysql> exit
[etc.]


References andTutorials

Books

MySQL and mSQL
Randy Jay Yarge, George Reese, and Tim King
O’Reilly & Associates
ISBN 1565924347

The Practical SQL Handbook: Using Structured Query Language
Judith S. Bowman, Sandra L. Emerson and Marcy Darnovsky
Addison-Wesley
ISBN 0201626233

Understanding SQL
Martin Gruber
Sybex
ISBN 0895886448

Teach Yourself Sql in 21 Days
Ryan K. Stephens (Editor), Ronald R. Plew, Bryan Morgan, jeff Perkins
Sams Publishing
ISBN 0672311100

Be sure to check for the most current edition.

Web Sites

The MySQL site has an SQL reference and lots of information about MySQL inparticular.

An SQL tutorial is available on the net at http://www.geocities.com/SiliconValley/Vista/2207/sql1.html

Newgroups

There are various newsgroups under the comp.database group which deal withdatabases. Always a good place to start.

Mailing Lists

The MySQL site lists in their documentation page a number of mailing lists concerning MySQL and SQL

*Please note that we do not provide free technical or development support for MySQL applications.