0

I am trying to make a vehicle advertising website like autotrader or ebay. I am using html, css, jsp, java and mysql for the database.Basically I want the user to be able to search for a vehicle (advert) via a form, then they are directed to a results page after processing by a Servlet. ON the "results_page" they can then choose which "advert" to click on. Basically am having trouble with getting a result (advert on result_page) to fetch for more details regarding it (e.g make, model etc) and displaying it on a separate (advert_page). The website is using html5lightbox to view images and video, and they are stored locally in a folder so only the path is stored in the DB.

Table structure for table `advert`
--

CREATE TABLE IF NOT EXISTS `advert` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `title` text COLLATE latin1_bin NOT NULL,
  `dealer_name` int(15) NOT NULL,
  `make` text COLLATE latin1_bin NOT NULL,
  `model` text COLLATE latin1_bin NOT NULL,
  `year` year(4) NOT NULL,
  `price` text COLLATE latin1_bin NOT NULL,
  `condition` text COLLATE latin1_bin NOT NULL,
  `image1` blob NOT NULL,
  `image2` blob NOT NULL,
  `image3` blob NOT NULL,
  `image4` blob NOT NULL,
  `video1` blob NOT NULL,
  `phone_number` text COLLATE latin1_bin NOT NULL,
  `type` text CHARACTER SET cp1250 COLLATE cp1250_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

**ResultsServlet.java**

Statement st;
        try {
            Class.forName(driver).newInstance();
            conn = DriverManager.getConnection(url + dbName, userName, password);
            System.out.println("Connected!");
            String q = request.getParameter("q"); //search entry from user via form

            ArrayList al = null:
            ArrayList advert_list = new ArrayList();
            String query = "SELECT title, price, image1 FROM advert WHERE title, make,   `model LIKE '%%' "";` //sort according to lkeness of search entry entered by user

            System.out.println("query " + query);
            st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);

            while (rs.next()) {
                al = new ArrayList();

//                out.println(rs.getString(1));
//                out.println(rs.getString(2));
//                out.println(rs.getString(3));
//                out.println(rs.getString(4));
                al.add(rs.getString(1));
                al.add(rs.getString(2));
                al.add(rs.getString(3));
                al.add(rs.getString(4));

                System.out.println("al :: " + al);
                advert_list.add(al);
            }

            request.setAttribute("adList", advert_list);
            RequestDispatcher view = request.getRequestDispatcher("/results_page.jsp");
            view.forward(request, response);
            conn.close();
            System.out.println("Disconnected!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

      /** 
     * Returns a short description of the servlet.
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}


**results_page.jsp**


    <head>
        <title>Search Results</title>

    <script type="text/javascript" src="html5lightbox/jquery.js"></script>
    <script type="text/javascript" src="html5lightbox/html5lightbox.js"></script>

    </head>
        <body>

    <div class="html5lightbox">
        <!-- need fetch only one image per advert to show the picture of the car ->
        <!-- so there should be  images corresponding to the arrays of results or adverts and the advert `details` ->
            </div>

    <div class="advert_details">
    <!--need to fetch title of advert and price  ->

        </div>
    </body>
    </html>

 ****   **FetchAdvertServlet.java**

//don't have an idea where to start
// need to fetch images  and details according to link pressed by the user 

**advert.jsp**

<!-- also don't have an idea, need loads of help or ideas ->
<!-- if there is a way for the page to fetch the advert's details by 
2
Contributors
1
Reply
15
Views
3 Years
Discussion Span
Last Post by peter_budo
0

Your SQL query is wrong

 "SELECT title, price, image1 FROM advert WHERE title, make,   `model LIKE '%%' "";`
  • You are having ``` in front of model
  • I have not sure what '%%'is supposed to represent
  • Also at the end you have two double quotes

To asure that your query is correct you can always connect to DB from command line and execute querry to check that it is returning corwct results

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.