MySQL - Using LIKE ? With Multiple Columns Search

Question

I've had a look around Stackoverflow and can't seem to find what I am looking for. I have a dynamically updating AJAX search form which shows location data from database.

The issue I am having is with this query here:

 $sql = "SELECT location FROM location_data WHERE location LIKE ? LIMIT 10";

Let me explain what is happening first. There are 3 different columns in a database table, one called location, one called CRS and one called tiploc. I would like to display results like the following:

Select location FROM location_data WHERE location(textbox) is LIKE ?(what the person typed in) OR CRS is LIKE ? or TIPLOC is LIKE ?

Now i've only tried it with CRS so far, and ive done the following query:

 $sql = "SELECT location FROM location_data WHERE location OR CRS LIKE ? LIMIT 10";

The above only displays the CRS result (exact match) and doesn't provide any suggestions for location, only shows CRS. Does anyone know how I can amend my query, so that it searches both location and CRS and TIPLOC, LIKE on location, but exact match only on CRS and TIPLOC?

if(isset($_REQUEST['term'])){
    // Prepare a select statement
    $sql = "SELECT location FROM location_data WHERE location LIKE ? LIMIT 10";

    if($stmt = mysqli_prepare($link, $sql)){
        // Bind variables to the prepared statement as parameters
        mysqli_stmt_bind_param($stmt, "s", $param_term);

        // Set parameters
        $param_term = $_REQUEST['term'] . '%';

        // Attempt to execute the prepared statement
        if(mysqli_stmt_execute($stmt)){
            $result = mysqli_stmt_get_result($stmt);

            // Check number of rows in the result set
            if(mysqli_num_rows($result) > 0){
                // Fetch result rows as an associative array
                while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
                    echo "<p>" . $row["location"] . "</p>";
                }
            } else{
                echo "<p>No matches found</p>";
            }
        } else{
            echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
        }
    }

    // Close statement
    mysqli_stmt_close($stmt);
}

// close connection
mysqli_close($link);

Now heres the on page search, the field I am pulling input from is called "location".

--Code for JS AJAX Search--

        <script type="text/javascript">
        $(document).ready(function(){
        $('.search-box input[type="text"]').on("keyup input", function(){
        /* Get input value on change */
        $(".result").show();
        var inputVal = $(this).val();
        var resultDropdown = $(this).siblings(".result");
        if(inputVal.length >2){
            $.get("backend-search.php", {term: inputVal}).done(function(data){
                // Display the returned data in browser
                resultDropdown.html(data);
          });
        } else{
            resultDropdown.empty();
        }

        });
    // Set search input value on click of result item
        $(document).on("click", ".result p", function(){
        $(this).parents(".search-box").find('input[type="text"]').val($(this).text());
        $(this).parent(".result").empty();
    });
        });
$(document).click(function(){
$(".result").hide();
});
</script>

Answer 1

For every individual expression in OR, you have to specify their comparison conditions. Note the location LIKE ? instead of LOCATION OR:

 $sql = "SELECT location 
         FROM location_data 
         WHERE location LIKE ? 
           OR CRS LIKE ? 
           OR TIPLOC LIKE ? 
         LIMIT 10";

Note: LIMIT clause without ORDER BY is non-deterministic in nature, since MySQL stores an unordered dataset. It basically means that, any 10 rows can be returned by MySQL (if not using ORDER BY).

Answer 2

You need to repeat the LIKE expression for each column.

$sql = "SELECT location 
        FROM location_data 
        WHERE location LIKE ? OR CRS LIKE ? OR TIPLOC LIKE ? 
        LIMIT 10";

And since there are now 3 placeholders in the query, you need to fill them all in with the binding:

mysqli_stmt_bind_param($stmt, "sss", $param_term, $param_term, $param_term);