Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

60 $my $sth = $dbh->prepare("SELECT * FROM Customers WHERE State = "KY");

70 $sth->execute();

80 while(my $ref = $sth->fetchrow_hashref()){

90 print "$ref->{'First_Name'} $ref->{'Last_Name'}";

}

100 $sth->finish();

110 $dbh->disconnect;

120 exit;

Now examine the code down line by line. You've seen lines 10–50 before. These lines just enable the

script to run using the DBI module, set up the connection string, and then connect to the database. Line

60 is where it starts to get a little interesting. Here you set a variable $sth equal to the results of the

prepare() method of the database handle. The prepare() statement parses the SQL statement and

makes sure it is correct. Then a statement handle is created. The SQL statement is held until the

execute() method is called. This is done on line 70 of the code.

When the execute()method is called, the query is actually passed to the database and a result set is

returned. All the information regarding this result set is contained within the statement handle, such as

the number of rows it contains and information about the data itself.

After the execute()statement, you use one of the statement handle's methods to work with the data.

Using a while loop in line 80, you walk through every row of the data, printing the data that is

contained in the column mentioned in the print statement (line 90). The data is retrieved in the same

way it is stored in the database.

Line 100 destroys the statement handle object, freeing up the resources. The next line destroys the

database handle, closing the connection. The script ends with the exit statement.

In this example, you saw how you could connect to a database, issue a query, and manipulate the

results returned from the database. Remember that every time you interface with a database you will

follow these same steps:

1. Build a connection string and use it to connect to a database. With Perl, this is
   done using the DBI object.
   $dbh = DBI->connect(DBI:mysql:database=Meet_A_Geek, "root", "tacobell");


2. After you establish a connection, use the database handle ($dbh) to issue
   queries. For queries that do not return a result set, use the do() method. For
   queries that will return a result set, use the prepare() statement.
   $dbh->do("DELETE FROM Customers WHERE State = 'KY'");


3. If you need a result set, use the prepare() statement.
   $sth = $dbh->prepare("SELECT * FROM Customers");


4. Before you can use the results of the query, you need to run the query on the
   database. To do this, use the statement handle's ($sth) execute() method.
   $sth->execute();


5. After you have executed the query, you can manipulate the result set by using the
   fetchrow_hashref() method to return a row's worth of data. You will need to
   use some sort of looping statement to retrieve all the data in the result set.


6. While($ref = $sth->fetchrow_hashref()){