Sorting the results of a mysql query in php

Question

I have a search page on my site that lists a names of flowers

PHP code :

$raw_results = mysql_query("SELECT * FROM flowers
WHERE (`title` RLIKE '".$query."[.,?!]?[ ]') LIMIT 200") or die(mysql_error());

Result :

I want to sort the results
Like this :

Full php code : search.php

<?php
$query = $_GET['query']; 
$min_length = 2;
if(strlen($query) >= $min_length){
$query = htmlspecialchars($query); 
$query = mysql_real_escape_string($query);
$raw_results = mysql_query("SELECT * FROM flowers
WHERE (`title` RLIKE '".$query."[.,?!]?[ ]') LIMIT 200") or     die(mysql_error());

if(mysql_num_rows($raw_results) > 0){
while($results = mysql_fetch_array($raw_results)){

$str = $results['title'];
$keyword = $_GET['query'];
$str = preg_replace("/\w*?$keyword\w*/i", "<mark>$0</mark>", $str);
echo $str;

echo "<br>".$results['text']."<br>";
}
}

else { echo "No results"; }

}
else{ echo "Minimum length is ".$min_length;}
?>

Thanks!

If you're writing new code, **_please_ don't use the `mysql_*` functions**. They are old and broken, were deprecated in PHP 5.5 (which is so old it no longer even receives security updates), and completely removed in PHP 7. Use [`PDO`](https://secure.php.net/manual/en/book.pdo.php) or [`mysqli_*`](https://secure.php.net/manual/en/book.mysqli.php) with _prepared statements_ and _parameter binding_ instead. See http://stackoverflow.com/q/12859942/354577 for details. — ChrisGPT was on strike, Jul 01 '17 at 14:15
Your code is _**wide open** to [SQL injection](https://en.wikipedia.org/wiki/SQL_injection)_. Don't build queries by sticking strings together. Instead, use [prepared statements](http://php.net/manual/en/pdo.prepare.php) with [parameter binding](http://php.net/manual/en/pdostatement.bindparam.php). — ChrisGPT was on strike, Jul 01 '17 at 14:16
I don't understand what your examples are trying to show. Please read [ask]. It is important that your question is as clear as possible. Consider showing us the query string you are trying to match against and a small dataset as a [mcve]. — ChrisGPT was on strike, Jul 01 '17 at 14:18

score 1 · Accepted Answer · answered Jul 01 '17 at 14:19

Hmmm, I think something more like this:

order by ( concat(' ', title, ' ') like concat('% ', $title, ' %') ) desc,
         ( title like concat('%', $title, '%') ) desc,
         ( title rlike replace(title, ' ', '|') ) desc
         locate($title, title)

This does the following ordering:

Exact matches (without punctuation) go first.
Then exact matches with punctuation.
Then partial matches.
Within the first two groups, matches closer to the beginning of the text are preferred.

Sorting the results of a mysql query in php

1 Answers1