Knowledgebase

[CGI2.04] How do I connect to my MySQL database from a Perl CGI script?

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.

Also Read