Hi David,
I have been busy trying to figure out the best way to go forward in terms of master categories and mapping them to the existing merchant categories.
Merchant categories do not change very often, so I have created 16 "master categories" I.E. "books", "computers" and so on... and will then manually map the merchant categories to the master categories.
One of the reasons for doing this is that the Price Comparison store I run covers a very wide range of items from lingerie to computers. Currently there was no way to search specific "master categories" or infact create specialised sub-stores for the likes of lingerie and computers. By doing this I am able to create 16 specalised stores with the need to create 16 seperate PC stores with 16 databases. It also of course addresses the issue of a merchant which sells items which are not related. I.E. John Lewis which sell lingerie and computers!
Any thoughts from any users would be much appreciated!
Hi,
I think you can use MySQL's INSERT-SELECT feature to do this:
http://dev.mysql.com/doc/refman/4.1/en/insert-select.html
I guess the query would look something like:
INSERT INTO table2 (fieldName) SELECT fieldName FROM table1Cheers!
David.
Hi David,
I have just completed the back-end system for creating User defined categories. This will enable the administrator to create Master Categories, Sub Categories and Sub Sub Categories and then map the Sub Sub categories to the merchants categories.
The obvious advantages is that it will give the site a category structure which is similar to those of the other PC sites, making items easy to find by moving through the categories.
This also of course addresses the issues of some feeds having products in more than one department. These can now be easily mapped to the correct category.
The final stage of this is to integrate the new category structure in to PT. This is where I am struggling as I am unsure where to join the queries so that the new categories are used.
I wonder if you would be able to point me in the right direction please?
Thanks,
Simon
Hi Simon,
The SQL is constructed in search.php depending on the type of search indicated by the query; for exaple:
foo (just search for "foo" - the default case)
merchant:bar (all products from merchant "bar")
merchant:bar:foo (search for "foo" in merchant "bar")
...ditto for category: and brand:
The query is first split on the ":" character into the $parts array, followed by a switch statement on $parts[0] to decide which type of SQL statement to construct (line 34 in the distribution).
Now, you will notice from the switch statement that "merchant", "category" and "brand" are all handled by the same block of code; all the changes is the field name that is used in the WHERE clause.
In your case; I would suggest that you split out the category case so that you can write a block of code specifically for the way you are storing categories, so the swtich { } construct would look like this (outline)
switch($parts[0])
{
case "merchant":
case "brand":
// existing code
break;
case "category":
// ***** YOUR NEW CODE HERE *****
break;
case "bw":
// existing code
break;
default:
// existing code
break;
}Within your section; you need to generate the following variables:
$sql (this contains the SQL to select products based on the category)
$sqlResultCount (this contains the SQL to count the total products that would be returned)
$parts[1] would contain the category as passed through from the category index pages; but how you do this would also have to be modified along with your new structure if you have not already done this...
Hope this helps,
Cheers,
David.
Hi David,
Thanks for your quick response.
I had a thought in the car this morning..
Would it do the same thing if I utelised the existing "categories" table which is at the moment used to map alternate names for categories?
Simon.
Hi Simon,
I don't think it would make any difference as the categories table is only used during import - in other words category mapping is not real time - so you'd still have to write new code to handle your new database structures.
Cheers,
David.
Hi David,
In your post above...
$parts[1] would contain the category as passed through from the category index pages; but how you do this would also have to be modified along with your new structure if you have not already done this...Thanks,
Simon
Ah - sorry, I mean search.php where the query is split up into parts, separated by the ":" character.
categories.php is a more straight forward script; which just does a SELECT DISTINCT(category) from the products table to generate the category a-z list. In your case; this would probably need to be replaced with code to reflect your new category tables...
Cheers,
David.
Hi David,
I am re-constructing the "$sql" query in search.php for the new categories.
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name";I have managed to do the initial query but have not yet managed to include the [WHERE ".$where."] part of the code. The new category structure has meant I have had to add an additional two tables, one to map the new categories to the merchant categories and the other contains the new category structure. The new query looks like this, which works (i'm aware that some data is missing)
$sql = "SELECT name , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, category_name AS category FROM products, categories_mapped, categories_categories WHERE products.category = categories_categories.merchant_category_name AND categories_categories.category_id = categories_mapped.category_id GROUP BY name";My question is that to include the [WHERE ".$where."] in the above query to get the products from the necessary categories, will I need to do a sub-query on the same table or is there an easier method that I could use?
Thanks,
Simon
Hi Simon,
Obviously I can't test this; but I think the place to insert the condition is right up front of your where clause; followed by AND; for example:
$sql = "SELECT name , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, category_name AS category FROM products, categories_mapped, categories_categories WHERE ".$where." AND products.category = categories_categories.merchant_category_name AND categories_categories.category_id = categories_mapped.category_id GROUP BY name";If that doesn't work, I would suggest using a print statement to echo the $sql that is being generated and then use the query in something like phpMyAdmin which will let you see what's going on...
Cheers,
David.
Hi David,
Thanks for all your help again.
It is up and running, just have one last question.
I managed to get it working by hard coding the ".$parts[0]." to category_name in the sql query as this represents the new categories. I couldn't figure out how to change it from "category" to "category_name"
$where = "'".database_safe($parts[1])."'";$sql = "SELECT *, MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM products, categories_mapped, categories_categories WHERE category_name = ".$where." AND products.category = categories_categories.merchant_category_name AND categories_categories.category_id = categories_mapped.category_id GROUP BY name";Where do I change this please?
Hi Simon,
I don't quite follow i'm afraid... can you post the before and after SQL and i'll see if I can help....
Cheers,
David.
Hi David,
In this code from search.php
$where = "".$parts[0]." = '".database_safe($parts[1])."'";for a category search [$parts[0] = "category"] How / where do I change it so that [$parts[0] = "category_name"]
Thanks,
Simon
Ah - I understand.
If you have created a separate case in the switch() statment for the category search (as suggested above) then I would leave this hard coded as there's no need for it to be based on $parts[0]. In fact, better still would be to do away with that line altogether and just incorporate category_name = ".$parts[1]." straight into the SQL; but if it's working as it is I wouldn't worry too much.
The only reason for this line is because originally the same block of code is used for all merchant, category and brand searches and this statement makes the SQL use the correct field.
Cheers,
David.
Hi David,
I now have this mod up and running and seems to be working very well!
I have also just read your post about future upgrades and will be more than willing to share this mod with you if you would like to include it in the next major upgrade?
I still need to work on the sites main page to reflect these categories, but that is really just a matter of time.
The working categories can be seen here New Category Mapping
Thanks again for all your help!
Simon.
Hi David,
Is there an easy way to copy a fields data from one table to another within the same database? Both fields name are the same!
Computer Hardware