Dynamic Web

Class 5 - Perl Talks to MySQL: The Web to the Database and Back

The Perl Interface To MySQL
So now that we know how variables that are sent by the web browser can be manipulated by Perl, and how we can set up our database to store and manipulate these values, we need to connect Perl to the database. The interface of Perl to MySQL is a two-level approach consisting of the following:

* DBI (DataBase Interface): a generic interface for Perl
* DBD (DataBase Driver): database-specific interface that talks to the Perl DBI

Thus, when Perl sends a SQL query to MySQL, the query is sent via the DBI to the DBD that is specific to MySQL. MySQL performs the query and sends the results back through its DBD to Perl's DBL interface such that Perl can obtains the results of the query. Visually, the chain is:

Perl -> DBI -> DBD for MySQL -> MySQL -> DBD for MySQL -> DBI -> Perl

Practical Considerations on Stage
In order to connect to your MySQL database via Perl, you need to use your MySQL login and password that was assigned to you. Because this is sensitive information, you should keep it in its own separate script that is only readable and writable by you (i.e. permissions set to "-rw-------" or "600" instead of "-rwxr-xr-x" or "755"). I named mine "info.pl" and it looks something like this:

#########################################################
# info.pl
#########################################################

$mySqlUsername = "your MySQL username - usually your Net ID";
$mySqlPassword = "your MySQL password";

1;

Then, you can reference this information at any time by issuing the statement:

# Get Login Info
require "info.pl";

However, in some environments, you may need to make sure that your main script can find the location of your login info script, so you can use the following to derive the the full directory path to your main script. The result is put in the var "$execDir":

if ($0=~m#^(.*)\\#){ $execDir = "$1"; } # Win/DOS
elsif ($0=~m#^(.*)/# ){ $execDir = "$1"; } # Unix
else {`pwd` =~ /(.*)/; $execDir = "$1"; } # Unix

If you put your login info script in the same directory as your main script, then you know the exact location of your login info script and can reference by its full pathname like this:

# Get Login Info
require "$execDir/info.pl";

Common Perl DBI Methods
You can achieve a lot with only a few commands using the Perl DBI. The main things to keep in mind are that you need to be able to connect to your database, you need to be able to make SQL queries, and you need to be able to disconnect. You connect to the DB once and only once at the beginning of your script, and you disconnect once and only once at the end of your script.

connect
Establishes a database connection, or session, to the requested $data_source. Returns a database handle object if the connection succeeds. Use $dbh->disconnect to terminate the connection.

$dbh = DBI->connect($data_source, $username, $password);

Examples of $data_source values are:

dbi:DriverName:database_name
dbi:DriverName:database_name@hostname:port
dbi:DriverName:database=database_name;host=hostname;port=port

In the real world, the connection goes something like this:

use DBI();

$database = $mySqlUsername;
$data_source = "dbi:mysql:$database";
$username = $mySqlUsername;
$password = $mySqlPassword;

#########################################################
# Connect to the database.
#########################################################
# This only needs to be done once per script
my $dbh = DBI->connect($data_source, $username, $password,
{'RaiseError' => 1, 'PrintError' => 1});

disconnect
Disconnects the database from the database handle. disconnect is typically only used before exiting the program.

#########################################################
# Disconnect from the database.
#########################################################
# Always the last thing you do before exiting your script
$dbh->disconnect();

exit(0);

do
The do method is used to prepare, execute, and finish a single statement. It returns the number of rows affected or undef on error. A return value of -1 means the number of rows is not known, not applicable, or not available.

$rows = $dbh->do($statement)

my $rows_deleted = $dbh->do('DELETE FROM table WHERE status = ?')
or die $dbh->errstr;

The main things about "do" is that it is great for INSERT, UPDATE, and DELETE queries but not for SELECT queries. More on that later. Here's some real world examples of "do":

#########################################################
# Inserting
#########################################################
$myName = "Morgan";
$myName = &escape_quotes($myName);
$SqlStatement = "INSERT INTO class05_table1 (name) ";
$SqlStatement .= "VALUES ('$myName') ";
$affected_rows = $dbh->do($SqlStatement);

#########################################################
# Updating
#########################################################
$myName = "Nancy";
$myName = &escape_quotes($myName);
$SqlStatement = "UPDATE class05_table1 ";
$SqlStatement .= "SET name='$myName' ";
$SqlStatement .= "WHERE id=1 ";
$affected_rows = $dbh->do($SqlStatement);

#########################################################
# Deleting
#########################################################
$myName = "Morgan";
$myName = &escape_quotes($myName);
$SqlStatement = "DELETE FROM class05_table1 ";
$SqlStatement .= "WHERE name='$myName' ";
$affected_rows = $dbh->do($SqlStatement);

Note that there is very little difference in how you make INSERT, UPDATE, and DELETE statements using the "do" method. It's all in how you construct the string that holds the actual SQL statement. In the above examples, we have put this statement into the scalar "$SqlStatement".

prepare, execute, finish
The reality is that "do" is an alias for executing the above three methods and in that order. "Prepare" takes a SQL statement and hands it to the DBD for pre-processing. Once the statement has been "prepared", a statement handle is returned. This handle has a method called "execute" which actual performs the query and returns a result set. Once we are done with this result set, the "finish" method is called to tidy up memory and system resources.

$SqlStatement = "SELECT * FROM class05_table1";
$sth = $dbh->prepare($SqlStatement);
$sth->execute();

# This is where we would obtain the results of our query

$sth->finish();

However, because "do" calls all three methods in a row, we don't ever get access to our result set because "do" has already called "finish", which has relinquished the very results in which we were interested. Thus, you have to explicitly "prepare" your SQL statement, then "execute" it, then obtain your result set using the two methods outlined below, and then "finish" it.

fetchrow_array
The method "fetchrow_array" returns a normal array that contains all the values for the fields requested. It returns one row at a time. We can use the while loop structure in Perl to obtain all the rows from this query.

#########################################################
# SELECT using fetchrow_array()
# Array containing one row of resultset
#########################################################
# $SqlStatement = "SELECT * FROM class05_table1"; # We can use wildcards to get all fields
$SqlStatement = "SELECT id, name FROM class05_table1"; # Or explicitly label fields
$sth = $dbh->prepare($SqlStatement);
$sth->execute();
while (@row_array = $sth->fetchrow_array())
{ $current_id = $row_array[0];
$current_name = $row_array[1];
}
$sth->finish();

Note that the columns in your request match up with the indexes in your array in the order that you request them. Thus, "id" is at index 0 in your array, "name" is at index 1. If our select statement had been:

$SqlStatement = "SELECT name, id FROM class05_table1";

then "name" would be at index 0 in your array, and "id" would be at index 1. Here is a variety of examples that manipulate a table of user firstnames.

Posted by dimitri at 07:53 PM | Comments (0)

February 14, 2005

class 04

relationship between tables

null values to enter no information which once can add at a later time.

Now that we have a basic understanding of how to create a simple table, we need to develop a better understanding of how to separate our data into a particular configuration of tables. Database design has evolved to the point where there are specific steps you can take to optimize any database configuration. This process is called normalization and it has three distinct forms.

forms on normalization found on class notes

removing further redundancy deals with many to many relationship tables.

Multiple Table Joins
The whole point of having a relational database is to be able to connect two or more different tables such that we can extract various sets of data. The process of pulling data from two or more related tables is called a join. The most common is the inner join which has two different syntaxes. For our purposes, we will be using the older syntax, which is a bit easier to read. This format is:

SELECT [DISTINCT] column1[,column2]
FROM table1,table2[,table3]
[WHERE "conditions"]
[GROUP BY "column-list" [ASC | DESC] ]
[HAVING "conditions]
[ORDER BY "column-list" [ASC | DESC] ]
[LIMIT [offset,] number-rows]

see extended text to see working function

For class note:
SELECT m.firstname,m.lastname,c.name
FROM music101_musician m, music101_country c
WHERE m.birth_country_id = c.id

For puffles and types:
SELECT p.name, m.type
FROM media_types m, puffles p
WHERE m.id = p.type
DATE_FORMAT(date,format)
Formats the date value according to the format string. The
following specifiers may be used in the format string:

Posted by dimitri at 03:35 PM | Comments (0)

February 07, 2005

class 03

missed class 2...

"\" escapes a command in perl. so if you want to use character like $ @ ETC...

don't escape and it will "BARF"

subroutine for perl to receive form in for from html:

################################################
# Takes data from an HTML Form or URL string
################################################
sub input_vars_receive
{ $formData = $ENV{'QUERY_STRING'};

if ($ENV{'REQUEST_METHOD'} eq 'POST')
{ read(STDIN, $formData, $ENV{'CONTENT_LENGTH'});
}

this code allows people post or use the browser line.

persistance:
rewrites to the text field. if you dont the text field will return clear...

fix text

redo code

environment tags...

MySQL
chris' class note's

relational database

Tableis made up of:
columns: fields
rows: entries

if a field set is constant one can create a new table to be called into other table.
one to many relationship Like a book one book many pages, birth place,

Many to many relationship for example an album can thought of a part of many styles, can be called a linking table.

" ' " is MySQL's escape character command

insert add
update change

"LIKE"

#

Week 3: Database Design, MySQL, and the SQL Language - Part 1

Topics:
# Introduction to Databases
# Relationship Modeling
# Using PhpMyAdmin To Create Your First Table
# MySQL Data Types
# Basic SQL Commands

Resources:
# Class #3 Notes
# PhpMyAdmin Database Tool

Weekly Assignment:
The basis of this week's assignment is to create and manipulate a table of your own creation, but also to show what you did and present it in a web page that is created by a Perl script. Thus, all SQL statements that you issue will be documented in your web page, as well as how each statement affected the data in your table. Here are more specific instructions:

* Create a table of your choosing which contains the following columns: an ID column, at least one numeric column (i.e. integer, double, or decimal), at least one string column (i.e. varchar, text, or blob), and at least one date or time column (i.e. date, time, datetime, etc). Show a visual representation of the columns of your table in your Perl-generated web page. You can use the "Print View" link on the PhpMyAdmin "Structure" page and take a snapshot of your table in PhotoShop and present it as an image, or you can cut and paste the HTML from the "Print View" link into your Perl script, or use whatever means necessary to show the structure of your table inside of your Perl-generated web page.
* Using the INSERT statement in the PhpMyAdmin SQL window, create at least 5 records.
* Inside of your Perl-generated web page, list the INSERT statements that were used to create your data set. Then show all records in your table. You can use the "Print View" link on the PhpMyAdmin "Browse" page and take a snapshot of your table in PhotoShop and present it as an image, or you can cut and paste the HTML from the "Print View" link into your Perl script, or use whatever means necessary to show the contents of your table inside of your Perl-generated web page.
* Using the SELECT statement in the PhpMyAdmin SQL window, make at three targeted queries. One should be based upon a numeric column (i.e. where id=5), one should be based upon a text query (i.e. where name='Jones') and one should be based upon a datetime column (i.e. where date>'4/1/2001'). Show each query and its respective results inside of your Perl-generated web page.
* Using the UPDATE statement in the PhpMyAdmin SQL window, change at least one of the records in your table. Show the statement and the new contents of your table inside of your Perl-generated web page.
* Using the DELETE statement in the PhpMyAdmin SQL window, remove at least one but not all of the records in your table. Show the statement and the new contents of your table inside of your Perl-generated web page.

Please try to choose your table columns and queries in some sort of coherent fashion. Think of some data in your life that you'd like to organize (address book, the weather, your CDs, etc), and how you might be able to keep track of it, and to obtain interesting results by querying it.

Recommended Reading/Reference:
# Teach Yourself MySQL in 21 Days By Anthony Butcher
Pages 42-46 (Relationship Modeling), 65-83 (Data Types), 109-112 (INSERT), 115-116 (UPDATE), 128-134 (SELECT), 151 (DELETE)

Posted by dimitri at 03:24 PM | Comments (0)

January 24, 2005

Perl error

CGI::Carp module for perl tells what the problem is in the html error message.

use CGI::Carp

Posted by dimitri at 05:15 PM | Comments (0)

notes 01

Uses homesite...

static vs dynamic in web-building

music site to look at, WholeNote

Bastardize version... because it is on the web...

-Static
-html
-Script
-perl
-php
-Scriptw/DB
-perl with DB
-php with DB

looked at Wallop

required reading:

learning perl
MySQL in 21 days

Posted by dimitri at 03:26 PM | Comments (0)

class Info

Dynamic Web Development

class info:

Course Description
How does one move away from creating static websites and toward building active, evolving hubs of activity? This class will cover the design and implementation of the "dynamic" website in two distinct but related contexts: the technical aspects of manipulating content "on the fly", and the end user experience of interacting in this type of setting. Particular attention will be given to social and community-based web interaction. The production environment will consist of the MySQL database and the Perl programming language. Students can expect to develop a firm knowledge of database design and optimization, the SQL query language, and the use of Perl to create dynamic activity of both orthodox and unorthodox nature. Late-semester topics will focus on interfacing this environment with other technologies such as JavaScript and Flash, along with data population and site architecture methodology. Introduction to Computational Media or equivalent programming experience is required. Students are also expected to have fluency in HTML or to come up to speed with it outside of class. Class requirements will include homework assignments to reinforce each week's concepts while simultaneously contributing to the student's "toolkit" of code and design principles. There will also be a midterm project, and a final project of the student's choosing. Given the wide range of applications that would benefit from a web-accessible database, students should feel free to use their project(s) from this class to support or enhance projects from other classes.

Syllabus

1. Introduction and Dynamic Website Theory Basics
2. Perl Basics and CGI: Script-Generated Pages
3. Database Design, MySQL, and the SQL Language - Part 1
4. Database Design, MySQL, and the SQL Language - Part 2
5. Perl Talks to MySQL: The Web to the Database and Back
6. Perl Tricks: The Basic and The Sexy
7. Dissection of a Dynamic Website: Friendster.com
8. Mid-Term Project Presentation
9. Dynamic Web Development on the Client-Side: JavaScript
10. More Client-Side: Interfacing with Flash
11. Dynamic Web Development and Scalability
12. Dissection of Dynamic Websites: DodgeBall, Wallop, WholeNote
13. Final Project Workshop
14. Final Project Presentation

Teacher Biography
Christopher Sung (B.S., Yale; M.P.S., NYU) is the President and CEO of eTonal Media, Inc. which owns and operates the WholeNote.com website for guitarists, the ActiveBass.com website for bassists, and ActiveMusician.com, an on-line retail musician store. eTonal's sites have garnered attention from such publications as The New York Times, Guitar Player magazine, Acoustic Guitar magazine, and Bass Player magazine. Prior to founding eTonal in 1999, he spent stints at Yale's Center for Studies in Music Technology (CSMT), the Banff School of the Arts, MIT Lincoln Laboratory, the Berklee College of Music, Microsoft Research, Interval Research, and Organic. Christopher holds a US patent in the field of music technology, and is active in the New York music scene, playing guitar with ASCAP and MAC-award winners Marcy Heisler and Zina Goldrich in the realm of musical theater, and with the singer/songwriter Talia Paul in the pop/rock sphere.

Posted by dimitri at 03:20 PM | Comments (0)