northwind

<%--
    Document   : query
    Created on : Mar 19, 2016, 6:43:52 PM
    Author     : hisham
--%>

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

  <%
    String Strcategory = request.getParameter("txtCategory");
    if (Strcategory != null) {
  %>
  <%@ page import = "java.sql.*" %>
  <%
      Connection conn = DriverManager.getConnection(
          "jdbc:mysql://localhost:3306/northwind", "root", ""); // <== Check!
      // Connection conn =
      //    DriverManager.getConnection("jdbc:odbc:eshopODBC");  // Access
      Statement stmt = conn.createStatement();

      String sqlStr = "SELECT * FROM categories WHERE CategoryName Like ";
      sqlStr += "'%" + Strcategory + "%'";  // First author
      ResultSet rset = stmt.executeQuery(sqlStr);
      int category = 0;
      while (rset.next())
      {
      category = rset.getInt("CategoryID");
     Strcategory = rset.getString("CategoryName");
      }
      //sqlStr += " AND qty > 0 ORDER BY author ASC, title ASC";

      String sqlStr2 = "SELECT ProductId, ProductName, FROM products WHERE CategoryID=";
      sqlStr2 += "" + category + "";  // First author

      // for debugging
      System.out.println("Query statement is " + sqlStr2);
      ResultSet rsetProduct = stmt.executeQuery(sqlStr2);
  %>
      <hr>
      <p>search Product for category :<%= Strcategory %></p>
      <form method="get" action="order.jsp">
        <table border=1 cellpadding=5>
          <tr>
            <th>Category</th>
            <th>Category</th>

          </tr>
  <%
      while (rset.next()) {
        int id = rset.getInt("ProductID");
  %>
          <tr>
            <td><input type="checkbox" name="id" value="<%= id %>"></td>
            <td><%= rset.getString("ProductID") %></td>
            <td><%= rset.getString("ProductName") %></td>

          </tr>
  <%
      }
  %>
        </table>
        <br>
        <input type="submit" value="Order">
        <input type="reset" value="Clear">
      </form>
      <a href="<%= request.getRequestURI() %>"><h3>Back</h3></a>
  <%
      rset.close();
      stmt.close();
      conn.close();
    }
  %>
</body>
</html>