Best way to group products

[oracle765]

Hi Professionals

 

We are trying to compare hotel prices like trivago do but the problem is there are slightly different hotel names but exactly the same hotel that we are pulling from the hotel providers as shown in the following request result

 

 

Any ideas if there could be a way around this, obviously trivago have managed it somehow but there are literally hundreds of hotels we are gathering at any one time

 

 

<<< Hotels duplicate details:-

 

hotel:- 1:-

 

[skoosh_id] => 

    [expedia_id] => exp123

    [getaroom_id] => get123

    [name] => Napa Prince Hotel Apartments

    [address1] => Tefkrou Anthia 65

    [city] => Ayia Napa

    [latitude] => 34.99267

    [longitude] => 33.99417

    [usersession_id] => user458492685

    [total_apis] => 2

 

 

hotel:- 2:-

 

[skoosh_id] => 

    [expedia_id] => 

    [lateroom_id] => late123

    [getaroom_id] => 

    [name] => Napa Prince Hotel Apts

    [address1] => Tefkrou Anthia St. 65, P.O.Box 30239 Ayia Napa 5342

    [city] => Ayia Napa

    [latitude] => 34.99288

    [longitude] => 33.99402

    [usersession_id] => user458492685

Edited March 18, 2016 by oracle765

[Barand]

You may want to look at string comparison functions such as

 

• soundex()
• levenshtein()
• metaphone()

[oracle765]

levenshtein()

with
$words  = array('apts','apartments','hotel');

To try and match up say hotels like the following

Napa Prince Hotel Apartments
Napa Prince Hotel
napa Prince Hotel apts

If = Napa Prince Hotel and there is another with Napa Prince Hotel + $words then it could be a match

am I totally going of track here

Hi Barand I hope you are well

 

Thanks for this I have worked on it for days now but just cannot get my head around it

 

I am trying things like

[Barand]

You could try proximity searches

SELECT 
  a.hotelname as hotela
, b.hotelname as hotelb
FROM hotel a
JOIN hotel b
    ON a.hotel_id <> b.hotel_id
    AND ABS(a.latitude-b.latitude) < 0.0001
    AND ABS(a.longitude-b.longitude) < 0.0001