PHP SQL:按用户输入顺序对搜索结果进行排序

Question

我试图寻找解决方案，但似乎无法掌握我遇到的问题。

我有一个带有"where 子句"的搜索查询，说明用户输入多个单词是否返回结果。

我需要以与搜索顺序相同的顺序返回结果。

即使我只是添加"ORDER BY DESC"，也会抛出错误"试图获取非对象的属性"。

这是我的代码：

$word = $_GET['word'];
$word3 = $_GET['word'];
$word = explode(";", $word);
$noOfWords = count($word);
$word2 = $word3;
if ($noOfWords == 1) {
$searchString = " word_eng LIKE '" . $conn->escape_string($word3)  
"%'";
} else {
$searchString = $whereClause = "";
foreach ($word as $entry) {
$searchString .= " OR word_eng LIKE '" . $conn->escape_string($entry) . "' ORDER BY '" . $word2 . "' ";
}
}
$whereClause = ($searchString != "") ? " WHERE " . preg_replace('/OR/', 
'', $searchString, 1) : $whereClause;

$sql = "SELECT word_eng FROM words " . $whereClause . " LIMIT 17";
$result = $conn->query($sql);
if ($result->num_rows > 0) { 
while($row = $result->fetch_assoc()) { 
$row1 = $row["word_eng"];
echo $row1;     

}

Answer 1

您尝试使用 ORDER BY 的方式存在一些问题。 在任何 SQL 中都应该只有 1 个 order by 子句，您正在为添加的每个单词添加它。 第二部分是，它期望按列名排序，并按搜索的单词对其进行排序。

由于想要保持项的顺序和结果的顺序，有必要使用带有case之类的顺序by子句(你能在ORDER BY中添加一个if语句吗？可能有助于解释这一点(。

$orderBy = "";
if ($noOfWords == 1) {
$searchString = " word_eng LIKE '" . $conn->escape_string($word3) ."%'";
} else {
$searchString = $whereClause = "";
$orderBy = " order by case `word_eng` ";
foreach ($word as $order=>$entry) {
$searchString .= " OR word_eng LIKE '" . $conn->escape_string($entry) . "'";
$orderBy .= " when '$entry' then $order ";
}
$orderBy .= " end ";
}
$whereClause = ($searchString != "") ? " WHERE " . preg_replace('/OR/',
'', $searchString, 1) : $whereClause;

$sql = "SELECT word_eng FROM words " . $whereClause . " " .$orderBy." LIMIT 17";

Answer 2

if ($noOfWords == 1) {
$searchString = " word_eng LIKE '" . $conn->escape_string($word3)  
"%'";
} else {
$searchString = $whereClause = "";
foreach ($word as $entry) {
$searchString .= " OR word_eng LIKE '" . $conn->escape_string($entry);
}
$searchString .= "' ORDER BY '" . $word2 . "' ";
}

Answer 3

我想你在下面的行代码中搞砸了MySQL查询字符串。

$searchString .= " OR word_eng LIKE '" . $conn->escape_string($entry) . "' ORDER BY '" . $word2 . "' ";

您的查询正在生成类似

ORDER BY DESC

OrderBy Query应该是这样的

ORDER BY expression [ ASC | DESC ];

因此，您在查询中缺少表达式。