#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2002
    Posts
    20
    Rep Power
    0

    concatenating strings and columns in mysql query statement in a jsp page


    Hi

    I would like to know how to concatenate two strings and columns in the where portion in an mysql statement.

    Here is a fragment of my code

    /*Search student refers to the name of the textfield either student number, first name, last name, or both first name and last name*/

    //the var student is the student number
    //the var first is the first name
    //the var last is the last name

    String student = request.getParameter("searchStudent");
    String first = request.getParameter("searchStudent");
    String last = request.getParameter("searchStudent");

    student.trim();
    first.trim();
    last.trim();

    Class.forName("org.gjt.mm.mysql.Driver").newInstance();
    connection = DriverManager.getConnection(connectionURL, "", "");
    statement=connection.createStatement();

    //Query statment
    //This statment search for all records from students table relating
    //firstname, lastname, first name and last name,student number
    //of particular student

    //Whats wrong with this statement
    rs=statement.executeQuery("SELECT * FROM students WHERE firstname+lastlastname='" +first+last+"' OR firstname='" + first + "' OR lastname='" + last + "' OR studentid='" + student + "'");

    while (rs.next()){
    String studentID=rs.getString(1);
    String firstName=rs.getString(2);
    String lastName=rs.getString(3);

    .......

    I'm getting this error
    javax.servlet.ServletException: Column not found: Unknown column 'first' in 'where clause'

    I want the text field to accept a full name for example "Tom Jones"
    It will then search the firstname and lastname from the student table and display those records for that persons name.

    Please Help

    Leslie
  2. #2
  3. No Profile Picture
    Java Developer
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2001
    Posts
    5
    Rep Power
    0
    there is a mysql function called concat

    i.e.

    firstname+lastname

    is equivalent to

    concat(firstname, lastname)

    Probably no help, but the error message doesn't make too much sense, cos you are never refering to a column as "first" this is purely in the java, so the db will not see this as it is at a different layer of abstraction

    Oscagne

IMN logo majestic logo threadwatch logo seochat tools logo