MySQL is most often used with other applications to display data. For displaying data on the web, Dreamweaver, PHP, and Perl are probably the most common methods used, and Python is also useful.
The PHP, Python, and Perl examples assume that you already know how to use those programming languages.
Dreamweaver makes it fairly easy to make simple web pages using MySQL and PHP. The screenshots on this page are from Dreamweaver MX 2004.
The first thing you need to do is tell Dreamweaver that your site is a dynamic site using PHP/MySQL server technology.
Then, you’ll need to tell it where you want your “test server” to be. Normally, you’ll edit locally, then upload to a remote testing server (as I’ve shown below). In this case, however, we’ll want to edit and test locally. We set up our database on “this computer”.
You’ll also need to tell Dreamweaver how to connect to MySQL. Go to the Applications toolbar and choose the Databases tab. Click on the “+” and make a new database connection.
If this were a live database, you would have a special username and password just for use on the web, and you would put that information into the “MySQL Connection” window. In this case, we’ll use the username and password we set up for this class.
If all goes well, Dreamweaver will now be able to list the tables in your database and the fields in those tables.
Now, in the Bindings tab, we need to make a new record set.
A record set is a select statement. For simple purposes, Dreamweaver can create the select statements for us; all we have to choose are the fields we want to display and the order we want them displayed in.
If you want to take over from Dreamweaver, you can click the “Advanced...” button and see--and modify--the select statement that Dreamweaver created.
Now that we have a select statement, we need to display the resulting rows on our web page. The most obvious way of showing a bunch of rows is to make a table.
Give the table two rows, and one column for each field we’ll be displaying. Here, I assume we’ll be displaying the artist, the album, the year, and the rating.
Set up the headers and drag fields over from the “Databases” tab.
As it currently stands, if we publish this page and view it, we’ll only see the first record. We need to tell Dreamweaver to repeat the second row for every record. First, highlight the entire row. Then, choose “Repeat Region” from the Server Behaviors tab.
Specify the record set that is going to be repeated, and how many records you want to show at one time. For small result sets, you can simplify things by showing all records. In this case, we have hundreds of albums, so we’ll show twenty at a time.
You’ll know that the row is a repeat region by the gray “Repeat” tab on its upper left.
Now, Dreamweaver shows us the first twenty records. We need a way for our readers to page through the list of albums. In the Server Behaviors tab, we also have “Recordset Paging”. There is one each for first page, previous page, next page, and last page. Go ahead and add one of each.
Each time, you’ll get a simple configuration window. Make sure you choose the correct record set and that the link is what you expect it to be. You can always cancel if it isn’t right.
Once you’ve done that, you should have a page that looks something like this, with one header row and one repeating row, and a couple of simple navigation links on the bottom of the page.
On the web, this will be displayed as:
This, for example, is the last page of albums.
There is a whole lot more you can do with MySQL in Dreamweaver, such as make forms and link the forms to a database. That’s a whole other tutorial, however.
If you’re programming directly in PHP, here’s a simple example of displaying records using PHP in HTML. This PHP is for PHP 4.x and earlier, and requires the mysql module be installed. Most web providers that provide PHP will include the mysql module.
<html>
<head>
<title>My Albums</title>
</head>
<body>
<h1>My Albums</h1>
<table>
<tr><th>Album</th><th>Artist</th><th>Year</th><th>Rating</th></tr>
<?
$host = 'localhost';
$user = 'Username';
$pass = 'Password';
$database = 'music';
$query = "SELECT album, artist, year, rating FROM albums ORDER BY artist";
if ($sqlConnection = mysql_connect($host, $user, $pass)) {
if (mysql_select_db($database, $sqlConnection)) {
if ($queryResult = mysql_query($query, $sqlConnection)) {
while ($album = mysql_fetch_array($queryResult)) {
albumRow($album);
}
mysql_free_result($queryResult);
} else {
sqlProblem("Cannot perform query $query.");
}
} else {
sqlProblem("Cannot select database $database.");
}
mysql_close($sqlConnection);
} else {
sqlProblem("Problem connecting to $host.");
}
function sqlProblem($warning) {
$latestError = mysql_error();
echo "$warning ($latestError)\n";
}
function albumRow($albumInfo) {
$album = $albumInfo['album'];
$artist = $albumInfo['artist'];
$year = $albumInfo['year'];
$rating = $albumInfo['rating'];
echo "\t\t\t<tr>";
echo "<th>$album</th><td>$artist</td><td>$year</td><td>$rating</td>";
echo "</tr>\n";
}
?>
</table>
</body>
</html>
The mysql_connect() function connects your page to the database, the mysql_select_db() function selects the database you want, and the mysql_query() function performs your query. The mysql_free_result() function tells PHP that you’re finished with that query and the mysql_close() function tells PHP that you’re finished with that connection.
The important parts are mysql_error(), which tells you the last error that occurred, and mysql_fetch_array(), which gives you the next row from the database. Items in the row can be pulled out by their name.
Python requires the MySQLdb module. You can get it from the project’s web page at http://sourceforge.net/projects/mysql-python/ if you do not already have it. You can find out if you type “python” and then try to “import MySQLdb”. Python is case sensitive.
#!/usr/bin/python
import MySQLdb
class album:
def __init__(self, albumInfo):
self.album = albumInfo["album"]
self.artist = albumInfo["artist"]
self.year = albumInfo["year"]
self.rating = albumInfo["rating"]
def tableRow(self):
rowHTML = "<tr>"
for cell in [self.album, self.artist, self.year, self.rating]:
rowHTML += self.tableCell(cell)
rowHTML += "</tr>\n"
return rowHTML
def tableHeader(self):
rowHTML = "<tr>"
for header in ["Album", "Artist", "Year", "Rating"]:
rowHTML += self.tableCell(header, "th")
rowHTML += "</tr>\n"
return rowHTML
def tableCell(self, cellData, cellType="td"):
cellHTML = "<" + cellType + ">"
cellHTML += str(cellData)
cellHTML += "</" + cellType + ">"
return cellHTML
class albums:
def __init__(self, user, password, host="localhost"):
self.db = 'music'
self.query = 'SELECT album, artist, year, rating FROM albums ORDER BY artist'
self.sql = MySQLdb.connect(host=host, user=user, passwd=password, db=self.db)
self.cursor = self.sql.cursor(MySQLdb.cursors.DictCursor)
self.nextAlbum = None
def __destroy__(self):
if self.sql:
self.cursor.close()
self.sql.close()
def table(self):
if self.sql:
albums = None
try:
self.cursor.execute(self.query)
albums = self.cursor.fetchall()
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
if albums:
tableRows = [album.tableHeaders()]
for albumInfo in albums:
currentAlbum = album(albumInfo)
tableRows.append(currentAlbum.tableRow())
table = "<table>\n"
table += "\n".join(tableRows)
table += "</table>\n"
else:
table = "<p>Unable to acquire albums</p>\n"
else:
table = "<p>Unable to connect to database " + self.db + ".</p>\n";
return table
def page(self):
page = "<html>\n"
page += "<head><title>My Albums</title></head>\n"
page += "<body>\n"
page += "<h1>My Albums</h1>\n"
page += self.table()
page += "</body>\n"
page += "</html>\n"
return page
albumList = albums('Username', 'Password')
print albumList.page()
Perl requires the DBI database interface to connect to MySQL. You can get it from CPAN:
perl -MCPAN -e shell
install dbi
install DBD::mysql
This will get you the DBI interface and the MySQL information it needs to work.
#!/usr/bin/perl
use DBI;
$driver = "mysql";
$host = "localhost";
$port = 3306;
$user = "Username";
$pass = "Password";
$database = "music";
$query = "SELECT album, artist, year, rating FROM albums ORDER BY artist";
$dataSource = "DBI:$driver:database=$database;host=$host;port=$port";
print "<html>\n";
print "<head><title>My Albums</title></head>\n";
print "<body>\n";
print "<h1>My Albums</h1>\n";
print "<table>\n";
print "<tr><th>Album</th><th>Artist</th><th>Year</th><th>Rating</th></tr>\n";
if ($databaseHandle = DBI->connect($dataSource, $user, $pass)) {
if ($queryHandle = $databaseHandle->prepare($query)) {
if ($queryHandle->execute) {
while ($rowHandle = $queryHandle->fetchrow_hashref()) {
$album = $$rowHandle{'album'};
$artist = $$rowHandle{'artist'};
$year = $$rowHandle{'year'};
$rating = $$rowHandle{'rating'};
print "<tr>";
print "<th>$album</th><td>$artist</td><td>$year</td><td>$rating</td>";
print "</tr>\n";
}
} else {
print "Unable to execute query $query: $!\n";
}
$queryHandle->finish;
} else {
print "Unable to prepare query $query: $!\n";
}
$databaseHandle->disconnect;
} else {
print "Unable to connect to data souce $dataSource: $!\n";
}
print "</table>\n";
print "</body>\n";
print "</html>\n";
The DBI interface is object-oriented, and returns handles to various objects or arrays. In Perl, queries must be “prepared” before they are executed. The fetchrow_hashref() method is the equivalent of PHP’s mysql_fetch_array() function. It returns a handle to an associative array. You need to dereference the handle using double dollar signs.