World Class Web Hosting
+1.877 724-7638

my pair account webmail

Web hosting services

 

  • pairNIC - domain name registration (as low as $13/year)
  • pairSIM - one-click software installation
  • pairCDN - content delivery network
  • pair reseller program - reselling our Web site hosting services
  • pair Networks does not offer "Business Associate" services under HIPAA

about pair Networks

 

Contact Us

pairNIC Support

blog.pair.com

Call Us

  • +1.877 724-7638
  • +1.412 381-7247

Fax

  • +1.412 381-9997

E-mail Us

Web contact forms

Mailing Address

  • pair Networks, Inc.
    Suite 210
    2403 Sidney Street
    Pittsburgh, PA 15203-2168

support - knowledge base

 

Authoring/Development - Using MySQL

What is MySQL?

MySQL is a true multi-user, multi-threaded SQL database server. It is our preferred solution for customers that need databases to drive their Web sites.

Setting up a MySQL Database

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

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

» Account Control Center

Once logged in, click on "Create a New Database" under the "Database Management" heading.

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 pair Networks username, and "something" is a name of your choosing up to 20 characters. Please note that your username is automatically added to the beginning of the database name you entered. For example, if you entered a database name "test123," your database name would be "username_test123."
  • Access Level - all databases have two available access levels. With "Local Only" access, the database can only be accessed from servers within our 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 configuration 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 password, 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.
  • Manage Using phpMyAdmin - manage your database using the phpMyAdmin database management tool.

Managing Your Databases with phpMyAdmin

You can manage your databases using phpMyAdmin, a Web-based database management tool. phpMyAdmin can be accessed from the ACC by following the instructions below:
  1. Click "Manage Using phpMyAdmin" on the database details page
  2. Enter the password for your database's full access username
  3. Click "Login to phpMyAdmin"

If you forget your database password, select "Change A Password," and create a new password.

You can also manage your databases using phpMyAdmin directly using the address below:

https://my-db.pair.com/phpMyAdmin/
This page will ask you for your database server name, username, and password. If you don't know this information, you can find out by following the instructions below:
  1. Log into the Account Control Center (https://my.pair.com/)
  2. Click "Advanced Features"
  3. Click "Database Administration"
  4. Click the name of your database
If you forget your database password, select "Change A Password," and create a new password.

Accessing a Database via the UNIX Shell

Most connections made to your MySQL database while you are connected to your account via SSH (Secure Shell) 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.54

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_select_db takes the database name.
// * Together, they open a connection to your database.
mysql_connect($SERVER,$USER,$PASSWORD);
mysql_select_db($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 strict;
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.
my $database = "your database name";
my $username = "your database username";
my $password = "your database password";
my $hostname = "your database hostname";
my $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.
my $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.
my $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 (my ($field1, $field2, $field3) = $query->fetchrow_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 SSH.

Other Resources

» MySQL Homepage
» MySQL Documentation