MySQL查询使用LIKE和空格

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.

如果搜索输入中有空格,我在查询显示结果时遇到问题。数据库有一个包含两个字符串的列。问题是,如果字符串中有空格,我不知道如何使用LIKE过滤信息。我不确定是否需要MATCH AGAINST,因为我对表中的两个单独的数据列不感兴趣,我对一个列有两个单独的空格列感兴趣,列是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的输入。最初将其记录为$ letter,但这仅适用于字母搜索。

$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 -

如前所述,我只对fullNameNormal感兴趣,它将作为单元格中的“John Smith”示例,但似乎没有办法使用LIKE并将其限制为仅在输入中放入$ search的内容。我也尝试过像这样添加MATCH和AGAINST -

$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.

我不确定我需要做什么。

1 个解决方案

#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.

您需要在搜索中的列上创建FULLTEXT索引。

See MySQL Documentation.

请参阅MySQL文档。

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.

旁注:我不知道为什么你会在COUNT(*)的上下文中应用查询中的排序或限制,而不能在select中返回多行而没有任何其他字段。

#1