The MySQL option in your control panel automates
MySQL.
The following information will assist
you in using and implementing MySQL.
Silver
Bullet Hosting does not provide technical support for
using MySQL, however you will find loads of useful information
as well as additional resources that will assist you
along your way.
Overview | MySQL Control Panel Feature | Examples of SQL Statements
Quick Actions | Advanced Queries | Table Properties | Table Select
Perl SQL Delete Example | Perl SQL Insert Example | Perl SQL Update Example
Perl While Loop Example | View Dump Database Schema
Using MySQL With CGI
Scripts
| References
and Tutorials
My SQL Overview
SQL stands
for Structured Query Language. It is the most common
language used for 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 or a 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 perform 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.
Using The MySQL Control Panel
The MySQL feature inside your Control Panel
is where you manage your database, including designing
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 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 labeled “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 the database.
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. 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 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.
Examples of SQL Statements
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, all of which are supported by MySQL.
Much of your database management will be done through
the MySQL Control Panel provided in your account administration
pages and not by typing in commands such as above. Though
not needed, a working understanding of how to read and
write SQL statements is of great help. There are many
books and web sites which teach SQL and have many more
examples. If you plan on doing much database development,
it is recommended that you review them.
Quick Actions
For quick step
by step tutorial on how to perform common functions,
see below. For all of the examples, you must first login
to your Control Panel and then into your MySQL feature
and go to the Main Database Management Page of your
MySQL database.
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.
Perform
a search:
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.
Many other operations
can be performed by using the MySQL Control Panel.
Advanced Queries
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'd
in the WHERE clause. If the "Or" radio button
is selected, that row will be logically OR'd in the
WHERE clause.
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.
Table Properties
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.
Index Create an index on the field
for faster searching.
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 as well.
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 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.
Perl SQL Delete Example
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 ($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::errstr\n";
# Create the statement.
$stmt = "DELETE
FROM Phonebook WHERE (Id = 'BBBBB')";
# Prepare and execute the SQL query
$sth = $$dbh->prepare($$stmt)
|| die "prepare: $$stmt: $DBI::errstr";
$sth->execute || die "execute: $$stmt: $DBI::errstr";
# DELETE does not return records
# Clean up the record set and the database connection
$sth->finish();
$dbh->disconnect();
Perl SQL Insert Example
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 ($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::errstr\n";
# Create the statement.
$stmt = "INSERT
INTO Phonebook (Id, Name, Telephone)
VALUES (‘BBBBB’,
‘Joe Smith’, ‘212-555-1212’)";
# Prepare
and execute the SQL query
$sth = $$dbh->prepare($$stmt)
|| die "prepare: $$stmt: $DBI::errstr";
$sth->execute || die "execute: $$stmt:
$DBI::errstr";
# INSERT does not return
records
# Clean up the record set
$sth->finish();
# We could add another record here as well
# Create the statement.
$stmt = "INSERT
INTO Phonebook (Id, Name, Telephone)
VALUES (‘CCCCC’,
‘Marcy Jones’, ‘402-555-1212’)";
# Prepare
and execute the SQL query
$sth = $$dbh->prepare($$stmt)
|| die "prepare: $$stmt: $DBI::errstr";
$sth->execute || die "execute: $$stmt:
$DBI::errstr";
# Clean up the record
set and the database connection
$sth->finish();
$dbh->disconnect();
Perl
SQL Update Example
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 ($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::errstr\n";
# Create the statement.
UPDATE Addresses SET
Last = 0 WHERE CustomerId = '$$customerId'
$stmt
= "UPDATE Phonebook
SET Telephone = '713-555-1212'
WHERE Name LIKE '%Smith'";
# Prepare
and execute the SQL query
$sth = $$dbh->prepare($$stmt)
|| die "prepare: $$stmt: $DBI::errstr";
$sth->execute || die "execute: $$stmt: $DBI::errstr";
# UPDATE does not return records
# Clean
up the record set and the database connection
$sth->finish();
$dbh->disconnect();
Perl
While Loop Example
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 $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.
View Dump of Schema
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 obtain
a dump of a single table. The additional radio button,
CVS will return the data in the table with each record
as a separate 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.
Using MySQL with CGI
scripts
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
likely 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
in a number or languages. Here 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 every time
the link or button 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 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. When the CGI script
is used with a database such as MySQL, many things are
possible. Generally, the page returned to the user's
browser contains the results of the database search.
Or, if the user had provided information through a form
in the web page, the database records were updated.
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 to use through your web site account.
The following code example sets up a connection
the database to the www.yourwebsite.com database, 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 ($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::errstr\n";
# 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 statement with the
INSERT, UPDATE, DELETE, etc. statement you wish to use.
Note that these other queries do not return records.
So, the fetchrow() and assignment which follows should
be deleted for then.
Many other operations
such as joins, subqueries, grouping, and sorting are
all supported by providing a proper SQL statement in
place of the one above.
References and Tutorials
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 in particular.
An SQL tutorial is available on the net at http://www.geocities.com/SiliconValley/Vista/2207/sql1.html
Newsgroups
There are various
newsgroups under the comp.database group which deal with
databases. 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.
Back to Main Menu |
