mardi 28 juin 2016

how to get similar search results in MySQL?

I am trying to search by words similarity or even if someone made a typo.

For example if anyone searches with picadilly correct one is piccadilly, It should get the result.

I had tried by searching by removing a char from last until 3 chars left using PHP, for example picadilly picadill picadil picadi picad pica pic

It's working but I am also getting unwanted results (due to shorten of keyword to 3 chars) which aren't similar, Also don't think it is a good way, Is there any better approach ?

Table Structure & Data:

+----+---------------------------+------------------------------------------------------+
| id | name                      | address                                              |
+----+---------------------------+------------------------------------------------------+
|  1 | Bethnal Green Station     | Bethnal Green Tube Station, London, E2 0ET           |
|  2 | Westminster Station       | Westminster Tube Station, London, SW1A 2JR           |
|  3 | Goldhawk Road Station     | Goldhawk Road Tube Station, London, W12 8EG          |
|  4 | Piccadilly Circus Station | Piccadilly Circus Tube Station, London, W1J 9HP      |
|  5 | Ravenscourt Park Station  | Ravenscourt Park Tube Station, London, W6 0JJ        |
|  6 | Barons Court Station      | Barons Court Tube Station, London, W14 9EA           |
|  7 | Charing Cross Station     | Charing Cross Tube & Train Station, London, WC2N 6RQ |
|  8 | Hammersmith Station       | Hammersmith Tube Station, London, W6 8AB             |
|  9 | Embankment Station        | Embankment Tube Station, London, WC2N 6NS            |
| 10 | Leicester Square Station  | Leicester Square Tube Station, London, WC2H 0AP      |
+----+---------------------------+------------------------------------------------------+

Query:

SELECT * FROM `stations`
WHERE name like '%picadilly%'
   OR name like '%picadill%'
   OR name like '%picadil%'
   OR name like '%picadi%'
   OR name like '%picad%'
   OR name like '%pica%'
   OR name like '%pic%'

Aucun commentaire:

Enregistrer un commentaire