Tutorials - Using MySQL

                      What is MySQL? 

                      MySQL is a true multi-user, multi-threaded SQL database server. It is our
                      preferred solution for those holding accounts at the Webmaster or higher level,
                      who need databases to drive their web sites.

                      Setting up a MySQL Database 

                      The Account Control Center allows you to setup and configure MySQL databases
                      for your account.

                      To begin configuring your account via the Account Control Center, you must first
                      log in at:

                      My pair Account Control Center 

                      Once logged in, select the Advanced menu, and then select the "Database
                      Administration" from the list of options. At the bottom of the next screen, click on
                      "Create New Database."

                      The database creation screen has three settings you must choose:

                          Database Name - all databases take names in the form
                          "username_something", where username is your username, and something
                          is a name of your choosing, up to 20 characters. 
                          Access Level - all databases have two availble access levels. With "Local
                          Only" access, the database can only be accessed from servers within the
                          pair network. With "Local/Remote" access, the database can also be
                          accessed from outside our network. Note that Remote access is available
                          only for debugging purposes, and not for general use. 
                          Optimization Period - all databases are optimized using MySQL's OPTIMIZE
                          TABLE query on a recurring basis. If you will be frequently inserting new
                          data into your database, choose weekly or bi-weekly. If your data will be
                          mostly static, choose the monthly option. 

                      Click the "Add Database" button, and after the database has been created the
                      Account Control Center will display usage information for the database.

                      Controlling a database with the Account Control Center 

                      The Account Control Center's main Database Administration page lists all
                      databases currently configured in your account. Click on the name of a database
                      to bring up its detailed screen.

                      From this screen, you can change the access-level and optimization period by
                      choosing new values and clicking the appropriate "Change" button. Buttons are
                      also provided for each of the following actions:

                          Optimize Now - initiate an immediate optimization of your database (this
                          feature can only be used once per day). 
                          Create Backup - this will require you to provide the database's full-access
                          username, and will backup the database to a directory in your account. 
                          Change A Password - allows you to change the password of any of the
                          database users to a new value. 
                          Purge Data - allows you to delete all information from the database. Please
                          use extreme caution with this feature. 
                          Delete Database - allows you to delete a database, if you find it to be no
                          longer needed. 

                      Accessing a database via Telnet/SSH 

                      Most connections made to your MySQL database while you are connected to your
                      account via telnet will be made with the mysql command line utility. Below is a
                      sample connection to a database using it: 

                          username@server% mysql -hdb14.pair.com -uusername -p username_dbname
                          Enter password: <enter password here>
                          Welcome to the MySQL monitor. Commands end with ; or \g.
                          Your MySQL connection id is 4 to server version: 3.23.27-beta-log

                          Type 'help;' or '\h' for help. Type '\c' to clear the buffer

                          mysql>

                      Several command-line options are required -- -h signifies hostname, -u
                      username, -p password, and the last argument should be the name of your
                      database. You can specify your password on the command line if you'd like, but it
                      is recommended for security reasons that you do not. MySQL will prompt for your
                      password, as shown above. (It will not be displayed on the screen as you type it.)

                      Once connected via the MySQL monitor, you can use it to issue SQL commands
                      to your database to create, populate, and delete tables. Some examples:

                      Creating a table:

                          mysql> create table test (
                          -> field1 int,
                          -> field2 char(10),
                          -> field3 char(10)
                          -> );
                          Query OK, 0 rows affected (0.02 sec) 

                      Inserting data:

                          mysql> insert into test
                          -> values(1,"name","place");
                          Query OK, 1 row affected (0.00 sec)

                          mysql> insert into test
                          -> values(2,"noun","word");
                          Query OK, 1 row affected (0.00 sec) 

                      Selecting data:

                          mysql> select * from test;
                          +--------+---------+--------+
                          | field1 | field2 | field33|
                          +--------+---------+--------+
                          | 1 | name | place |
                          | 2 | noun | word |
                          +--------+---------+--------+
                          2 rows in set (0.00 sec) 

                      When you are finished interacting with your data, the "exit" command exits the
                      mysql utility:

                          mysql> exit
                          Bye 

                      Connecting to a database via PHP 

                      The PHP scripting language is a popular method of accessing your MySQL
                      database via the web. Below is a sample program using PHP to interact with a
                      MySQL database, which has been commented to explain each function:

                          <?php

                          // Connect To Database
                          // * mysql_connect takes the servername, user, and password
                          // * as arguments. mysql_selectdb takes the database name.
                          // * Together, they open a connection to your database.
                          mysql_connect($SERVER,$USER,$PASSWORD);
                          mysql_selectdb($DATABASE);

                          // Execute Query
                          // * mysql_query takes as its argument the query you are
                          // * executing on the database. It should be assigned to
                          // * a variable -- the variable is used by other functions
                          // * to retrieve the results.
                          $QUERY = mysql_query("SELECT * from test");

                          // How man rows in results?
                          // * mysql_num_rows takes the variable the query was
                          // * assigned to (referred to hereafter as the query
                          // * identifier) and returns the number of rows the query
                          // * resulted in.
                          $NUMROWS = mysql_num_rows($QUERY);

                          // Display Results
                          if ($NUMROWS) {
                          $I = 0;
                          while ($I < $NUMROWS) {
                          // Get Results
                          // * mysql_result returns the value of a specific field
                          // * in a specific row. It takes three arguments: the
                          // * first is the query identifier, the second is the row
                          // * number, and the third is the field name. In this
                          // * example, a while loop is used to process all
                          // * rows.
                          $FIELD1 = mysql_result($QUERY,$I,"field1");
                          $FIELD2 = mysql_result($QUERY,$I,"field2");
                          $FIELD3 = mysql_result($QUERY,$I,"field3");
                          echo "field1 = $FIELD1, field2 = $FIELD2, field3 = $FIELD3 \n";
                          $I++;
                          }
                          }

                          ?> 

                      PHP provides many other MySQL-related functions -- you can read about each in
                      the documentation at the PHP Homepage.

                      Connecting to a database via Perl 

                      You can also choose to use Perl and the DBI Perl Module to access your
                      database. A commented example:

                          #!/usr/local/bin/perl

                          use DBI;

                          # Connect To Database
                          # * The DBI interface to MySQL uses the method "connect" to make a
                          # * connection to the database. It takes as it's first argument
                          # * the string "DBI:mysql:database:hostname", where database is equal
                          # * to the name of your database, and hostname to the server that it's
                          # * located on. The second and third arguments, respectively, should
                          # * be your account username and password. The connection is assigned.
                          # * to a variable that is used by most other methods in the module.
                          $database = "your database name";
                          $username = "your database username";
                          $password = "your database password";
                          $db = DBI->connect("DBI:mysql:$database:$hostname", $username, $password);

                          # Execute a Query
                          # * executing a query is done in two steps. First,
                          # * the query is setup using the "prepare" method.
                          # * this requires the use of the variable used to
                          # * initiate the connection. Second, the "execute"
                          # * method is called, as shown below.
                          $query = $db->prepare("SELECT * FROM test");
                          $query->execute;

                          # How many rows in result?
                          # * the "rows" method using the variable name the
                          # * query was executed under returns the number
                          # * of rows in the result.
                          $numrows = $query->rows;

                          # Display Results
                          # * the fetchrow_array method executed on the
                          # * query returns the first row as an array.
                          # * subsequent calls return the other rows in
                          # * sequence. It returns zero when all rows have
                          # * been retrieved.
                          while (@array = $query->fetchrow_array) {
                          ($field1, $field2, $field3) = @array;
                          print "field1 = $field1, field2 = $field2, field3 = $field3 \n";
                          }

                          # Cleaning Up
                          # * with the DBI module, it is a good idea to clean up by
                          # * explicitly ending all queries with the "finish" method,
                          # * and all connections with the "disconnect" method.
                          $query->finish;
                          $db->disconnect;

                          exit(0); 

                      The DBI Module provides other methods you might find useful. More information
                      is available by running "perldoc DBI" while logged on to your account via telnet or
                      SSH.

                      Other Resources 


                          MySQL Homepage 
                          MySQL Manual 
                          SQL Tutorial