4 Replies Latest reply: Sep 5, 2007 9:36 AM by 807600 RSS

    Multiple field database search problem... plz help me

    807600
      Hi,

      I have a search form with 6 search fields. 5 text boxes and one combo box. I want to search values from two tables based on this search keys. I am using 3 tables. Followings are the table structure....
      comp_det
      CREATE TABLE `comp_det` (`comp_id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
      `compname` VARCHAR( 100 ) NOT NULL ,
      `address` VARCHAR( 100 ) NOT NULL ,
      `city` VARCHAR( 100 ) NOT NULL ,
      `state` VARCHAR( 100 ) NOT NULL ,
      `membnum` VARCHAR( 100 ) NOT NULL ,
      PRIMARY KEY ( `comp_id` )) TYPE = MYISAM ;

      pro_det
      CREATE TABLE `comp_det` (`comp_id` INT( 11 ) NOT NULL AUTO_INCREMENT , `pro_name` VARCHAR( 100 ) NOT NULL , PRIMARY KEY ( `pro_id` )) TYPE = MYISAM ;

      comp_prod
      CREATE TABLE comp_prod (comp_id INT NOT NULL,pro_id INT NOT NULL,productName varchar(255),FOREIGN KEY (comp_id) REFERENCES comp_det (comp_id),FOREIGN KEY (pro_id) REFERENCES pro_det (pro_id)) TYPE = MYISAM ;

      These are the search key :
      Company Name (compname from comp_det)
      contact person (personname from comp_det)
      city( city from comp_det)
      state (state from comp_det)
      membership number (membnum from comp_det)
      Products( pro_name from pro_det)

      i tried with with the following sql query. But i couldn't gt the correct result
           String cn="'"+request.getParameter("compname")+"'";
           String cp="'"+request.getParameter("contactperson")+"'";
           String ct="'"+request.getParameter("city")+"'";
           String st="'"+request.getParameter("state")+"'";
           String mn="'"+request.getParameter("membershipnumber")+"'";
           String ps="'"+request.getParameter("pro_name")+"'";
           String slct="";

      slct="select distinct t1.comp_id, t1.compname, t1.personname, t1.city, t1.state, t1.email, t1.website, t2.pro_name from comp_det t1, pro_det t2, comp_prod t3 where t1.compname= " + cn + " or t1.personname= " + cp + " or t1.city = " + ct + " or t1.state = " + st + "or t1.membnum = " + mn + "or t2.pro_id = " + ps + " and t1.comp_id = t3.comp_id ORDER BY t1.compname";

      Please help me..... Its urgent
      Thanks
        • 1. Re: Multiple field database search problem... plz help me
          791266
          Use PreparedStatements. The way you are trying to solve it opens up for SQL injection attacks.

          Kaj
          • 2. Re: Multiple field database search problem... plz help me
            807600
            hi
            thanks for your reply. Iam using prepared statement.
            here my coding

            String DbDriver = "org.gjt.mm.mysql.Driver";
            String url="jdbc:mysql://localhost:3306/mydb";
                 Connection con=null;
                 PreparedStatement pstmt=null;
            try {
                      Class.forName(DbDriver).newInstance();
            con=DriverManager.getConnection(url,"myuid","mypwd");
                      
                 String cn="'"+request.getParameter("compname")+"'";
                 String cp="'"+request.getParameter("contactperson")+"'";
                 String ct="'"+request.getParameter("city")+"'";
                 String st="'"+request.getParameter("state")+"'";
                 String mn="'"+request.getParameter("membershipnumber")+"'";
                 String ps="'"+request.getParameter("pro_name")+"'";
                 String slct="";
                      
                      slct="select distinct t1.comp_id, t1.compname, t1.personname, t1.city, t1.state, t1.email, t1.website, t2.pro_name from comp_det t1, pro_det t2, comp_prod t3 where t1.compname= " + cn + " or t1.personname= " + cp + " or t1.city = " + ct + " or t1.state = " + st + "or t1.membnum = " + mn + "or t2.pro_id = " + ps + " and t1.comp_id = t3.comp_id ORDER BY t1.compname";

            pstmt = con.prepareStatement(slct);
                      ResultSet rs = pstmt.executeQuery();
            • 3. Re: Multiple field database search problem... plz help me
              791266
              hi
              thanks for your reply. Iam using prepared statement.
              here my coding
              You are using it in the wrong way.

              You should use setString etc to specify the values. Google for a tutorial on PreparedStatement.

              Kaj
              • 4. Re: Multiple field database search problem... plz help me
                807600
                k i vl try and get back to you