Perl While Loop Example.

If your SQL query will return more than one record, you will need to place the fetchrow() call in a while loop.

my (@telephone);
my $i = 0;
my $count;
while (@aRow = $sth->fetchrow())
{
$telephone[$i] = @aRow[0];
$i++;
}
$count = $i;

# @telephone can now be used to build an html table
# to display all the telephone numbers in the "518"
# area code.

Using MySQL with CGI scripts.

Using MySQL with Common Gateway Interface scripts will allow you to develop more interactive web sites. Examples of using CGI scripts with MySQL are searchable catalogs, user account management, inventory tracking, and information management. Any time you have even small quantities of data which are similar and/or which will change over time, a database solution will likely be useful.

CGI scripting does require programming experience. If you are not familiar with CGI scripting, it is suggested that you begin with the basics of forms and non database applications. There are many books available to teach you CGI programming in a number or languages. Here we will be focusing on how to program MySQL using Perl as the CGI scripting language.

A Quick Review of How CGI Works

Normally clicking on a link in a web browser causes the web server to return a static .html page. No matter who clicks on this link or how many times they do it, the resulting returned web page is always the same. To change a static .html page the site's webmaster must edit the contents of the .html file.

On the other hand, a CGI script allows a link or a button in a web page to run a program on the web server. This program can do any number of things from getting the current date and time to performing a complex lookup and update in a database. In either case, the results are not the same everytime the link or button is pressed.

The process occurs something like this:

User clicks on a link in a web page (e.g. http://www.cgitest.com/cgi-bin/test.cgi).
The web server runs the program test.cgi.
The test.cgi program does what it is programmed to do.
The test.cgi program also builds a .html file in memory and sends it back to the user's browser.
It is the last two steps which make CGI scripts so useful. The program can perform what ever operations it needs to and it can then generate a .html page based on the results of these operations. When the CGI script is used with a database such as MySQL, many things are possible. Generally, the page returned to the user's browser contains the results of the database search. Or, if the user had provided information through a form in the web page, the database records were updated.

Using Perl to Access a MySQL Database

The programming language Perl can be used to access a MySQL database. It is the language we will use for our examples. Access to MySQL using Perl requires the Perl DBI module. Both Perl and the DBI module are installed and available to use through your web site account.

The following code example sets up a connection the database to the www.yourwebsite.com database, prepares and executes an SQL statement, stores the result in a local variable, and then cleans up the connection.

# Use the DBI module
use DBI qw(:sql_types);

# Declare local variables

my ($databaseName, $databaseUser, $databasePw, $dbh);
my ($stmt, sth, @newRow);
my ($telephone);
# Set the parameter values for the connection
$databaseName = "DBI:mysql:yourWebSite_com";
$databaseUser = "yourLoginId";
$databasePw = "yourLoginPassword";

# Connect to the database
# Note this connection can be used to
# execute more than one statement
# on any number of tables in the database

$dbh = DBI->connect($databaseName, $databaseUser,
$databasePw) || die "Connect failed: $DBI::errstr\n";

# Create the statement.
$stmt = "SELECT Name FROM Phonebook
WHERE (Telephone LIKE '518%')";

# Prepare and execute the SQL query
$sth = $$dbh->prepare($$stmt)
|| die "prepare: $$stmt: $DBI::errstr";
$sth->execute || die "execute: $$stmt: $DBI::errstr";

# Get the first record
# If more than one record will be returned put
# the fetchrow in a while loop
@record = $sth->fetchrow()

# Get the value of the first field returned.
$telephone = $record[0];

# Clean up the record set and the database connection
$sth->finish();
$dbh->disconnect();

All queries follow the same basic formula. Simply replace the SELECT statement with the INSERT, UPDATE, DELETE, etc. statement you wish to use. Note that these other queries do not return records. So, the fetchrow() and assignment which follows should be deleted for then.

Many other operations such as joins, subqueries, grouping, and sorting are all supported by providing a proper SQL statement in place of the one above.

How do I work with a MySQL database using PHP?

1.To merely display the information in your database without the use of a form to call a php script you simply create your HTML document as you would any other web page but instead of the extension of .htm or .html you need to name the file with the extension .phtml. Then within the document itself the section that you'd like to be the PHP code, you begin it with <? and end it with ?>. For instance:

<P>These are the products I sell:</P>

<TABLE BORDER="1">

<?
mysql_connect("localhost", username, password);
$result = mysql(mydatabase, "select * from products");
$num = mysql_numrows($result);
$i = 0;

while($i < $num) {
echo "<TR>n";
echo "<TD>n";
echo mysql_result($result,$i,"prodid");
echo "</TD>n<TD>";
echo mysql_result($result,$i,"name");
echo "</TD>n<TD>";
echo mysql_result($result,$i,"price");
echo "</TD>n";
echo "</TR>n";
$i++;}
?>
</TABLE>

Thus having the loop in the php program create a table with the products listed. NOTE your username and password for the database are not written in the file when it's displayed on the Internet so users viewing the source of your webpage will not see your password.

2.When using a CGI script to pull information from a form which has been submitted by a browser you must have the first line of the script have this command on it (Much like perl scripts):

#!/usr/local/bin/php

To run/lookat mysql:
/usr/local/mysql/bin/mysql mysql -p$rootpass
[examples:]
mysql> show tables;
mysql> select * from user;
mysql> select * from db;
mysql> show columns from db;
mysql> select User, Password from user;
mysql> exit
[etc.]

References and Tutorials.

Books

MySQL and mSQL
Randy Jay Yarge, George Reese, and Tim King
O'Reilly & Associates
ISBN 1565924347

The Practical SQL Handbook: Using Structured Query Language
Judith S. Bowman, Sandra L. Emerson and Marcy Darnovsky
Addison-Wesley
ISBN 0201626233

Understanding SQL
Martin Gruber
Sybex
ISBN 0895886448

Teach Yourself Sql in 21 Days
Ryan K. Stephens (Editor), Ronald R. Plew, Bryan Morgan, jeff Perkins
Sams Publishing
ISBN 0672311100

Be sure to check for the most current edition.

Web Sites

The MySQL site has an SQL reference and lots of information about MySQL in particular.

An SQL tutorial is available on the net at http://www.geocities.com/SiliconValley/Vista/2207/sql1.html

Newgroups

There are various newsgroups under the comp.database group which deal with databases. Always a good place to start.

Mailing Lists

The MySQL site lists in their documentation page a number of mailing lists concerning MySQL and SQL

*Please note that we do not provide technical or development support for MySQL applications.