Mysql Database

The following documentation provides a brief introduction to the use of Manic's SQL database system, "Mysql". For detailed information about Mysql, please consult the Mysql documentation, or visit the Mysql web site. As with CGI, Manic Design 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 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 Manic's SQL Executer (available in your Account Control Panel). 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 Manic'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

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.


Main | Signup | Hosting | Design | Webmaster Resources | Contact Us!




Copyright © 1996-1998, Manic Design
support@manicdesign.com