Example: Viewing Database Information Over the Web

Before we can view database information we need to create an msql table and add some data to it. (Later we will add data to our tables using html forms and cgi scripts)

  1. Logon to fnts38.fnal.gov.
  2. Begin an msql session by typing:

    3.     msql webdev

  3. Create a new table by typing:

    4.     create table <table_name>

        (first_name char(15) not null,

        last_name char(15) not null,

        age int) \g

    You should replace <table_name> with whatever name you want your table to have. The char(15) which follows first_name and last_name tells msql that you will be storing strings up to 15 characters long in these two columns. The int which follows age tells msql that you will be storing integer values in this column. See the msql quick reference page for all the available data types msql uses. The not null which follows first_name and last_name tells Msql that every entry in our table must have data in these two columns. The \g tells msql that you want to execute the command.

    Your table has been created. It is now time to add data to that table.

  4. Add data to your table by typing:

    5.     insert into <table_name>

        values ('John', 'Smith', 42) \g

        and

        insert into <table_name>

        (first_name, last_name)

        values ('Bill', 'Johnson') \g

    Notice the second insert statement does not contain an entry for the age column. This is okay because when we created the table we did not put not null after age. Make a few more entries with whatever names and ages you want.

  5. Now let’s take a look at our data using the select statement.

    6.     select * from <table_name> \g

        or

        select first_name from <table_name>

        where age = 42 \g

    Try several different select statements to see the different ways you can access your data. See the quick reference page for a complete display of select statement options. Try using order by to cause your data to be displayed in a specific order. Later all these msql statements will be imbedded in Perl scripts.

    There are other Msql statements which I haven't mentioned in this example.  Feel free to try out the other commands as well.  You will find these commands on the Msql quick reference page or on the Msql home page.

  6. Now quit Msql by typing:

    7.     \q

  7. Create a directory were you can place your Perl script by typing the following from the webdev home directory.

    8.    mkdir <directory_name>

    Replace <directory_name> with whatever you want to call your directory. This is where you will put your Perl scripts.  Currently, only the webdev home directory is recognized by the server as an valid place from which to run cgi-scripts.   Once you have created a directory for yourself send me mail and I will activate your directory as well.  Until your dirctory has been activated you can run your scripts from the webdev home directory.

    This system is fairly limited in its available editors, so I recommend that you write your Perl scripts on another machine, using your favorite editor, and then transfer your files to this directory. If you don’t know how to do this, send me mail and I will tell you.

  8. It is now time to write the Perl script that will access the data from our database. So create the following file.

      9.  

      #!/usr/local/bin/perl

use Msql;

my $dbh = Msql->connect;

$dbh->selectdb('webdev');
my $sth = $dbh->query(qq[select * from whitney]);

print("Content-type: text/html\n\n");
print "
        <html>
        <body>
        <table border=\"1\" width=\"727\">\n";

my %hash;
while (%hash = $sth->fetchhash)
{
    print "
        <tr>
            <td align=\"center\">$hash{first_name}</td>
            <td align=\"center\">$hash{last_name}</td>
        </tr>\n";
}

print "
        </table>
        </body>
        </html>\n";

       

  9. Once you have created the file place it in the directory you created earlier or in the webdev home directory(~webdev).

    10.  

  10. You can now run your script from a web browser with the following URL:

    11. http://www-td.fnal.gov/webdev/<directory_name>/<script_name>

    If you are running your script from the webdev home directory you will have no <directory_name>.   If you are running perl code from the directory you created for yourself, replace <directory_name> with the name of your directory.  In either case replace <script_name> with the name of your script.

     

  11. Experiment with your script. Try different Perl functions and different msql statements. Feel free to create more tables and more Perl functions. Let me know if you encounter any problems.

    12.