[Perl] Beginning Perl Tutorial - Installing and Using MySQL

Now that we have a database to connect to, let's look at how we access the information from our Perl programs. The following is a simple script that connects to the database and runs a short bit of SQL to retrieve all the samples.

 #!/usr/bin/perl -w
 use DBI;
 $dbh = DBI->connect('dbi:mysql:perltest','root','password')
 or die "Connection Error: $DBI::errstr\n";
 $sql = "select * from samples";
 $sth = $dbh->prepare($sql);
 $sth->execute
 or die "SQL Error: $DBI::errstr\n";
 while (@row = $sth->fetchrow_array) {
 print "@row\n";
 } 

The first thing we need to do is let the Perl interpreter know that we intend to use the Perl DBI function library, by including it in our program with the use function.

 use DBI;
 

Next we'll need to create a connection to the database itself. For this part of the script, your perltest database will need to be running.

 $dbh = DBI->connect('dbi:mysql:perltest','root','password')
 or die "Connection Error: $DBI::errstr\n";
 

The connect function contains the name of the database, and your username and password used to connect. This might be different depending on your setup.

 $dbh = DBI->connect('dbi:mysql:DATABASE_NAME', USERNAME, PASSWORD)
 

The die option provides an alternative to the program simply not working if a connection is not established. Basically, the connect it tried, and if it fails, your script will die and display an error message that should help you debug. Once we've established a connection to the MySQL database, we will need to create a string of SQL and then prepare it to query the database.

 $sql = "select * from samples";
 $sth = $dbh->prepare($sql);
 

Next we query the database with our prepared SQL query, or exit the program and display some debugging information if the MySQL query fails to execute.

 $sth->execute
 or die "SQL Error: $DBI::errstr\n";
 

Finally we use the fetchrow_array function to fetch each row of the results from the MySQL database and print them one to a line.

 while (@row = $sth->fetchrow_array) {
 print "@row\n";
 } 

If the program is successful, you should see the following output:

 1 Some Person 555-5555
 2 Another Person 222-2222 

Learning MySQL is beyond the scope of this tutorial. If you're looking for more information, I'd suggest going through some of the tutorials from About's guide to PHP / MySQL, Angela Bradley in the Learn MySQL section. The tutorial on Understanding MySQL is a good spot for a beginner to start.
When you are able to do some basic administration tasks, let's create a database called perltest and in that database, we will create a simple table called samples and populate it with some data. Here is the SQL you'll need to create the table and fill in a few records, just connect to your MySQL database and run them.
 CREATE DATABASE perltest;
 USE perltest;
 CREATE TABLE samples (
 id int(10) unsigned NOT NULL auto_increment,
 name varchar(128) NOT NULL default '',
 phone varchar(128) NOT NULL default '',
 PRIMARY KEY (id)
 );
 INSERT INTO samples VALUES (1, 'Some Person', '555-5555');
 INSERT INTO samples VALUES (2, 'Another Person', '222-2222');