[ o n l i n e   r e s o u r c e s ]

MySQL Database

The following documentation provides a brief introduction to the use of Xenoplanet's SQL database system, "MySQL". For detailed information about MySQL, please consult the MySQL documentation, or visit the MySQL web site. As with CGI, Xenoplanet 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.

MySQL

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.
The version currently running on our servers is 4.0.13.

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_yourdomain_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 Xenoplanet'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 table 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 Xenoplanet'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 DBI; $dbh = DBI->connect("DBI:mysql:db_name:localhost", "username", "password") ||     die "DBI->connect: $DBI::errstr\n"; $sql_stmt = "insert into my_table (field_1, field_2, field_3)              values (?, ?, ?)"; $sth = $dbh->do($sql_stmt, {}, "value_1", "value_2", "value_3") ||     die "dbh->do($sql_stmt: value_1, value_2, value_3): $DBI::errstr\n"; $sql_stmt = "select field_1, field_2, field_3 from my_table"; $sth = $dbh->prepare($sql_stmt) ||     die "dbh->prepare($sql_stmt): $DBI::errstr\n"; $sth->execute ||     die "sth->execute($sql_stmt): $DBI::errstr\n"; while (($field1, $field2, $field3) = $sth->fetchrow) {     print "column 1 = $field_1; column 2 = $field_2; column 3 = $field_3\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.