Sort autocomplete suggestions by first letter in alphabetical order
I want to sort the autocomplete suggestions by first letter alphabetically.
It now looks like this: http://i.imgur.com/EevFyv2.png
If I type the letter C, I want to show the tag suggestions like this:
- FROM
- C ++
- Active Directory
- Apple Script
- BlueCoat
code:
$text = $mysqli->real_escape_string($_GET['term']);
$query = "SELECT * FROM autocomplete WHERE name LIKE '%$text%' ORDER BY name ASC";
$result = $mysqli->query($query);
$json = '[';
$first = true;
while($row = $result->fetch_assoc())
{
if (!$first) { $json .= ','; } else { $first = false; }
$json .= '{"value":"'.$row['name'].'"}';
}
$json .= ']';
echo $json;
I hope someone can help, thanks in advance.
source to share
You will need
SELECT *
FROM autocomplete
WHERE name LIKE '%$text%'
ORDER BY (name LIKE '$text%') DESC,
name ASC
The first ORDER BY clause is the BOOLEAN value, which 1
when the name starts with $text
and 0
otherwise, so when sorting in order, the DESC
values starting with $text
will move to the beginning of the list (and those containing it only elsewhere will be moved to end). The second sentence then sorts both "sublists" alphabetically again.
source to share
Update. ... It sounds like you need sentences that start with the prefix you entered so that it appears first in the list, you will need to use the user sort feature. I have updated the code to match this.
If you want to do PHP sorting, you need to create an array with the results and sort it through a single PHP array sort function . Here's your modified code that uses the function usort()
:
$term = $_GET['term'];
$text = $mysqli->real_escape_string($term);
$query = "SELECT * FROM autocomplete WHERE name LIKE '%$text%' ORDER BY name ASC";
$result = $mysqli->query($query);
$results = array();
while($row = $result->fetch_assoc())
{
$results[] = $row['name'];
}
usort($results, function($item1, $item2) use($term) {
// prefixed items need to go first
$item1HasPrefix = strpos($item1, $term) === 0;
$item2HasPrefix = strpos($item2, $term) === 0;
if($item1HasPrefix && $item2HasPrefix) {
// if both items have the prefix, sort them
return strcmp($item1, $item2);
} elseif($item1HasPrefix) {
return -1;
} elseif($item2HasPrefix) {
return 1;
} else {
return strcmp($item1, $item2);
}
});
echo json_encode($results);
source to share