MySQL query using LIKE and spaces

Question

I'm having trouble getting a query to display results if there are spaces in the search input. The database has a column that has two strings in it. The issue is that I don't know how to use LIKE to filter information if it has spaces in the string. I'm not sure if MATCH AGAINST is needed for this since I'm not interested in two separate columns of data in the table, I'm interested in one column that has two words separated by a space, the column is fullNameNormal.

EDIT: I just realized that I forgot that the search feature is actually the input that is for $search. Originally documented it as $letter, but that is for a letter search only.

$search = '';
        if(isset($_REQUEST['search'])){
            $search = substr($this->encode($_REQUEST['search']), 0, 50);
        }
$letter = '';
if (isset($_REQUEST['letter'])) {
    $letter = substr($_REQUEST['letter'], 0, 1);
}
// Get total amount of records for current search...for paging
$total = 0;
$sql = "SELECT COUNT(*) FROM " . $wpdb->prefix . "example_directory";
$clean_where = " WHERE dirListing = 'public' AND active = 1 AND (((employeeType = 'faculty') AND (shortPositionCode NOT IN ('47', '49', '58', '59')) AND (status IN ('fullTime', 'partTime', 'proRata'))) OR ((employeeType = 'staff') AND (shortPositionCode NOT IN ('47', '49', '58', '59')) AND (status IN ('fullTime', 'partTime'))))";
$order = " ORDER BY lastName, firstName, department";
$limit_query = $wpdb->prepare(" LIMIT %d, %d", $start, $limit);
$where = "";
$args = array();
if ($search != '') {
    $where = " AND (lastName LIKE %s OR firstName LIKE %s OR department LIKE %s OR fullName LIKE %s OR nickName LIKE %s OR jobTitle LIKE %s OR phoneExt LIKE %s OR phone LIKE %s OR email LIKE %s OR locationBuilding LIKE %s OR locationBuildingAbbrev LIKE %s OR locationRoom LIKE %s or fullNameNormal LIKE %s)";
    $arg = '%' . $search . '%';
    $args = array($arg, $arg, $arg, $arg, $arg, $arg, $arg, $arg, $arg, $arg, $arg, $arg, $arg);
} elseif ($letter != '') {
    $where = " AND lastName LIKE %s";
    $arg = $letter . '%';
    $args = array($arg);
} else {
    $where = "";
}
    $where = $wpdb->prepare($where, $args);
    $total = $wpdb->get_var($sql . $clean_where . $where . $order);

As previously stated, I'm only interested in fullNameNormal which would be as an example "John Smith" in the cell, but it seems that there's no way to use LIKE and limit it to only what I put in the input that becomes $search. I also tried adding MATCH and AGAINST like this -

$where = " AND (lastName LIKE %s OR firstName LIKE %s OR department LIKE %s OR fullName LIKE %s OR nickName LIKE %s OR jobTitle LIKE %s OR phoneExt LIKE %s OR phone LIKE %s OR email LIKE %s OR locationBuilding LIKE %s OR locationBuildingAbbrev LIKE %s OR locationRoom LIKE %s OR MATCH(fullNameNormal) AGAINST (".$search."))";

EDIT: Also tried this -

$where = " AND (lastName LIKE %s OR firstName LIKE %s OR department LIKE %s OR fullName LIKE %s OR nickName LIKE %s OR jobTitle LIKE %s OR phoneExt LIKE %s OR phone LIKE %s OR email LIKE %s OR locationBuilding LIKE %s OR locationBuildingAbbrev LIKE %s OR locationRoom LIKE %s OR (fullNameNormal LIKE 'something%' AND fullNameNormal LIKE '% something%')";

Ended up query everything despite what I put in the search input.

I'm unsure of what I need to do here.

You need to use columnname LIKE '%something%' i.e. the string need to be inside single quotes — RiggsFolly
– RiggsFolly, Commented Sep 9, 2015 at 18:06
FYI: You can do lastName LIKE ? in your prepare and then bind $arg to that parameter. — gen_Eric
– gen_Eric, Commented Sep 9, 2015 at 18:08
Where is $search coming from? Can you try entering "John Smith" and then echo the $where variable to see what's going on? — uri2x
– uri2x, Commented Sep 9, 2015 at 18:42
You should really be taking a natural language search approach to this problem. dev.mysql.com/doc/refman/5.7/en/fulltext-natural-language.html — Mike Brant
– Mike Brant, Commented Sep 9, 2015 at 19:45

Mike Brant · Accepted Answer · 2015-09-09 19:58:49Z

1

You should really be taking a natural language search approach to this problem. A query using this approach would look like:

SELECT COUNT(*) FROM *example_directory
WHERE MATCH (
  lastName,
  firstName,
  department,
  fullName,
  nickName,
  jobTitle,
  phoneExt,
  phone,
  email,
  locationBuilding,
  locationBuildingAbbrev,
  locationRoom,
  fullNameNormal
) AGAINST (? IN NATURAL LANGUAGE MODE)
AND  ... /* other filter conditions */

Where ? is your search phrase.

You will need to create a FULLTEXT index on the columns in your search.

See MySQL Documentation.

Side note: I have no idea why you would be applying ordering or limits in your query within the context of COUNT(*) not being able to return more than one row without any additional fields in select.

edited Sep 9, 2015 at 19:58

answered Sep 9, 2015 at 19:52

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Howell Over a year ago

Thank you, I looked over the documentation in your comment and agree with this solution, this was built by someone else and I was just requested to change the functionality to include FULLTEXT indexing but have never done that before.

Collectives™ on Stack Overflow

MySQL query using LIKE and spaces

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related