PHP MYSQL TABLE COMPARISON

Question

I have two tables in my database, and I'm trying to compare phone prices from both db's. The phone models are the same, but have different spelling (as you see on table1 (model) and table2 (model)). Actually I want to check if table2 has cheaper model as table1 and output it as MODEL, PRICE1, PRICE2.

TABLE1
id  date        manufac     model           price
1   2016-06-26  SAMSUNG     A3 A300F BLACK  187
2   2016-06-26  SAMSUNG     A5 A500H BLACK  239
3   2016-06-26  SAMSUNG     J5 J300F GOLD   185


TABLE2
id  date        manufac     model           price
1   2016-06-26  SAMSUNG     A300F A3        180
2   2016-06-26  SAMSUNG     A500H A5        232
3   2016-06-26  SAMSUNG     J300F J5 GOLD   172

I've tried using the following query with no success:

SELECT table1.model AS "MODEL",
       table1.price AS "PRICE TABLE1",
       table2.price AS "PRICE TABLE2" 
FROM   table1, table2 
WHERE  table1.model LIKE CONCAT('%', table2.model, '%');

Have you guys got any ideas how to solve this issue?

The approach for best results would be to search for best similarity. For that check this question: http://stackoverflow.com/questions/5322917/how-to-compute-similarity-between-two-strings-in-mysql — Edu, Jun 27 '16 at 20:30

score 1 · Answer 1 · answered Jun 27 '16 at 20:43

There is no way to write your query that will always work. The problem is that the 'model' holds two or three different values in a single string type field.

The proper way to do this would be to have "model", "color", and potentially "sub_model" fields. When you want to compare between models, you would then do a strict equality expression:

SELECT table1.model AS "MODEL",table1.price AS "PRICE TABLE1",table2.price AS "PRICE TABLE2" FROM table1,table2 WHERE table1.model = table2.model

With a table that now looks like this:

TABLE1
id  date        manufac     model     color      price
1   2016-06-26  SAMSUNG     A300F     BLACK      187
2   2016-06-26  SAMSUNG     A500H     BLACK      239
3   2016-06-26  SAMSUNG     J300F     GOLD       185


TABLE2
id  date        manufac     model     color      price
1   2016-06-26  SAMSUNG     A300F                180
2   2016-06-26  SAMSUNG     A500H                232
3   2016-06-26  SAMSUNG     J300F     GOLD       172

You will have to perform a data cleanup to separate these fields into the new fields, then make sure they are also separated on all new rows.

PHP MYSQL TABLE COMPARISON

1 Answers1