To use MySQL, you have to have a UNIX Shared Host service with us.

It is crucial that you have a working knowledge of Perl, CGI, HTML and SQL to effectively use this service. If you aren't sure whether or not this service is appropriate for you, contact support@primushost.com.

Please be aware that MySQL is UNIX-based and thus cannot be used on any NT-based machines.

Setting Up MySQL

MySQL is an SQL database server. It is used to organize large and generally fluid information, such as an inventory list, easily and quickly. You must first purchase the service from us (provided you have either the UNIX Virtual Host or UNIX Dedicated service with us).

At that point, you still need to create an application program interface (API) in order to interact with the mysqld daemon. This will essentially be a graphical interface that you can create with any of the existing languages compilers on the server (i.e. C, C++, DBI, Perl with the DBI (database interface) or MySQL modules, Python, and TCL API's). You can also use PHP, which is an HTML-embedded scripting language to interact with the mysqld daemon.

Connecting to the Database

Which ever language you choose to use for your API, connecting to the daemon will still use the same format from the command line but may vary depeding on the language. For our example we are going to use Perl and the MySQL modules.

From the Command line you would issue the command mysql <host> <userid> <password> <database name> in this format:

mysql -h landshark.primushost.com -u userid -ppassword database_name

Notice that there is no space between the -p and the password; this is important. If you are on the same host as the mysqld daemon you can disregard the host field, as it knows to connect to port 3306 using unix sockets for a speedier and more robust connection.

The first thing you do is create the $dbh variable (database handle = $dbh), which essentially opens the connection to the daemon. For example:

Using DBI:

#!/usr/local/bin/perl5

#declare use of DBI perl module.

use DBI;

$dbh = DBI->connect("DBI:mysql:$database:$hostname:$port",$user, $password);

or

$dbh = DBI->connect("DBI:mysql:$database", $user, $password);

Hostname and port may be left empty if the mysql daemon lives on localhost, as it already knows to look on port 3306 (the default port for mysql daemons). This goes for both DBI and straight perl using sockets, although using the MySQL modules you still need to put in the empty field as shown below.

Using Mysql:

#!/usr/local/bin/perl5

use Mysql;

$dbh = Mysql->connect("", "userid", "password", "database name");

mysql -h landshark.primushost.com -u userid -ppassword database_name

By typing 'perldoc mysql' at the command line in a telnet session, you will recieve a man page describing the different ways to connect to the daemon through Perl. Be advised that mSQL documentation is mixed in among the MySQL docs.

Example MySQL Interaction

 [landshark]/home/1/v/vhost12 [65][2:02pm]> mysql 
-h landshark.primushost.com -u vhost12 -p vhost12 Enter password: ******* Welcome 
to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2316 
to server version: 3.21.30-log Type 'help' for help. mysql> show databases \g 
+-----------+ | Database | +-----------+ | OLD | | mysql | | smarter | | standards 
| | usbionet | | vhost12 | | vhost13 | +-----------+ 7 rows in set (0.00 sec) 
mysql> mysql> show tables \g +-------------------+ | Tables in vhost12 | +-------------------+ 
| contact | +-------------------+ 1 row in set (0.00 sec) mysql> 

Querying the Database

Now you can use the $dbh variable as your link to the database. Depending on which module you used the functions are different (the database below uses the MySQL module). Now another variable is created to temporarily store the results of your query that communicates through the database handle as such:

$sth = $dbh->query("SELECT namel, namef, email_address FROM shore ORDER BY namel");

This query selects the first name, last name, and email address from the table shore and puts them in order by last name.

This variable has now returned information, but it still is not suitable for viewing because it is a MySQL hash index. To retrieve the info create a while loop to dump your temporary variables values into a hash that any user could read:

 while (%hash $sth->fetchhash) 
{ print STDOUT "$hash{namef} $hash{namel} $hash{email}"; } 

Those are the basics of connecting to and selecting from the database.

PRIMUS Sample Contact Database (using mysql & socket connections)

This is a sample database setup with two perl scripts, shore-example.pl (which is called as an SSI via this page) and add-user.pl which is the FORM ACTION for the submit button below. The database consists of four columns: id, namef, namel, email_address. Duplicate email addresses are not allowed.

[an error occurred while processing this directive]

Adding to the Database

Links

Below are links to some MySQL and PHP web sites. PHP is an HTML-embedded scripting language that is used to connect to the MySQL daemon.

• MySQL by T.c.X. DataKonsultAB
• PHP: A server side HTML-embedded scripting language

Back to Shared Hosting Support