Using MySQL

MySQL support is included with the Platinum and Platinum Plus web hosting plans, and can be optionally added to the Gold web hosting plan.

This overview provides an introduction to MySQL. For more detailed information, please download the MySQL Reference Manual.

FastVirtual does not provide technical support for MySQL development, installation, or programming problems. If you require MySQL technical support, please visit the the MySQL web site, which includes extensive documentation and support options, in addition to information that will assist you in finding a database developer.

First-Time Database Developers

If you are new to database development, and are unfamiliar with SQL databases, then MySQL is not really the best place to start. You may be more interested in Miva Script, which provides an entry-level database system that is much easier to learn, and is also compatible with several popular database formats.

Creating a MySQL Database

MySQL-enabled plans include a single database (a database consists of 0 or more tables). Additional databases can be optionally added if required.

FastVirtual provides phpMyAdmin for adding and managing MySQL databases. To access phpMyAdmin, select "Developer's Corner" from the "Hosting" area of your account control panel.

Adding, Editing and Deleting Tables

Tables are easily created, edited, and deleted through the phpMyAdmin interface. This provides a fast and easy method for managing databases without the need for telnet access.

Adding and Retrieving Data

Once your database has been created, and you've added a table, your database will be ready to start storing data. You can enter data into your database as follows:

insert into table (account, emailtype, address, destination) values ('abc','d','ef@gh.ij','kl@mn.op')

You can retrieve data from your database as follows:

select account, emailtype, address from mytable where emailtype = 'd' and destination like 'a%' order by account

MySQL also supports aggregate functions, for example:

select count(account) from mytable
select avg(emailusage) from mytable

These commands would usually be executed from within Perl or C scripts.

Note: Don't forget to include a backslash ("\") before single quotes or backslashes.

Including MySQL in Your Perl Scripts

Perl is the preferred choice for many database developers. The following demonstrates the use of MySQL within Perl:

use DBI;
$dbh = DBI->connect("DBI:mysql:mydatabase:localhost", "username","password") ||
     die "DBI->connect: $DBI::errstr ";

$sql_stmt = "insert into mytable (field1, field2, field3)
     values (?, ?, ?)";
$sth = $dbh->do($sql_stmt, {}, "value1", "value2", "value3") ||
     die "dbh->do($sql_stmt: value1, value2, value3): $DBI::errstr ";

$sql_stmt = "select field1, field2, field3 from mytable";
$sth = $dbh->prepare($sql_stmt) ||
     die "dbh->prepare($sql_stmt): $DBI::errstr ";

$sth->execute ||
     die "sth->execute($sql_stmt): $DBI::errstr ";

while (($field1, $field2, $field3) = $sth->fetchrow)
{
     print "column 1 = $field1; column 2 = $field2; column 3 = $field3 ";
}

Including MySQL in Your PHP Scripts

PHP is an rapidly becoming a popular alternative to more complex scripting methods. The following demonstrates the use of MySQL within PHP:

<?php

mysql_connect("localhost","username", "password");

$result = mysql_db_query("mydatabase","select * from table");

echo "<TABLE BORDER=1 CELLPADDING=5> ";
while($row = mysql_fetch_row($result))
{
     echo "<TR>";
     for ($i = 0; $i < count($row); $i++)
     {
          echo "<TD>";
          echo $row[$i];
          echo "</TD> ";
     }
     echo "</TR> ";
}
echo "</TABLE> ";

mysql_free_result($result);

?>

Including MySQL in Your C Scripts

For information about using MYSQL within C, please download the MySQL Reference Manual.

MySQL for mSQL Developers

Users that are familiar with mSQL will be pleased to know that MySQL and mSQL are fully compatible. However, MySQL includes several benefits over mSQL, including performance, security and feature set improvements.

For those interested in porting an mSQL application to a MySQL database, this can be easily done as follows:

/usr/local/bin/replace msqlConnect mysql_connect msqlListDBs mysql_list_dbs msqlNumRows mysql_num_rows msqlFetchRow mysql_fetch_row msqlFetchField mysql_fetch_field msqlFreeResult mysql_free_result msqlListFields mysql_list_fields msqlListTables mysql_list_tables msqlErr mysql_error msqlStoreResult mysql_store_result msqlQuery mysql_query msqlField mysql_field msqlSelect mysql_select msqlSelectDB mysql_select_db msqlNumFields mysql_num_fields msqlClose mysql_close msqlDataSeek mysql_data_seek m_field MYSQL_FIELD m_result MYSQL_RES m_row MYSQL_ROW msql mysql mSQL mySQL MSQL MYSQL msqlCreateDB mysql_create_db msqlDropDB mysql_drop_db msqlFieldSeeek mysql_field_seek -- $*

Find Out More

This overview only provides a brief introduction to MySQL. For detailed information about MySQL's comprehensive feature list, including aggregate functions, indexing, command syntax, and much more, please download the MySQL Reference Manual.