<%@ page import="java.io.*, java.lang.*, java.sql.*" %>
<%!
// Declarations
String catID, categoryName;
StringBuffer sites, categories;
%>
<%
Connection con;
Statement s;
ResultSet rs;
String line, desc;
try {
// Establish database connection
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(
"jdbc:mysql://localhost/phos",
"cklutzke", "periadon");
s = con.createStatement();
// Determine category
catID = request.getParameter("catID");
if (catID == null) {
catID = "0";
}
// Get the category name from the database.
rs = s.executeQuery("SELECT c.name FROM category AS c " +
"WHERE c.categoryId = '" + catID + "'");
while (rs.next()) {
categoryName = rs.getString("c.name");
}
rs.close();
// Get the category list from the database.
categories = new StringBuffer("Categories
");
rs = s.executeQuery("SELECT c.name, c.categoryId FROM category AS c " +
"LEFT JOIN linkCategoryCategory AS lcc ON c.categoryId = lcc.childId " +
"WHERE lcc.parentId = '" + catID + "' ORDER BY c.name");
while (rs.next()) {
line = "" + rs.getString("c.name") + "";
categories.append("- " + line + "
");
}
rs.close();
categories.append("
");
// Get the site list from the database.
sites = new StringBuffer("Sites
");
rs = s.executeQuery("SELECT s.name, s.url, s.description FROM site AS s " +
"LEFT JOIN linkSiteCategory AS lsc ON s.siteId = lsc.siteId " +
"WHERE lsc.categoryId = '" + catID + "' ORDER BY s.name");
while (rs.next()) {
line = "" + rs.getString("s.name") + "";
desc = rs.getString("s.description");
if (rs.wasNull()) {
sites.append("- " + line + "
");
} else {
sites.append("- " + line + ": " + desc + "
");
}
}
rs.close();
sites.append("
");
s.close();
con.close();
}
catch (ClassNotFoundException e1) {
// JDBC driver class not found, print error message to the console
out.println(e1.toString());
}
catch (SQLException e2) {
// Exception when executing java.sql related commands, print error message to the console
out.println(e2.toString());
}
catch (Exception e3) {
// other unexpected exception, print error message to the console
out.println(e3.toString());
}
%>
Carl's Link Database: <%= categoryName %>
<%= categoryName %>
LinkDb
> [todo: link chain goes here]
<%= categories %>
<%= sites %>