php and mysql live search

Question

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.

score 2 · Answer 1 · answered Feb 01 '16 at 12:29

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).

I solved this using the answer i selected, but i think i'm going to give this solution a try as well. Thanks! — Nick, Feb 01 '16 at 12:37

Dimag Kharab · Accepted Answer · 2016-02-01T11:49:55.880

1

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

edited Feb 01 '16 at 11:49

answered Feb 01 '16 at 10:21

Dimag Kharab

4,439
1
24
45

with this example, when i try to search i get this message: Unknown column '$q' in 'where clause' – Nick Feb 01 '16 at 10:47
@Nick check my edit now , i used single quote that was the problem – Dimag Kharab Feb 01 '16 at 10:48
ok, now the message is a bit different...if i type "test" i get Unknown column 'test' in 'where clause' – Nick Feb 01 '16 at 10:50
echo your $sql_res and provide – Dimag Kharab Feb 01 '16 at 10:57
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/102226/discussion-between-n01ze-and-nick). – Dimag Kharab Feb 01 '16 at 10:58
select link, description, keyword from _db where FIND_IN_SET(test,keywords) or description like '%test%' – Nick Feb 01 '16 at 10:58

score 0 · Answer 3 · answered Feb 01 '16 at 10:15

There are 2 solutions I can think of:

Use fulltext index and search.
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.

score 0 · Answer 4 · answered Feb 01 '16 at 10:19

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

score 0 · Answer 5 · answered Feb 01 '16 at 10:22

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".

score 0 · Answer 6 · edited May 23 '17 at 12:23

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

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.

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

How can i use an html autocompleter if i have already everything stored into a database? I mean, how would you actually do this using autocompleter with data from the db? do you have any example? — Nick, Feb 01 '16 at 10:41
You can select and send your data as json to html. And you can write a search function which is using this json data. — hurricane, Feb 01 '16 at 12:00

php and mysql live search

6 Answers6