Need to fix sorting order by name and price-CodePudding

I am working on school listing project. I have created database name is school and under I add new table name is schoollist Here I have many school. I have added few column on it bellow the list of column.

|-------name---------|----address----|----price--|
| Wright school      | Oslo, Norway  |  870      |
| metro  school      | Oslo, Norway  |  880      |
| unit   school      | Oslo, Norway  |  670      |
| Oslo   school      | Oslo, Norway  |  540      |
| Wright oslo school | Oslo, Norway  |  510      |

For the above table i want to search Wright Oslo and after the result should be Wright oslo school and Wright school because Wright oslo school have low price. And other school dont need show because of they dont have exact name school of wright. How can i get it done by sql query I used old query but its not working perfectly bellow the code:

 if(isset($_POST['searchbtn']) ){
    $searchContent = strtolower(mysqli_real_escape_string($db, trim($_POST['search'])));
     if(empty($searchContent)){
       header("Location: index.php");
exit();}                   
    $searchArray = explode(" ", $searchContent);
    
    foreach ($searchArray as $word) {
            $sql = "SELECT * FROM `schoolist` WHERE MATCH(sc_name, sch_address) AGAINST('%$word%') ORDER BY CASE WHEN sc_name like '%$word' THEN 1 ELSE 2 END,sc_name ";        
        }

I want output should be like bellow:

|-------name---------|----address----|----price--|
| Wright Oslo school | Oslo, Norway  |  510      |
| Wright school      | Oslo, Norway  |  870      |

CodePudding user response：

Using the same dataset as yours :

You can achieve your goal using this query :

SELECT * FROM schoollist WHERE CONCAT(name, address) LIKE $input ORDER BY price; //$input = %Wright%Oslo% (see below)

Here is the result as expected :

You order by price (ASC by default)
You search for any match with the keywords (Wright and Oslo), even if they are not stuck together. Once you get the user input, you have to string replace any whitespace with the % caracter and to add % at the beginning and at the end of the input :
```
$input = '%' . $_GET['input'] . '%';

$input = str_replace(" ","%",$input); // %Wright%Oslo%
```

For security purposes, you should use a prepared statement :

$sql = "SELECT * FROM schoollist WHERE CONCAT(name, address) LIKE ? ORDER BY price;";

$stmt = $conn->prepare($sql); 
$stmt->bind_param("s", $input);   

$stmt->execute();