1. Overview
2. MySQL Control Panel Feature
3. Examples of SQL Statements
4. Quick Actions
5. Advanced Queries
6. Table Select
7. Table Properties
8. Perl SQL Delete Example
9. Perl SQL Insert Example
10. Perl SQL Update Example
11. Perl While Loop Example
12. View Dump Database Schema
13. Using MySQL With CGI Scripts
14. References and Tutorials

SQL stands for Structured Query Language. It is the most common language used for accessing databases. SQL has been standardized for use in databases, and is used in such databases as MySQL, MS-SQL, Oracle and several others. However, there are minor differences in the SQL syntax adopted by different vendors. Your control panel lets you manage the MySQL database for your account.

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. Setup and management of 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 (CGI) scripts. These scripts are small computer programs which run on the web hosting server and are activated by clicking on a link or a button in a web page. This help you add considerable functionality for your site's users. Using CGI scripts and MySQL you can maintain account information on visitors, allow people to search and browse catalogs, and much more. See See Using MySQL with CGI scripts for instructions 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 page.

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

Please note that Webmaintainer does not provide technical or development support for MySQL applications.

Top

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

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

The Tree

The top entry in the tree, “Home,” will return you to the Welcome page. Beneath that is your database name and a square with a plus or minus sign in it. Clicking the square will show and hide the names of the tables in the database in the tree. Clicking on the database name in the tree will display the main database management page. Clicking on one of the tables names in the tree 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 of records in each. You can also execute an SQL statement, perform advanced queries, 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 a table.

Browse - Display the records in the table, 30 records at a time. From the Browse page you can edit or delete a record.

Select - Build and execute a SELECT query on the table. Only those records which match 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 random numbers, 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 this neither the table nor the data will be available.

Empty - Delete all of the records in the table. Once you do this the table will still exist 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 the textbox labled “Run SQL query/queries on database” and pressing the 'Go' button.

Query by example

Use this feature to build and execute advanced queries using a graphical interface.

View dump (schema) of database

Dumping of the database displays the structure and or data contained in the database. You can then save this information to a file on your local computer for archiving or to aid in the development of your database. The contents and format of the dump are based on the radio button and check box selections you make.

Create a new tables

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

Top

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

Though they have a specific structure and can perform complex operations, SQL commands 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 in your database.

The statement

SELECT Name, Telephone
FROM Phonebook
WHERE Id = ‘AAAAA’;

searches the table Phonebook and finds the Name and Telephone number of the customer whose Id is equal to ‘AAAAA’.

These are, of course, simple statements. Much more complicated databases and queries can be written using SQL.

While you would carry out most of your database management through the MySQL Control Panel rather than by typing in SQL syntax, a a working knowledge of how to read and write the commands is of great help. We've listed some reference books and sites that will help you learn SQL further. If you plan on doing much database development, it is recommended that you review them.

Top

Create a table

1. Type in the name of the new table and the number of fields for the table in the textboxes provided.
2. Press the Go button next to the Fields textbox.
3. Enter in the name of each field and the datatype of the field. Other attributes of the field can be set as well. Length of char strings, not null, default value are commonly used attributes.
4. You can also specify primary fields, indexes and unique fields here as well. These can also be set for individual fields from the table properties page.
5. If everything is correctly specified, when you press the Save button the table will be created and you will end up on the table properties page for the new table.

Add a record

1. From the Main Database Management Page or the table properties page press the Insert link.
2. Enter in a value for each field. You must provide a value for any field which set not null and has no default value. If you do not provide a value the default value will be used if provided. The functions in the menu can be used to generate a value for the field for you. Note that the functions may require a value to operate on.
3. Press the Save button.

1. From the Main Database Management Page or the table properties page press the Select link.
2. Fill out the form fields as described here.
3. Press the Go button.

Queries are built by selecting the fields to search on and the criteria to use for the search. The SQL statement that will be executed is displayed in the textbox in the lower right. The statement is updated to reflect the values provided in the rest of the form fields on the page by pressing the 'Update Query' button. Execute the statement by pressing on the 'Submit Query' button.

Each column can be used to specify a field for the SQL statement. Empty columns are ignored.

The fields specified in the 'Fields' row are combined with criteria below it to create a WHERE clause. If the 'Show' checkbox in on then the field is placed in the SELECT clause as well. The query results may be sorted on a field based on the selection in the 'Sort' menu.

More fields can be added by turning on the 'Ins' checkbox below a column or selecting a positive number in the 'Add/Delete Field Columns' menu. Fields are deleted by turning on the 'Del' checkbox or selecting a negative number in the 'Add/Delete Field Columns' menu. Press 'Update Query' to update the page to reflect the changes. You may have to scroll your web browser to the right to see all of the field columns.

The tables selected in the 'Use Tables' listbox form the FROM clause. Also, the fields listed in the 'Fields' menus are restricted to the fields in the selected tables.

Each criteria should be placed on a separate criteria row. If the 'And' radio button is selected for a criteria row, that row will be logically AND-ed in the WHERE clause. If the 'Or' radio button is selected, that row will be logically OR-ed.

Criteria are not required for any column. If not provided, and the 'Show' checkbox is on, the field will be shown for all records that match any other criteria.

Criteria are added and deleted in a manner similar to adding/deleting fields using the checkboxes to the left of a criteria row or the 'Add/Delete Criteria Row' menu. Again, press 'Update Query' to update the page.

Top

The fields in the table are listed with their datatypes and attributes.

The List of Fields

Next to each field name are links to various actions you can perform on a field.

Change - Change the attributes of a field.

Drop - Delete the field from the table. Once you do this the data will be no longer available.

Primary - Set the field to be a primary field (all values have to be unique and no record can have a null value in this field).

Index - Create an index on the field for faster seaching.

Unique - Require all values in the field to be unique.

Primary fields and indexes

The primary fields and indexes are listed again below the list of all fields.

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

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

Insert - Add a new record to the table. Various functions can be used to obtain the current time, generate random numbers, and more.

Add New Fields - To add one or more new fields to the table, select the number of fields to add and press the 'Go' button next to the text 'Add new field'.

Upload Data - 'Insert textfiles into table'allows you to load data into the table from a properly formatted text file on your local computer.

Dump Table Properties - 'View dump (schema) of table' displays the structure and or data contained in the table. You can then save this information to a file on your local computer for archiving or to aide in the development of your database. The contents and format of the dump are based on the radio button and check box selections you make.

Rename and Copy - You can rename or copy a table with this option.

Top

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 the table. Select from the list box the columns you wish to see in your result set.

Any valid WHERE clause can be entered in the 'Add search conditions' text box.

The field names and a text box are listed again under the 'Do a 'query by example'' bullet. These can be used build a WHERE clause more easily than typing the entire clause into the textbox above. Each entry becomes a condition of the WHERE clause. The conditional operator used is LIKE which allows the wildcard operators '%' and '_' to represent zero or more characters, 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 more than one as needed. Note that using no wildcard characters is equivalent to using '=' 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 WHERE clause 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.

Top

Here we delete a record from the database using a DELETE statement.

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

# Declare local variables

my (, , , );
my (, sth, @newRow);
my ();

# Set the parameter values for the connection
= 'DBI:mysql:yourWebSite_com';
= 'yourLoginId';
= '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

= DBI->connect(, ,
) || die 'Connect failed: ::errstr ';

# Create the statement.
= 'DELETE FROM Phonebook WHERE (Id = 'BBBBB')';

# Prepare and execute the SQL query
= $->prepare($)
|| die 'prepare: $: ::errstr';
->execute || die 'execute: $: ::errstr';

# DELETE does not return records

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

Top

Here we add two records to the database using an INSERT statement. The data to be entered can be gathered from an html form.

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

# Declare local variables

my (, , , );
my (, sth, @newRow);
my ();

# Set the parameter values for the connection
= 'DBI:mysql:yourWebSite_com';
= 'yourLoginId';
= '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

= DBI->connect(, ,
) || die 'Connect failed: ::errstr ';

# Create the statement.
= 'INSERT INTO Phonebook (Id, Name, Telephone)
VALUES (‘BBBBB’, ‘Joe Smith’, ‘212-555-1212’)';

# Prepare and execute the SQL query
= $->prepare($)
|| die 'prepare: $: ::errstr';
->execute || die 'execute: $: ::errstr';

# INSERT does not return records

# Clean up the record set
->finish();

# We could add another record here as well

# Create the statement.
= 'INSERT INTO Phonebook (Id, Name, Telephone)
VALUES (‘CCCCC’, ‘Marcy Jones’, ‘402-555-1212’)';

# Prepare and execute the SQL query
= $->prepare($)
|| die 'prepare: $: ::errstr';
->execute || die 'execute: $: ::errstr';

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

Top

Here we update a record in the database using an UPDATE statement.

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

# Declare local variables

my (, , , );
my (, sth, @newRow);
my ();

# Set the parameter values for the connection
= 'DBI:mysql:yourWebSite_com';
= 'yourLoginId';
= '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

= DBI->connect(, ,
) || die 'Connect failed: ::errstr ';

# Create the statement.
UPDATE Addresses SET Last = 0 WHERE CustomerId = '$'
= 'UPDATE Phonebook
SET Telephone = '713-555-1212'
WHERE Name LIKE '%Smith'';

# Prepare and execute the SQL query
= $->prepare($)
|| die 'prepare: $: ::errstr';
->execute || die 'execute: $: ::errstr';

# UPDATE does not return records

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

Top

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

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

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

Top

The 'View Dump (Schema) of Database' section of the Main Database Management page is useful. Pressing the associated 'Go' button will generate a page containing the SQL statements for recreating the database. If the 'Structure and Data' radio button is selected, the SQL statements for INSERTing the data will be generated as well. Turn on the. 'Add 'DROP TABLE'' checkbox and the SQL statements to DROP the tables will be included also. When you drop a table, the table is deleted. Turning on the 'Send' checkbox, causes the generated SQL statements to be sent to you as a file which you can save to your hard disk.

The 'View Dump(Schema) of Table' section of the Table Properties page allows you to obain a dump of a single table. The additional radio button, CVS will return the data in the table with each record as a seperate line. The fields are delimited by the character specified in the 'Terminated by' textbox.

The dumped data can be imported into another database or a spreadsheet, or archived for backup.

NOTE: None of the selections above will alter your database.

Top

Using MySQL with Common Gateway Interface scripts will allow you to develop more interactive web sites. Examples of using CGI scripts with MySQL are searchable catalogs, user account management, inventory tracking, and information management. Any time you have even small quantities of data which are similar and/or which will change over time, a database solution will probably be useful.

CGI scripting does require programming experience. If you are not familiar with CGI scripting, it is suggested that you begin with the basics of forms and non-database applications. There are many books available to teach you CGI programming using a number of scripting or programming languages. We will be focusing on how to program MySQL using Perl as the CGI scripting language.

A Quick Review of How CGI Works

Normally, clicking on a link in a web browser causes the web server to return a static .html page. No matter who clicks on this link or how many times they do it, 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 run a program on the web server. This program can do any number of things from getting the current date and time to performing a complex lookup and update in a database. In either case, the results are not the same everytime the link or button is pressed.

The process occurs something like this:

1.User clicks on a link in a web page (e.g. http://www.cgitest.com/cgi-bin/test.cgi).
2.The web server runs the program test.cgi.
3.The test.cgi program does what it is programmed to do.
4.The test.cgi program also builds a .html file in memory and sends it back to the user's browser.

It is the last two steps which make CGI scripts so useful. The program can perform what ever operations it needs to and it can then generate a .html page based on the results of these operations. Thus CGI and MySQL give you a great deal of options to make your site more interactive and feature-rich.

Using Perl to Access a MySQL Database

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

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

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

# Declare local variables

my (, , , );
my (, sth, @newRow);
my ();

# Set the parameter values for the connection
= 'DBI:mysql:yourWebSite_com';
= 'yourLoginId';
= '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

= DBI->connect(, ,
) || die 'Connect failed: ::errstr ';

# Create the statement.
= 'SELECT Name FROM Phonebook
WHERE (Telephone LIKE '518%')';

# Prepare and execute the SQL query
= $->prepare($)
|| die 'prepare: $: ::errstr';
->execute || die 'execute: $: ::errstr';

# Get the first record
# If more than one record will be returned put
# the fetchr

Top