search function not working in my PHP MySQL query

Question

I have this mysql query using PHP and first part is working fine, but second part where have search function on site simply cannot get it workable to search properly. Here is exact code:

<?
$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;

?>

So this part of code from above code which is considering searching my 'game' table is not working:

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

On my page I receive this error when open this page and try to search for word '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

This search function is working fine when put some simple SELECT statement something like: SELECT * FROM game but when done those more complex select query with CASE and WHEN statements which i need its not functioning...

Please help me with proper code for proper search function

Your code is vulnerable to SQL injection. Please use $searchText = mysql_real_escape_string($_REQUEST['search_text']); — Michael Berkowski
– Michael Berkowski, Commented Nov 1, 2011 at 17:43

Mark Byers · Accepted Answer · 2011-11-01 17:48:30Z

The problem is that you can't have a WHERE clause after the ORDER BY. Your query currently looks like this:

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

But you need it to look like this:

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

You should add the ORDER BY afterwards in your PHP script. Try something like this:

$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";

Aurelio De Rosa · Accepted Answer · 2011-11-01 17:48:38Z

3

You have several errors.

First: You're putting the WHERE after the ORDER BY.

Second: Using just $searchText = $_REQUEST['search_text']; you can receive an sql injection.

edited Nov 1, 2011 at 17:48

answered Nov 1, 2011 at 17:41

Aurelio De Rosa

22.2k8 gold badges51 silver badges71 bronze badges

1 Comment

Ivy Over a year ago

Thank you for noticing me this as am beginner in this so didn't realize this before. I see Mark Byers edit this too in full code above :)

Collectives™ on Stack Overflow

search function not working in my PHP MySQL query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related