CODEWITHSUNDEEP
phpmysqlajax
Nick
Nick

Reputation: 14283

php and mysql live search

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.

Upvotes: 3

Views: 626

Answers (6)

symcbean
symcbean

Reputation: 48387

Normalize your schema

The rules of relational database are very simple (at least the first three).

keywords: google, test, search

...breaks the second rule. Each keyword should be in its own row in a related table. Then you can simply write your query as....

SELECT link, description, resources, keyword 
FROM _db 
INNER JOIN keywords 
ON _db.id=keywords.db_id
WHERE keyword.value IN (" . atomize($q) . ")

(where atomize explodes the query string, applies mysqli_escape_paramter() to each entry to avoid breaking your code, encloses each term in single quotes and concatenates the result).

Alternatively you could use MySQL's full text indexing which does this for you transparently.

Although hurricane makes some good points in his/her answer, they do not mention that none of the solutions proposed there does not scale to handle large volumes of data with any efficiency (decomposing the field into a new table/using full text indexing does).

Upvotes: 2

Dimag Kharab
Dimag Kharab

Reputation: 4519

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%'";

Upvotes: 1

hurricane
hurricane

Reputation: 6734

First of all as you say it is not a good way to do it. I think you are writing a autocompleter.

  1. Seperators for words

"google, test" or "test, google" is a attached words. First you need to define a seperator for users. Usually it is a whitespace ' '.

When you define it you need to split words.

$words = explode(" ",$q);
// now you get two words "google," and "test"

Then you need to create a sql which gives you multiple search chance.

There are a lot example in MySQL LIKE IN()?

Now you get your result.

  1. Text similarity

Select all result from db and in a while search a text from another text. It gives you a dobule point for similarity. Best result is your result.

Php Similarity Example

Important Info

If you ask my opinion don't use it like that bcs it is very expensive. Use autocompleters on html side. Here is an example

Upvotes: 0

Ting Sun
Ting Sun

Reputation: 316

Not an ideal solution, but instead of treating your search datastring as one element, you can have php treat it as an array of keywords separated by a comma (by using explode). You'd then build a query depending on how many keywords were sent.

For example, using "google, test" your query would be:

$sql_res = "select link, description, resources, keyword from _db where (description like '%$q1%' or keyword like '%$q1%') AND (description like '%$q2%' or keyword like '%$q2%')";

Where $q1 and $q2 are "google" and "test".

Upvotes: 0

Zamrony P. Juhara
Zamrony P. Juhara

Reputation: 5262

LIKE '%google, test%' will match id=1 but not '%google,test%' (no space between coma) nor '%google test%' (space delimiter) nor '%test, google%'. Put each keyword as separate table or you can split input keywords into several single keyword and use OR operator such as LIKE 'google%' OR LIKE 'test%'

Upvotes: 0

Shadow
Shadow

Reputation: 34305

There are 2 solutions I can think of:

  1. Use fulltext index and search.

  2. You can split the search string into words in php for example using explode() and serach for the words not in a single serach criteria, but in separate ones. This latter one can be very resource intensive, since you are seraching in multiple fields.

Upvotes: 0

Related Questions