通过逗号分隔搜索不同的列 [英] Search different columns by a separation of a comma

问题描述

我有一张表格，其中包含姓名、电子邮件地址、号码等信息.

I have a table that contains information such as names, email-addresses, numbers etc.

假设我有 30 个同名联系人，但他们都住在不同的城市.如何拆分逗号并将其替换为 和 ....

Let's pretend that I have 30 contacts by the same name but they all live in different cities . How do I split the comma and replace it with and ....

示例SELECT * WHERE name %$searchString% OR city %$searchString%...

现在如果 $searchString 包含逗号

SELECT * WHERE name %$searchString% OR city %$searchString%... AND SELECT * WHERE name %$searchString2% OR city %$searchString2%...

$searchString2 包含用逗号分隔的信息.

The $searchString2 contains information that's separated with comma.

更新

我想一遍又一遍地搜索每一行，次数与逗号存在的次数相同.对不起，我无法解释自己

I want to search each row over and over again as many times as commas exist. I'm sorry that I can't explain myself

推荐答案

这取决于您是要返回名称或城市与搜索值完全匹配 (=) 的行，还是 名称或城市的任何部分匹配搜索值(LIKE).

This depends on whether you want to return rows where name or city match the search values exactly (=), or rows where any part of name or city match the search values (LIKE).

无论您需要哪一个，您都可以先将搜索字符串转换为这样的字符串数组:

Regardless of which one you need, you can start out by converting your search string into an array of strings like this:

$strings = array_map('trim', explode(',', $searchString));

array_map('trim'... 确保您不会尝试匹配逗号分隔搜索字符串中逗号前后的任何空格.

The array_map('trim'... ensures that you don't try to match any spaces before or after the commas in your comma-separated search string.

以下示例介绍了如何使用 PDO 中的预准备语句执行查询.首先，使用 IN 进行完全匹配:

Here are examples for how to execute your query using prepared statements in PDO. First, full matches using IN:

$phs = rtrim(str_repeat('?,', count($strings)),',');
$stmt = $pdo->prepare("SELECT * FROM your_table WHERE name IN ($phs) OR city IN ($phs)");
// double the string values to us in both INs
$values = array_merge($strings, $strings);
$stmt->execute($values);

和使用 LIKE 的部分匹配:

and partial matches using LIKE:

$sql = '';
foreach ($strings as $string) {
    $sql .= ' name LIKE ? OR city LIKE ? OR';
    $values[] = $string;
    $values[] = $string;
}
$stmt = $pdo->prepare('SELECT * FROM your_table WHERE' . rtrim($sql, ' OR'));
$stmt->execute($values);

这篇关于通过逗号分隔搜索不同的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！