Database Reference
In-Depth Information
The first line here sets up a variable,
$search_parameter
, to store a value from
shift
, which loads into that variable the value given by the user when executing the
program. On the next line of code, we create the
$sql_stmnt
variable to store the SQL
statement. Notice that where we would specify the last name of the member in the
WHERE
clause, we entered instead a question mark. This is known as a
placeholder
. We willre-
place the placeholder with
$search_parameter
two lines later. Placeholders are a
good security precaution. For more information on this, see
SQL Injection
.
After creating the
$sql_stmnt
variable, we usethe
prepare()
function of the data-
base handle in order to prepare the SQL statement to form an SQL statement handle
(
$sth
). Then we usethe
execute()
function to execute the statement handle, with the
$search_parameter
to replace the placeholder. To replace multiple placeholders,
you would list them in a comma-separated list within the parentheses of
execute()
.
Having connected to MySQL and invoked an SQL statement, what remains is to capture
the data results and to display them to the administrator.The
fetchrow_array()
function can be used to fetch the data one row at a time. We'll use that with a
while
statement. Here's how that would look:
...
while
(
my
($human_id,$full_name,$membership_expiration) =
$sth->
fetchrow_array
())
{
print
"$full_name ($human_id) - $membership_expiration \n"
;
}
$sth->
finish
();
$dbh->
disconnect
();
The
while
statement executes its block of code repeatedly so long as there are rows to
process. The value of each element of each array (i.e., each row) is stored in the two vari-
ables
$common_name
and
$scientific_name
— and overwritten by each loop of
while
. Then the variables are printed to the screen with a newline character after each
pair.
The second to last lineuses
finish()
to end the SQL statement handle. The last line
disconnects the databasehandle with
disconnect()
. Alternatively, you can leave open
the connection to MySQL so that you can create and execute more statement handles to
interface with MySQL.
A better method of retrieving data from MySQL perhaps would be to capture all of the
data in memory for later use in the Perl program, thus allowing the connection to MySQL
