PHP SQL: Order search query results by user input order

Question

I've tried to look for a solution but can't seem to grasp the issue I have.

I have a search query with a "where clause" stating if a user inputs multiple words return the result.

I need the result returned in the same order searched.

Even if i just add the addition "ORDER BY DESC" an error is thrown "Trying to get property of non-object".

Here is my code:

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


    }

Nigel Ren · Accepted Answer · 2018-07-07T07:11:17.080

There are a couple of problems with the way your trying to use ORDER BY. There should be only 1 order by clause in any SQL, you are adding it in for each word your adding. The second part is that it's expecting to order by a column name and your ordering it by the words your searching for.

With wanting to maintain the order of the terms and the order of the results, it would be necessary to use an order by clause with something like a case (Can you add an if statement in ORDER BY? may help explain this).

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

Yes, thanks I'm no longer getting an error but resulted words are not displaying in the same order as the initial search? so if searched 3,2,5 it would return in the same order 3,2,5 not "2,3,5" which is what it's doing at the moment. Hope that makes sense — johnyTran, Jul 07 '18 at 06:53
I've had to rework most of the code, but this should give you closer to what your after. — Nigel Ren, Jul 07 '18 at 07:11
Perfect! thanks for the explanation i'll study more into this. — johnyTran, Jul 07 '18 at 07:23

score 0 · Answer 2 · answered Jul 07 '18 at 06:26

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 . "' ";
}

score 0 · Answer 3 · answered Jul 07 '18 at 06:29

I think you messed up with MySQL Query string in bellow line code.

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

Your Query is generating Something like

ORDER BY DESC

And OrderBy Query should be something like this

ORDER BY expression [ ASC | DESC ];

So you are missing the expression in query.

PHP SQL: Order search query results by user input order

3 Answers3