php MySQL 在两列上搜索 [英] php MySQL Search on two columns

问题描述

我正在尝试使用 php 和 MySQL 执行搜索功能.我需要从两个不同的列中进行搜索，一个是歌曲名称，一个是艺术家，用户可以使用以下方法进行搜索:

I'm trying to do a search function using php and MySQL. I need to search from two different columns, one for song title and one for artist and user can search using:

1. artist name.
2. song title.
3. both of them (without order)
4. there will be special characters for ex: ' (apostrophe)

这就是我所做的，但我需要一种更有效的方法来做到这一点.我也想过在 php 中使用 similar_text(); .谁能建议我一个更好的方法来做到这一点.谢谢

This is what I have done but I need a more efficient way to do this. I have also thought using similar_text(); in php. Can anyone suggest me a better way to do this. Thank You

表格:歌曲

|artist   |    title   |
|----------------------|
|eminem   | not afraid |
|kida     | o'najr     |

我的代码:

$search_value = $_POST["search_value"];
$query = "select * from `songs` where concat(`title`, `artist`) like '%$search_value%'";

推荐答案

1. 在这种情况下，您应该使用 SQL OR 语句，比 CONCAT 语句更好.

1. You should use the SQL OR statement, better than CONCAT one in this case.

结合您搜索内容前后的空格，这应该会给您预期的结果！(我的意思是，如果您搜索raid"，您将找不到Eminem - Not Afraid"，如果您想找到它，您必须搜索afraid"，例如；如果您想按 revelance 对结果进行排序，您将必须创建索引并使用它们，或者使用 Levenshtein 方法或其他方法......)

Combined with a space before and after what you search, this should give you the expected result ! (I mean if you search for 'raid' you will not find 'Eminem - Not Afraid', if you want to find it you have to search for 'afraid' for exemple ; If you want to sort the results by revelance, you will have to create indexes and use them, or use Levenshtein method or something else ...)

在 sql 语句中使用数据之前，不要忘记对其进行转义.

Don't forget to escape your data before using it in sql statements.

顺便说一句，如果你想让它不区分大小写，你将不得不使用 blabla COLLATE UTF8_GENERAL_CI LIKE %what you search% blabla

Btw if you want to make it case insesitive you will have to use blabla COLLATE UTF8_GENERAL_CI LIKE %what you search% blabla

// if you are using mysql_connect()
$search_value = ' '.mysql_real_escape_string($_POST["search_value"]).' ';
$query = "select * from `songs` where `title` like '%$search_value%' or  `artist` like '%$search_value%'";

// if you are using PDO
$args[':search_value'] = '% '.$_POST["search_value"].' %';

$query = "SELECT * FROM `songs` WHERE `title` LIKE :search_value OR `artist` LIKE :search_value";

$qry = $PDO->prepare($query);
$res = $qry->execute($args);

对于多词搜索，您也可以使用

For a multi-words search you can also use

// for mysql_connect()
$search_value = $_POST["search_value"];
$search_value = explode(' ', $search_value);
foreach($search_value as $k => $v){
    $search_value[$k] = mysql_real_escape_string($v);
}
$search_value = ' '.implode(' % ', $search_value).' ';

// for PDO
$search_value = explode(' ', $_POST["search_value"]);
$search_value = implode(' % ', $search_value);
$args[':search_value'] = '% '.$search_value.' %';

这篇关于php MySQL 在两列上搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！