Hey there,

let's see...

I have a search function on my website and do get a string from a textfield($actor) which I compare with data in my mysql tables.

Let's say I have some rows in my table which consist of 1 field that have the following data:

1 Diaz, Cameron
2 Ford, Harrison
3 Connery, Sean
4 Barrymore, Drew
5 Jolie, Angelina
6 Bloom, Orlando

So, someone simply enters some characters into that textfield ($actor) and the data he entered is being compared with above rows.

Let's say he is looking for Harrison Ford and maybe just enters "Ford" as search query. So far so good.

My SELECT would look like this:

SELECT * FROM table WHERE actor = '$actor';

It won't give any search result yet the searched string does not exactly match the data in my table, so I'm altering the SELECT to this one:

SELECT * FROM table WHERE actor LIKE '%$actor%';

is that right?

NOW, to my problem:

If someone enters two characters, like only "OR" the search result looks like this:

2 Ford, Harrison
4 Barrymore, Drew
6 Bloom, Orlando

But I want Bloom, Orlando to be the FIRST result, since the "OR" is the first part of the surname. I mean, I don't want the script to look for any "or" or at least sort it a different way, so that more precise matches show on top of all results.

The SECOND problem I have, is that I want typing errors to be considered/allowed. So that e.g. someone looks for "ORLANDO BLOM" or "DREW BARRINGMORE" it might still give me the results I want instead of an ERROR or a 0-result.

Is there any way I can do this trick via PHP or is there even a way to do that via a MYSQL SELECT QUERY directly? I looked into regular expressions but I cannot seem to figure out how to do it right.

Thanks in advance!

    Got a question, are you stuck with this data structure or can you break the name into first and last? Might make what your trying to do easier.

      SELECT * FROM actors WHERE actor LIKE '%$actor%'

      is that right?

      Try it ... ... and you'll find out that it is.

      But I want Bloom, Orlando to be the FIRST result, since the "OR" is the first part of the surname. I mean, I don't want the script to look for any "or" or at least sort it a different way, so that more precise matches show on top of all results.

      Basically order by how close to the front of the string the substring is?
      SELECT * FROM actors WHERE actor LIKE '%$actor%' ORDER BY LOCATE($actor, actor)

      The last point you're asking about is a fuzzy search which is quite complicated. I can help you but I'll have to leave it until I finish work.

      HTH
      Bubble

        Originally posted by Buddha443556
        Got a question, are you stuck with this data structure or can you break the name into first and last? Might make what your trying to do easier.

        Hey ya,

        well that name thing was just an example, basically, I do have data in one field that consists of 2 or even more words and I cannot seperate them into different fields since I import the rows from a textfile which has to look the way it is.

          Originally posted by bubblenut
          Try it ... ... and you'll find out that it is.


          Basically order by how close to the front of the string the substring is?
          SELECT * FROM actors WHERE actor LIKE '%$actor%' ORDER BY LOCATE($actor, actor)

          The last point you're asking about is a fuzzy search which is quite complicated. I can help you but I'll have to leave it until I finish work.

          HTH
          Bubble

          Thx bubblenut, I'll look into that LOCATE thingy, never used it before...

          Oh and thx for your offer about the typing error stuff, maybe there's already a sniplet out there which deals with this request?

            Most likely, have a look on htscripts for fuzzy search. Unlikely you'll get something specific to your needs but you'll get the principles and from that you can build your own.

              Write a Reply...