使用 1 个输入字段搜索 2 列
我有一个包含两列的数据库：名字和姓氏。名字可以有多个单词。姓氏可以包含连字符的单词。
有没有一种方法可以只用一个输入框搜索两列？
数据库
ID `First Name` `Last Name`
1 John Peter Doe
2 John Fubar
3 Michael Doe
搜索
john peter 返回 id 1
john 返回 id 1,2
doe 返回 id 1,3
john doe 返回 id 1
peter john 返回 id 1
peter doe 返回 id 1
doe john 返回 id 1
我之前尝试过以下操作。搜索 John Doe:
SELECT * FROM names WHERE (
`first` LIKE '%john%' OR
`first` LIKE '%doe%' OR
`last` LIKE '%john%' OR
`last` LIKE '%doe%'
)
会同时返回 1 和 3
I have a db with two columns: first name and last name. The first name can have multiple words. Last name can contain hyphenated words.
Is there a way to search both columns with only one input box?
Database
ID `First Name` `Last Name`
1 John Peter Doe
2 John Fubar
3 Michael Doe
Search
john peter returns id 1
john returns id 1,2
doe returns id 1,3
john doe returns id 1
peter john returns id 1
peter doe returns id 1
doe john returns id 1
I previously tried the following. Searching for John Doe:
SELECT * FROM names WHERE (
`first` LIKE '%john%' OR
`first` LIKE '%doe%' OR
`last` LIKE '%john%' OR
`last` LIKE '%doe%'
)
which returns both 1 and 3
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
需要名字和姓氏之间的关联。
对于两个单词 (w1,w2)，条件
含义是，w1 必须是名字，w2 必须是姓氏，
或者 w2 必须是名字，w1 必须是姓氏。
这样，John Doe 将选择 ID 1。
A correlation between the first and last names is required.
For two words (w1,w2), the condition
Meaning, w1 must be in first name AND w2 in last name,
OR w2 must be in first name AND w1 in last name.
This way John Doe will have ID 1 selected.
您可以按空格分割用户名“words”并获取数组的最后一项，这应该是他的姓氏。然后你应该像“Nanne”所说的那样查询SQL，或者你可以使用标签搜索。
祝你好运。
You can split user name "words" by space and get the last item of array which should be his last name. Then you should query SQL like "Nanne" said or you can use tags search.
Good luck.
我不知道你到底想问什么，但你的意思不是就是这个吗？
正如您的评论所说，如果任何单词可以出现在任何列中（它们应该至少出现一次吗？或者应该是最合适的？不同的顺序意味着什么）那么您将不得不做更多的事情，但是您确实需要首先考虑一些规格:)
I don't know what you're asking exactly, but don't you mean just this?
As you comment says, if any of the words can be in any column (and should they be present at least once? or should it be best fit? does a different order mean anything) then you'll have to do something more, but you really need to think of some specifications then first :)
一个简短的解决方案是通过 preg_match 或类似的东西找到输入的最后一个空格（现在真的不记得正确的函数），然后将其用作标记。将标记后面的所有内容存储在姓氏“searchstring”中，将其之前的所有内容存储在名字“searchstring”中。
不过，此解决方案的一个明显缺陷是，如果该人的姓氏只有两个单词。但是，我不确定您是否/接受/接受两个单词的姓氏。所以，让我在这里引用 Nanne 的话：
A short solution would be to find the last whitespace of the input by preg_match or something to that effect(really can't remember the right function right now), then use that as a marker. Store everything after the marker in the last name "searchstring" and everything before it in the first name "searchstring".
An obvious flaw in this solution though would be if the person had 2 words for a last name. But, I'm not sure if you /do/ accept 2 word last names or not. So, let me quote Nanne here: