0

I am making a price comparing website. I have data from different stores, but I could not find any way to map similar products to single product.

I have a table 'product1' like this .

  id       title                                               brandName    
|  8986 | Samsung Galaxy Ace                                 | Samsung   |       1 |
|  8983 | Samsung Galaxy Ace                                 | Samsung   |       1 |
|  6048 | Samsung Galaxy Ace                                 | Samsung   |       1 |
|  6109 | Samsung Galaxy Ace                                 | Samsung   |       1 |
|  9840 | Samsung Galaxy Ace Duos                            | Samsung   |       1 |
|  8905 | Samsung Galaxy Ace Duos                            | Samsung   |       1 |
|  9097 | Samsung Galaxy Ace Duos                            | Samsung   |       1 |
|   765 | Samsung galaxy Ace Duos S6802 ( Black)             | Samsung   |       2 |
|   766 | Samsung Galaxy Ace Duos S6802 (Chic White)         | Samsung   |       2 |
| 11880 | Samsung Galaxy Ace NXT                             | Samsung   |       1 |
| 11218 | Samsung Galaxy Ace NXT                             | Samsung   |       1 |
|   323 | Samsung Galaxy Ace Nxt G313H Black                 | Samsung   |       2 |
|   311 | Samsung Galaxy Ace Nxt G313H White                 | Samsung   |       2 |
|  9099 | Samsung Galaxy Ace Plus                            | Samsung   |       1 |
|  1153 | Samsung Galaxy Ace Plus S7500 Dark Blue            | Samsung   |       2 |

Now I want to map similar products; for example I need results like

title                                  brandIDs
Samsung Galaxy Ace             8986 , 8983 , 6048 , 6109
Samsung Galaxy Ace Duos        9840 , 8905 ,9097 ,  765 , 766
Samsung Galaxy Ace NXT         11880 , 11218 , 323 , 311
Samsung Galaxy Ace Plus        9099 , 1153

Is there any programming logic or mysql which can give me desired result? I tried different mysql clause i.e LIKE'%%' but it returns me whole data.

Table schema :

CREATE TABLEproduct1( idint(11) NOT NULL, titlevarchar(50) NOT NULL, brandNamevarchar(50) NOT NULL, PRIMARY KEY (id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--

INSERT INTO `product1` VALUES (8986, 'Samsung Galaxy Ace', 'Samsung');
INSERT INTO `product1` VALUES (8983, 'Samsung Galaxy Ace', 'Samsung');
INSERT INTO `product1` VALUES (6048, 'Samsung Galaxy Ace', 'Samsung');
INSERT INTO `product1` VALUES (6109, 'Samsung Galaxy Ace', 'Samsung');
INSERT INTO `product1` VALUES (9840, 'Samsung Galaxy Ace Duos', 'Samsung');
INSERT INTO `product1` VALUES (8905, 'Samsung Galaxy Ace Duos', 'Samsung');
INSERT INTO `product1` VALUES (9097, 'Samsung Galaxy Ace Duos', 'Samsung');
INSERT INTO `product1` VALUES (765, 'Samsung galaxy Ace Duos S6802 ( Black)', 'Samsung');
INSERT INTO `product1` VALUES (766, 'Samsung Galaxy Ace Duos S6802 (Chic White)', 'Samsung');
INSERT INTO `product1` VALUES (11880, 'Samsung Galaxy Ace NXT', 'Samsung');
INSERT INTO `product1` VALUES (11218, 'Samsung Galaxy Ace NXT', 'Samsung');
INSERT INTO `product1` VALUES (323, 'Samsung Galaxy Ace Nxt G313H Black', 'Samsung');
INSERT INTO `product1` VALUES (311, 'Samsung Galaxy Ace Nxt G313H White', 'Samsung');
INSERT INTO `product1` VALUES (9099, 'Samsung Galaxy Ace Plus', 'Samsung');
INSERT INTO `product1` VALUES (1153, 'Samsung Galaxy Ace Plus S7500 Dark Blue', 'Samsung');

Can any body please tell me how can i achieve desired result in mysql.

0

The basics you can do with a simple GROUP BY:

select
 title,
 group_concat(id) as brandIDs
from product1
group by title;

http://sqlfiddle.com/#!9/87532/1

But it will group only products with exactly the same title, no way to simply say in SQL that Samsung Galaxy Ace Nxt G313H Black and Samsung Galaxy Ace Nxt G313H White are the same.

You should normalize your table. You can use a separate table of brands which will save space from the multiplication of the names. And you can add some "hierarchy" of brand->series->product like Samsung->Galaxy Ace Nxt->G313H Black to make these queries easier.

Edit: I tried to get exactly the results you show with MySQL string functions:

select
 substring_index(title,' ',4),
 group_concat(id) as brandIDs
from product1
group by substring_index(title,' ',4);

http://sqlfiddle.com/#!9/87532/6

  • Hi @jkavalik . Thanks for the valuable answer . But isn't there any way to get data separated from spaces using SQL as you mentioned in your answer ? I am trying to solve this problem from last week but did not get anything useful . although you have given me perfect direction . – Deepak Rathore Oct 16 '15 at 12:05
  • There are String functions available in MySQL and with them you might define some expression for the GROUP BY to do what you want. But it would probably be easier to do that in php/python/java or any other language you use. – jkavalik Oct 16 '15 at 12:08
  • @DeepakRathore added a query using substring_index to give the exact results you wanted. – jkavalik Oct 16 '15 at 13:13
  • Hey man . You are super man for me . you made my day . Edit part is the solution of my problem . A big thank to you . Thanks man @jkavalik – Deepak Rathore Oct 16 '15 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.