Perl Connect MySQL Database
http://www.tizag.com/ 30/0/2552
Table of Contents perl ‐ mysql module ............................................................................................................................ 1 perl ‐ mysql config .............................................................................................................................. 1 perlmysqlconfig.pl: .......................................................................................................................... 1 perl ‐ mysql connect ........................................................................................................................... 1 perlmysqlconnect.pl: ...................................................................................................................... 2 perl ‐ mysql listdbs() .......................................................................................................................... 2 listdbs.pl: ......................................................................................................................................... 2 listdbs2.pl: ....................................................................................................................................... 2 perl ‐ select database .......................................................................................................................... 3 perlmysqlselectdb.pl: ..................................................................................................................... 3 perl ‐ list tables function .................................................................................................................... 3 listtables.pl: ..................................................................................................................................... 3 perl ‐ mysql query .............................................................................................................................. 4 perlmysqlquery.pl: ......................................................................................................................... 4 perl ‐ mysql insert query .................................................................................................................... 4 perlinsertquery.pl: ............................................................................................................................ 4 perl ‐ mysql select query .................................................................................................................... 5 easyselectfunctions.pl: ................................................................................................................... 5 perl ‐ mysql fetchrow()....................................................................................................................... 6 fetchrow.pl: ..................................................................................................................................... 6
Perl Connect MySQL Database perl ‐ mysql module MySQL queries and the like can be executed with PERL via the MySQLModule. This module should already be installed with your web server if not contact your web host. As a quick overview, this module installs the necessary functions required to execute MySQL queries using a PERL script. Please take note that this moduleonly works with the MySQL platform. Other SQL platforms will require the use of the DBI module discussed in our PERL DBI Module lesson.
perl ‐ mysql config Before we dive head first into the functions, we may want to set up some config variables that we will be calling upon in each script to first connect to our database. Have the following information easily accessible. • • • • •
Our Web Host's data source name (DSN) User Name for the MySQL Database Password for the MySQL Database Name of Database Name of Table(s)
perlmysqlconfig.pl: #!/usr/bin/perl # PERL MODULE WE WILL BE USING use Mysql; # MySQL CONFIG VARIABLES $host = "localhost"; $database = "store"; $tablename = "inventory"; $user = "username"; $pw = "password";
A config set‐up like this simplifies our connection script and the queries that will be executed later.
perl ‐ mysql connect The MySQL module works only with the MySQL platform. We can maintain the same variables from the previous example to connect to MySQL.
Perl Connect MySQL Database
หน้า 1
perlmysqlconnect.pl: #!/usr/bin/perl # PERL MODULE use Mysql; # HTTP HEADER print "Content-type: text/html \n\n"; # CONFIG VARIABLES $host = "localhost"; $database = "store"; $tablename = "inventory"; $user = "username"; $pw = "password"; # PERL MYSQL CONNECT $connect = Mysql->connect($host, $database, $user, $pw);
If this script was run on your web server through a web browser, you should be starring at a blank white screen and all is well.
perl ‐ mysql listdbs() Once PERL has established a connection we can execute any of the built in module functions. A great introductory function is the listdbs function. This function reads from the MySQL platform and places the name of each database into an array.
listdbs.pl: @databases = $connect->listdbs;
We can then loop through this array and print out our results to the browser.
listdbs2.pl: #!/usr/bin/perl # PERL MODULES use Mysql; # MYSQL CONFIG VARIABLES $host = "localhost"; $database = "store"; $tablename = "inventory"; $user = "username"; $pw = "password"; # PERL CONNECT() $connect = Mysql->connect($host, $database, $user, $pw); # LISTDBS() @databases = $connect->listdbs; foreach $database (@databases) { print "$database
";
Perl Connect MySQL Database
หน้า 2
}
perl ‐ select database In order to perform even the simplest of queries we must first select a database to be working with. Since we have our database name already listed with our config variables, things will be quite simple.
perlmysqlselectdb.pl: #!/usr/bin/perl # PERL MODULE use Mysql; # MYSQL CONFIG VARIABLES $host = "localhost"; $database = "store"; $tablename = "inventory"; $user = "username"; $pw = "password"; # PERL CONNECT() $connect = Mysql->connect($host, $database, $user, $pw); # SELECT DB $connect->selectdb($database);
Notice how the syntax requires that we connect to our host each time we perform a function. You will see this with nearly every script we execute. Once we are connected, the sky is the limit as to what queries we can execute.
perl ‐ list tables function A function exists to list the tables in a database just like the listdbs() function. Use the listtables() function to list each table in a database.
listtables.pl: #!/usr/bin/perl use Mysql; # HTTP HEADER print "Content-type: text/html \n\n"; # MYSQL CONFIG VARIABLES $host = "localhost"; $database = "store"; $tablename = "inventory"; $user = "username"; $pw = "password"; # PERL MYSQL CONNECT() $connect = Mysql->connect($host, $database, $user, $pw);
Perl Connect MySQL Database
หน้า 3
# SELECT DB $connect->selectdb($database); # LISTTABLES() @tables = $db->listtables; # PRINT EACH TABLE NAME @tables = $connect->listtables; foreach $table (@tables) { print "$table
"; }
The database is defined when we run the $connect variable. To change the script to a different database simply run a new selectdb() function or change the $database variable.
perl ‐ mysql query Executing a query using the MySQL module is a two step process ‐ very straight forward. We define a query in the form of a scalar variable then call upon that variable using our connection script and the query function.
perlmysqlquery.pl: # DEFINE A MySQL QUERY $myquery = "INSERT INTO $tablename (id, product, quantity) VALUES (DEFAULT,'pineapples','15')"; # EXECUTE THE QUERY FUNCTION $execute = $connect->query($myquery);
perl ‐ mysql insert query Here we introduce the affectedrow() function along with the insertid() function. You can probably guess what the affected rows function does but insertid is unique. Inserid() returns the 'id' of the last inserted row, that is it will return an id if you have an id field set up to auto‐ increment in your MySQL table.
perlinsertquery.pl: #!/usr/bin/perl use Mysql; print "Content-type: text/html \n\n"; # MYSQL CONFIG VARIABLES $host = "localhost"; $database = "store"; $tablename = "inventory"; $user = "username"; $pw = "password"; # PERL MYSQL CONNECT() $connect = Mysql->connect($host, $database, $user, $pw);
Perl Connect MySQL Database
หน้า 4
# SELECT DB $connect->selectdb($database); # DEFINE A MySQL QUERY $myquery = "INSERT INTO $tablename (id, product, quantity) VALUES (DEFAULT,'pineapples','15')"; # EXECUTE THE QUERY FUNCTION $execute = $connect->query($myquery); # AFFECTED ROWS $affectedrows = $execute->affectedrows($myquery); # ID OF LAST INSERT $lastid = $execute->insertid($myquery); print $affectedrows."
"; print $lastid."
";
These functions could be run without defining them as scalar variables as well.
perl ‐ mysql select query Queries that use the SELECT clause are a little more exciting. Here we introduce two new functions, the numrows() function and the numbfields() function. Both of these do exactly as they say, one fetches the number of rows returned with as the query executes while the other fetches the number of fields returned.
easyselectfunctions.pl: #!/usr/bin/perl use Mysql; # HTTP HEADER print "Content-type: text/html \n\n"; # MYSQL CONFIG VARIABLES $host = "localhost"; $database = "store"; $tablename = "inventory"; $user = "username"; $pw = "password"; # PERL MYSQL CONNECT() $connect = Mysql->connect($host, $database, $user, $pw); # SELECT DB $connect->selectdb($database); # DEFINE A MySQL QUERY $myquery = "SELECT * FROM $tablename"; # EXECUTE THE QUERY $execute = $connect->query($myquery); $rownumber = $execute->numrows(); $fieldnumber = $execute->numfields(); # PRINT THE RESULTS
Perl Connect MySQL Database
หน้า 5
print $rownumber."
"; print $fieldnumber."
";
Two numbers should be printed to your web browser.
perl ‐ mysql fetchrow() The fetchrow() function does exactly as it says it does, it goes out and fetches a row that matches your MySQL Query. An array is returned and each element represents a column value for the fetched row. If the query is intended to return multiple rows, fetchrow() must be called again and again. This is easily accomplished with a while loop.
fetchrow.pl: #!/usr/bin/perl use Mysql; print "Content-type: text/html \n\n"; # MYSQL CONFIG VARIABLES $host = "localhost"; $database = "store"; $tablename = "inventory"; $user = "username"; $pw = "password"; # PERL MYSQL CONNECT() $connect = Mysql->connect($host, $database, $user, $pw); # SELECT DB $connect->selectdb($database); # DEFINE A MySQL QUERY $myquery = "SELECT * FROM $tablename"; # EXECUTE THE QUERY FUNCTION $execute = $connect->query($myquery); # HTML TABLE print "
id | product | quantity |
"; # FETCHROW ARRAY while (@results = $execute->fetchrow()) { print "" .$results[0]." | " .$results[1]." | " .$results[2]." |
"; } print "
";
Perl Connect MySQL Database
หน้า 6
Perl Connect MySQL Database
หน้า 7