searchproduct

<%--
    Document   : query
    Created on : Apr 03, 2016, 10:27:52 AM
    Author     : mazlan
--%>

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
  <title>NorthWind Search Product by Category</title>
</head>
<body>
  <h1>Find Product</h1>
  <h3>Enter Category :</h3>
  <form method="get">
    <input type="text" name="cateName" value="" />
    <input type="submit" value="Search">
  </form>

  <%!
    //String[] lName = request.getParameterValues("lastName");
    String cateName = "";
  %>
  <%
      cateName = request.getParameter("cateName");
    if (cateName != null) {
  %>
  <%@ page import = "java.sql.*" %>
  <%
      Connection conn = DriverManager.getConnection(
          "jdbc:mysql://localhost:3306/northwind", "user", "1234"); // <== Check!

      // Connection conn =
      //    DriverManager.getConnection("jdbc:odbc:eshopODBC");  // Access
      Statement stmt = conn.createStatement();
      //a.ProductID,a.ProductName,b.CategoryName
      String sqlStr = "SELECT * FROM products a, categories b WHERE  b.CategoryName like '%"+ cateName + "%' AND b.CategoryID=a.CategoryID ORDER BY ProductName ASC";

      // for debugging
      System.out.println("Query statement is " + sqlStr);
      ResultSet rset = stmt.executeQuery(sqlStr);
  %>
  Query statement is <%= sqlStr %><br/>
    <h3>List of Product under category: <%= cateName %></h3>
      <hr>
        <table border=1 cellpadding=5>
          <tr>
            <th>Product ID</th>
            <th>Product Name</th>
            <th>Category</th>
          </tr>
  <%
      while (rset.next()) {
        int id = rset.getInt("ProductID");
  %>
          <tr>
            <td><%= rset.getInt("ProductID") %></td>
            <td><%= rset.getString("ProductName") %></td>
            <td><%= rset.getString("CategoryName") %></td>
          </tr>
  <%
      }
  %>
        </table>
        <br>
  <%
      rset.close();
      stmt.close();
      conn.close();
    }
  %>
</body>
</html>