如何使用多个值来爆炸字段中的单词？

Question

I have this table: TABLE_ELEMENTS, with the name ELEMENTS, i have a multiple values inside, see image.

This is the php and return the results from autocomplete request.

$("#autocomplete").autocomplete({
    source: "http://localhost/include/autocomplete.php?type=mauto_complete",

First i call this..

if(isset($_GET['type']) && in_array($_GET['type'], $arr_action)) $type=$_GET['type']; 

if($type == "mauto_complete") {
    require_once $config_abs_path."/autocomplete/autocomplete.php";
    if(isset($_GET['term'])) {
    $term = escape($_GET['term']);
    $response = mauto_complete::getAutocomplete($term);
    echo json_encode($response);
}

}

And this is the secondauto.php file function getAutocomplete($term) {

    global $db;
    global $config_table_prefix;
    global $crt_lang;

    $elements=$db->fetchRow("select Distinct `elements` from TABLE_ELEMENTS where `elements` like '$term%' limit 10");
    $elements_array = explode("|", $elements);
    return $elements_array;
}

I have write this after select

$elements_array = explode("|", $elements);

Ok the request is working fine, but in autocomplete results when i type the word Building i take no words. But when i type the first word of the elements ( Apartment ) i take all words.

The words is not uniqe

Answer 1

A common approach to this is to add a | to the left of the field, then search that. This ensures that an element containing the search doesn't get matched.

select
    Distinct `elements`
from
    TABLE_ELEMENTS
where
    lower(CONCAT('|', `elements`)) LIKE lower('%|$term%')

However, you're probably looking for something else. Below is how I'd approach it. I couldn't figure out what library you were using for your connection, so you may have to change a little bit for it to work for you.

function getAutocomplete($name, $term)
{
    // make sure you escape the string to avoid SQL injection
    $name = mysqli_real_escape_string($db, $name);

    // make the searches case-insensitive
    $term = strtolower($term);

    // fetch the valid elements for the field and split them using explode
    $elements = $db->fetchRow("SELECT `elements` FROM `TABLE_ELEMENTS` WHERE `name` = '$name'");
    $elements_array = explode('|', $elements);

    // make an array to save the matching elements
    $filtered = array();

    // iterate over each element to check for a match
    foreach($elements_array as $element)
    {
        // check to see if the beginning of the element starts with the search term
        if(strpos(strtolower($element), $term) === 0)
        {
            // add it to the filtered array
            $filtered[] = $element;
        }
    }

    // return the matching results
    return $filtered;
}

Then to use it, specify what field you want to autocomplete for:

print_r(getAutocomplete('Property Type', 'B'));

// Outputs: Array
// (
//     [0] => Building
//     [1] => Bungalow
//     [2] => Business
// )

To make your existing code to use it, change your JavaScript to match the following. You'll need to change name depending on what field you're autocompleting.

$("#autocomplete").autocomplete({
    source: "http://localhost/include/autocomplete.php?type=mauto_complete&name=Property%20Type"
});

Then update the file where you call the getAutocomplete function:

if(isset($_GET['type']) && in_array($_GET['type'], $arr_action)) $type=$_GET['type']; 

if($type == "mauto_complete") {
    require_once $config_abs_path."/autocomplete/autocomplete.php";
    if(isset($_GET['term']) && isset($_GET['name'])) {
        $name = $_GET['name'];
        $term = $_GET['term'];
        $response = mauto_complete::getAutocomplete($name, $term);
        echo json_encode($response);
    }
}