I'm creating a method wherein a user can search a customer either by first name, last name, or full name. the search bar only shows the result if it's by first or last name but when I try searching a customer by full name the record won't show.

Here's my code:

public int getMatchingCustomerRecords(String keyword) {
    int rows = 0;
    try {
        if (db.startTransaction()) {
            String sql = "Select COUNT(firstName OR lastName) AS TOTAL FROM Customer";
            String sqlSearch = "";
            if (keyword != null) {
                sqlSearch = sql + " WHERE firstName LIKE '%" + keyword + "%' AND lastName LIKE '%" + keyword + "%'";
                ps = db.getQueryStatement(sqlSearch);
            } else {
                ps = db.getQueryStatement(sql);
            }

            rs = ps.executeQuery();
            if (rs.next()) {
                rows = rs.getInt("TOTAL");

            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return rows;
}

3 Answers

-2
Community On
public int getMatchingCustomerRecords(String keyword) {
int rows = 0;
try {
    if (db.startTransaction()) {
        String sql = "Select COUNT(firstName OR lastName) AS TOTAL FROM Customer";
        String sqlSearch = "";
        if (keyword != null) {
            sqlSearch = sql + " WHERE LOCATE(firstName ,'" + keyword + "')>=0 AND LOCATE(lastName ,'" + keyword + "')>=0";
            ps = db.getQueryStatement(sqlSearch);
        } else {
            ps = db.getQueryStatement(sql);
        }

        rs = ps.executeQuery();
        if (rs.next()) {
            rows = rs.getInt("TOTAL");

        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}

return rows;

}

2
Ronald Haan On

Your queries should be:

Select * FROM `Customer`
WHERE `firstName` LIKE '%" + keyword + "%' OR `lastName` LIKE '%" + keyword + "%'"
0
sachin-rai On
public int getMatchingCustomerRecords(String keyword) {
        int rows = 0;
        try {
            if (db.startTransaction()) {
                String sql = "Select COUNT(firstName OR lastName) AS TOTAL FROM Customer";
                String sqlSearch = "";
                if (keyword != null) {

             String[] keywordArray=keyword.split(" ");


            if(keywordArray.length>1) {
             // matching full Name
                sqlSearch = sql+" WHERE firstName LIKE '%" + keywordArray[0].trim() + "%' AND lastName LIKE '%" + keywordArray[1].trim() + "%'";
            }else {
             // matching firstName or lastName
                sqlSearch = sql+" WHERE firstName LIKE '%" + keywordArray[0].trim() + "%' OR lastName LIKE '%" + keywordArray[0].trim() + "%'";
            }
             ps = db.getQueryStatement(sqlSearch);
         } else {
             ps = db.getQueryStatement(sql);
         }
                rs = ps.executeQuery();
                if (rs.next()) {
                    rows = rs.getInt("TOTAL");

                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rows;
    }