Dynamic Graphics

Generating graphics, charts and graphs for your web site is easy following Mr. Lerner's instructions.
Retrieving Data from a Database

The above example introduced us to the notion of creating a chart based on data stored on disk. While this is certainly the right idea, storing such data in a text file has its drawbacks. It is more common and more useful to put such data in a relational database.

Creating a chart based on a table in a relational database is not very different from creating one based on a text file. The main difference is with the loop we use to iterate over our input data. In vote.pl, we iterated over each line of votes.txt, turning each line of text into a name,value pair, which we then added to @data. When we retrieve information from a database, the information is already split into name,value pairs for us.

Before we can begin to write db-vote.pl (a database version of vote.pl), we must create a table in our database. As usual, I will use MySQL, a “mostly free” database described in Resources. MySQL's syntax is standard enough for most purposes, and most of the following should work with other databases as well.

Relational databases expect to receive input in SQL, the “structured query language”. SQL is not a programming language—so while we can create all sorts of queries to manipulate data in our table, we must embed those queries within a program written in a full programming language. Perl's DBI (“database interface”) module allows us to embed SQL statements inside our Perl programs.

We can create a new table by issuing the following SQL command:

CREATE TABLE Votes (
         candidate_name VARCHAR(30),
         votes_received BIGINT UNSIGNED
         );

While we could send the above to our database server from within a Perl program, it is more usual to type it directly from within an interactive database client. MySQL comes with an interactive client called mysql which allows you to send queries to the database (and receive responses) without having to embed your statements inside a Perl program.

After you issue the above SQL query, the database server will create a new table, Votes, with two columns. The first column, candidate_name, allows for up to 30 characters. The second column is defined to be a BIGINT UNSIGNED, that is, a large integer. We name this column votes_received.

We will now take a leap of faith and assume that, after the polls close on election night, our database table will magically be filled with appropriate values for each candidate. (In a real application, we would probably design things differently, storing each candidate's name in a second table and perhaps even storing each vote in its own row. We will ignore real-world concerns for the time being, so as to concentrate on how to create a graph with this data.)

Assuming our table has been populated with a list of candidates' names and their votes, how can we rewrite vote.pl so it takes its input from a database? As mentioned above, we will rely on DBI, Perl's database interface, which provides a uniform, object-oriented interface to most popular relational databases. Each database is described in a DBD, or database driver, and is imported automatically when we open a connection.

Opening a connection to the database creates a “database handle” object, traditionally called $dbh. We use this object to create a “statement handle”, traditionally called $sth, with which we send the SQL to the database server. Our query, in this case, is rather simple:

SELECT candidate_name, votes_received
    FROM Votes

When it executes this query, the database server will return a two-column table to the user—in this particular case, the entire contents of the Votes table. Each row of the table corresponds to a line in the text file votes.txt which we saw earlier.

DBI provides us with a number of methods by which to retrieve data from $sth. The most commonly used methods retrieve a row as an array, either in its usual form (using $sth->fetchrow_array) or as a reference (using $sth->fetchrow_arrayref). While the arrayref method is more efficient, beginning Perl programmers often prefer to avoid references, which sometimes confuse them. In both cases, the order of elements in the returned list is determined by the order in which columns were named in the query.

Listing 4.

The rest of db-vote.pl (see Listing 4) continues in almost the same way as vote.pl, pushing the values in each row onto @names and @values, then using those to create @data.

It is generally preferable to put such information in a database, because of the reliability and flexibility offered by relational databases. Remember, though, there is no free lunch: a relational database is inherently much slower than a flat ASCII text file. Moreover, our CGI program opens a connection to the database each time it is invoked, an expensive and time-consuming operation. For these reasons, vote.pl will almost certainly execute faster than db-vote.pl. Whether this is an appropriate trade-off depends on the number of visitors to your site, as well as the nature of your web applications.

______________________

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState