Perl's DBI module provides an interface to MySQL databases for CGI scripts. A commented example can be found below:
#!/usr/bin/perl
use DBI;
Connect To Database The DBI interface to MySQL uses the method "connect" to make a connection to the database. It takes as it's first argument the string "DBI:mysql:database:hostname", where database is equal to the name of your database, and hostname to the server that it's located on. The second and third arguments, respectively, should be your account username and password. The connection is assigned. to a variable that is used by most other methods in the module.
$database = "your database name";
$username = "your database username";
$password = "your database password";
$hostname = "your database hostname";
$db = DBI->connect("DBI:mysql:$database:$hostname", $username, $password);
Execute a Query executing a query is done in two steps. First, the query is setup using the "prepare" method. this requires the use of the variable used to initiate the connection. Second, the "execute" method is called, as shown below.
$query = $db->prepare("SELECT * FROM test");
$query->execute;
How many rows in result? the "rows" method using the variable name the query was executed under returns the number of rows in the result.
$numrows = $query->rows;
Display Results the fetchrow_array method executed on the query returns the first row as an array. subsequent calls return the other rows in sequence. It returns zero when all rows have been retrieved.
while (@array = $query->fetchrow_array) { ($field1, $field2, $field3) = @array; print "field1 = $field1, field2 = $field2, field3 = $field3 \n"; }
Cleaning Up with the DBI module, it is a good idea to clean up by explicitly ending all queries with the "finish" method, and all connections with the "disconnect" method.
$query->finish;
$db->disconnect;
exit(0);
The DBI Module provides other methods you might find useful. More information is available by running "perldoc DBI" while logged on to your account via SSH.
