Web Hosting

Mysql Database

The following documentation provides a brief introduction to the use of CAMEX VI's SQL database system, "Mysql". For detailed information about Mysql, please consult the Mysql documentation, or visit the Mysql web site. As with CGI, CAMEX Virtual Image does NOT provide support for installation, development, or programming issues. There is a wealth of information provided on the Mysql web site, including various support options, and information on finding a Mysql developer.

SQL

If you are not familiar with SQL, Mysql (or any SQL database, for that matter) is probably not for you. Miva provides a beginner database which proves more-than-sufficient for most needs.

Creating your database

Each SQL-enabled account includes access to a single Mysql database (a database consists of zero or more tables). This database is created for you upon request, and is typically named db_yourname_com. Multiple databases per account are not supported.

Access to your database is restricted by username and password, where appropriate.

Managing tables

Tables can be created, modified, and deleted using CAMEX VI's SQL Executer (available in the customer service area). A "create" statement with different data types and indexing looks like:

create table my_table_name 
                        (

  account char(40) not null,

  email_type char(1),

  email_usage int,

  address char(80),

  destination char(80),

  other_info blob,

  key key01 (account,email_type)

                        );

To view the description of a table, use the following syntax:

desc my_table_name

To delete a table, removing all contents:

drop my_table_name

You can also use the Table List feature of the SQL Executer to list your table information.

To change a table's structure without deleting it, use the alter table command. For information on this, and other table management commands, please consult the Mysql documentation.

Managing data

Once you have a table created, you will typically want to enter data into and query data from the database. Data entry can be done as follows (continuing the previous example):

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

Data retrieval can be done as follows:

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

Mysql supports several aggregate functions, i.e.:

select count(account) 
                        from my_table_name

                        select avg(email_usage) from my_table_name

While the above commands can be entered using CAMEX VI's SQL Executer, you will typically want to execute them from within your Perl or C code.

Remember to "backslash" single quotes and backslashes before entering them into the Mysql command stream.

Using Mysql in Perl

The following code fragment illustrates the use of Mysql in Perl:

use Mysql;

  $dbh = Mysql->connect("localhost","db_name","username","password");

                        if (!$dbh)

                        {

    print "Couldn't contact the Mysql 
                        engine... database is down or does not exist.\n";

    exit;

                        }

                        

                        $sth = $dbh->query("select * from my_table");

                        if (!$sth)

                        {

    print "Syntax error in SQL query.\n";

    exit;

                        }

                        

                        while (@row = $sth->fetchrow)

                        {

    print "column 1 = $row[0]; column 
                        2 = $row[1]; column 3 = $row[2]\n";

                        }

Using Mysql in PHP

The following code fragment illustrates the use of Mysql in PHP:

 <?php 

                        

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

                        

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

                        

                        echo "<TABLE BORDER=1 CELLPADDING=10>\n";

                        while($row = mysql_fetch_row($result))

                        {

    echo "<TR>";

    for ($i = 0; $i < count($row); 
                        $i++)

    {

        echo "<TD>";

        echo $row[$i];

        echo "</TD>\n";

    }

    echo "</TR>\n";

                        }

                        echo "</TABLE>\n";

                        

                        mysql_free_result($result);

                        

                        ?>

Using Mysql in C

Please consult the Mysql documentation for information on using Mysql with C

Mysql vs. Msql

Mysql has been designed to be completely compatible with Msql (mini SQL, a popular database by Hughes Technologies), for those who are familiar with that database system. However, Mysql includes significant feature set, performance, and security improvements over Msql. Porting a Msql application to Mysql is as easy as executing this command on your code:

/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 -- $*

More Information

This document only scratches the surface of Mysql's capabilities. There are hundreds of items -- indexing, aggregate functions, command syntax, detailed Perl and C API, and more -- discussed in the Mysql documentation. Developers interested in seriously using Mysql should take the time to read it.