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);

# 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."<br />";
print $lastid."<br />";

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
print $rownumber."<br />";
print $fieldnumber."<br />";

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 "<table border='1'><tr>
<th>id</th>
<th>product</th>
<th>quantity</th></tr>";

# FETCHROW ARRAY

while (@results = $execute->fetchrow()) {
 print "<tr><td>"
 .$results[0]."</td><td>"
 .$results[1]."</td><td>"
 .$results[2]."</td></tr>";
}

print "</table>";

Posted in: PERL