php - 搜索功能在我的 PHP MySQL 查询中不起作用

Question

我有一个使用 PHP 的 mysql 查询，第一部分工作正常，但第二部分在站点上具有搜索功能，根本无法正常搜索。这是确切的代码:

<?
$qry = "
SELECT bidprice,timelive,match_title,
CASE 
WHEN game.result LIKE '' THEN 'PENDING'
WHEN game.result LIKE 1 THEN 'WON'
END AS result
FROM game
ORDER BY timelive DESC
";
$searchText = "";
if($_REQUEST['search_text']!=""){
    $searchText = $_REQUEST['search_text'];
$qry .=" WHERE game.bidprice LIKE '%$searchText%' OR game.timelive LIKE '%$searchText%'";
}

//for pagination
$starting=0;
$recpage = 10;//number of records per page

$obj = new pagination_class($qry,$starting,$recpage);       
$result = $obj->result;

?>

所以上面代码中正在考虑搜索我的“游戏”表的这部分代码不起作用:

$searchText = "";
if($_REQUEST['search_text']!=""){
    $searchText = $_REQUEST['search_text'];
$qry .=" WHERE game.bidprice LIKE '%$searchText%' OR game.timelive LIKE '%$searchText%'";

在我的页面上，当我打开此页面并尝试搜索单词“football”时收到此错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL    server version for the right syntax to use near 'game.bidprice like   '%football%' OR game.timelive like   '%football%' at line 9

当放置一些简单的 SELECT 语句时，此搜索功能工作正常:从游戏中选择 *但是当完成那些更复杂的带有 CASE 和 WHEN 语句的选择查询时，我需要它不起作用......

请帮我编写正确的代码以实现正确的搜索功能

Answer 1

问题是在 ORDER BY 之后不能有 WHERE 子句。您的查询目前看起来像这样:

SELECT bidprice,timelive,match_title,
CASE 
WHEN game.result LIKE '' THEN 'PENDING'
WHEN game.result LIKE 1 THEN 'WON'
END AS finalization
FROM game
ORDER BY timelive DESC WHERE game.bidprice LIKE '%football%' OR game.timelive LIKE '%football%'
                      ^^^^^^^ error

但是你需要它看起来像这样:

SELECT
    bidprice,
    timelive,
    match_title,
    CASE 
        WHEN game.result LIKE '' THEN 'PENDING'
        WHEN game.result LIKE 1 THEN 'WON'
    END AS finalization
FROM game
WHERE game.bidprice LIKE '%football%' OR game.timelive LIKE '%football%'
ORDER BY timelive DESC

之后您应该在 PHP 脚本中添加 ORDER BY。尝试这样的事情:

$qry = "
SELECT bidprice,timelive,match_title,
CASE 
WHEN game.result LIKE '' THEN 'PENDING'
WHEN game.result LIKE 1 THEN 'WON'
END AS result
FROM game
";

$searchText = "";
if ($_REQUEST['search_text']!="")
{
    $searchText = mysql_real_escape_string($_REQUEST['search_text']);
    $qry .= " WHERE game.bidprice LIKE '%$searchText%' " .
            " OR game.timelive LIKE '%$searchText%'";
}

$qry .= " ORDER BY timelive DESC";