I have now finished coding for my price comparison. For this I get the data from different XML files and modify the titles because the only way to make a price comparison is by the title. There is no EAN, ID or something like this provided by the shops...
But anyway. My problem actual is that I have multiple titles per shop. Every URL is unique, of course. But because I'm modifying the titles I get multiple titles per shop while they have different URLs and and different "products". They are not real different products but different version like "Premium Edition", "Game of The Year Edition", "Legion Edition".
My problem is that not every shop is naming the editions like the real name or I have a game which is a Gift and other on is a simple normal game but the tribe is still the real name. For example:
Product 1: Battlefield 3 Premium Steam Key
Product 2: Battlefield 3 Premium Steam Gift
So what I am doing is, I remove the words like "Steam", "Key", "Gift" and so on. But while I'm doing this I end up in a price comparison which is based on the name contains multiples entries by the same shop which could be very confusing for the customers. The next thing is that they are also priced the same.
I store every product in a database. To increase the number of fail matches, like above, I want to see which shop is containing the same product several times. Heeded that every URL is unique. So that could be the kicker in my problem.
Does anybody know how I can solve this problem and see with SQL Statements, firstly how many matches by the title I have considering the url. So I now how many entries I have per product page. And secondly how could I solve this problem? - I want only one entry per shop per product.
Here a some different SQL Statements I tried. But my problem is I do not know how I could interpret them. I do not get which number is displaying how many matches I have and so on... :/
SQL Attributes: name, price, shop, url, publisher, platform and category
Statement 1:
SELECT `name`, `shop`, Count(*) as cnt FROM `wp_all_import_xml` GROUP BY `name`, `shop` HAVING cnt>1
Statement 2:
SELECT `name`, `price`, `shop`, Count(*) as cnt FROM `wp_all_import_xml` GROUP BY `name`, `price`, `shop` HAVING cnt>1
Statement 3:
SELECT `name`, `price`, Count(*) as cnt FROM `wp_all_import_xml` GROUP BY `name`, `price` HAVING cnt>1
Greetings and Thank You!