php and mysql live search

Nick

I'm currently working on a live search that displays results directly from a mysql db.

The code works, but not really as i want it.

Let's start with an example so that it is easier to understand:

My database has 5 columns:

id, link, description, try, keywords

The script that runs the ajax request on key up is the following:

$("#searchid").keyup(function () {
    var searchid = encodeURIComponent($.trim($(this).val()));
    var dataString = 'search=' + searchid;
        if (searchid != '') {
           $.ajax({
              type: "POST",
              url: "results.php",
              data: dataString,
              cache: false,
              success: function (html) {
                 $("#result").html(html).show();
              }
           });
         }
        return false;
    });
});

on the results.php file looks like this:

if ($db->connect_errno > 0) {
    die('Unable to connect to database [' . $db->connect_error . ']');
}

if ($_REQUEST) {
    $q = $_REQUEST['search'];

    $sql_res = "select link, description, resources, keyword from _db where description like '%$q%' or keyword like '%$q%'";
    $result = mysqli_query($db, $sql_res) or die(mysqli_error($db));

    if (mysqli_num_rows($result) == 0) {
        $display = '<div id="explainMessage" class="explainMessage">Sorry, no results found</div>';
        echo $display;
    } else {
        while ($row = $result->fetch_assoc()) {
            $link = $row['link'];
            $description = $row['description'];
            $keyword = $row['keyword'];
            $b_description = '<strong>' . $q . '</strong>';
            $b_keyword = '<strong>' . $q . '</strong>';
            $final_description = str_ireplace($q, $b_description, $description);
            $final_keyword = str_ireplace($q, $b_keyword, $keyword);

            $display = '<div class="results" id="dbResults">
                    <div>
                        <div class="center"><span class="">Description :</span><span class="displayResult">' . $final_description . '</span></div>
                        <div class="right"><span class="">Keyword :</span><span class="displayResult">' . $final_keyword . '</span></div>
                    </div>
                    <hr>
                </div>
                </div>';
            echo $display;
        }

    }
}

now, let's say that i have this row in my DB:

id = 1
link = google.com
description = it's google
totry = 0
keywords: google, test, search

if i type in the search bar:

google, test

i have the right result, but if i type:

test, google

i have no results, as obviously the order is wrong. So basically, what o'd like to achieve is something a bit more like "tags", so that i can search for the right keywords without having to use the right order.

Can i do it with my current code (if yes, how?) or i need to change something?

thanks in advance for any suggestion.

PS: I know this is not the best way to read from a DB as it has some security issues, i'm going to change it later as this is an old script that i wrote ages ago, i'm more interested in have this to work properly, and i'm going to change method after.

Dimag Kharab

Untested code but modify according to your needs,

$q = $_REQUEST['search'];

$q_comma = explode(",", $q);

$where_in_set = '';

$count = count($q_comma);

foreach( $q_comma as $q)
{
    $counter++;
    if($counter == $count) {
         $where_in_set .= "FIND_IN_SET('$q','keywords')";
     }else {
         $where_in_set .= "FIND_IN_SET('$q','keywords') OR ";
     }
}

$sql_res = "select link, description, resources, keyword from _db where $where_in_set or description like '%$q%'";

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related