| Author |
 Topic  |
|
|
IfOnly
VP-CART New User
United Kingdom
90 Posts |
 Posted - February 24 2004 : 07:15:54
|
If I want to make sure I amend all products within a subcatergory, how do I get the Edit Products menu to display all the products in that sub.cat.
I have tried choosing subcategoryid, then enter 20(which is the sub cat id, click Display, but no results displayed.
I then tried the name Cricket Balls - nope results displayed.
What am I doing wrong please?
Regards
Phil |
|
|
jonmadrid
VP-CART New User
USA
192 Posts |
Posted - February 24 2004 : 08:11:27
|
Phil,
I think you may need to do an Advanced Query for that because the subcategory ids are stored in a separate table (prodcategories). The products page only pulls data from the products table (as far as I know).
So, here is what you could do:
From the admin menu, choose 'Advanced Query' and enter in the following SQL statement:
*******
SELECT products.catalogid, products.cname, products.cprice, products.cdescription, prodcategories.intcategoryid FROM prodcategories INNER JOIN products ON prodcategories.intcatalogid = products.catalogid WHERE prodcategories.intcategoryid = 25;
*******
Where '25' at the end of the statement is equal to the subcategory number you are searching for. Click 'Run Query' to see your results.
This is just an example and I selected only a few fields to display but you could alter the fields as you like. Just make sure you follow the correct syntax.
Hope that helps.
All the best,
Jon Madrid
--------------------
Madrid Communications
Web Design, Development, and Hosting
www.madridcom.com
|
 |
|
|
IfOnly
VP-CART New User
United Kingdom
90 Posts |
Posted - February 24 2004 : 09:17:06
|
Thanks Jon - spot on with the SQL Query all 19 cricket balls displayed.
Unfortunately there is no 'Edit' option before them; do I then make a written note of the Product numbers and amend them the normal way?
This seems a bit of a basic omission for VP-ASP, surely the more existing businesses add products, they are not going to be in logical number order in the products table, easy to miss some to amend.
The most common I can think of is one supplier say Kookaburra, puts up prices by 10%. I would want to find all bats in the Kookaburra sub cat to amend; they could have added more bats in at various times over the past year and they are not all going to be in one group in the products table.
Am I missing something here, is there another way I should be approaching what I am trying to do?
Regards
Phil
Edited by - IfOnly on February 24 2004 09:26:21 |
|
|
|
jonmadrid
VP-CART New User
USA
192 Posts |
Posted - February 24 2004 : 10:49:53
|
Hi Phil,
I'd have to say I agree with you on the fact that this seems like something that would be a little more easily done. Perhaps I am missing something but, from what I know, it just cant be done with the default cart setup.
Despite the fact that it can't be done via the admin panel, you can do it again with the help of some SQL which you can run to update any group of products in one shot, which may be far easier than manually updating a large number of products.
You mentioned you wanted to update all of a particular product subcategory with price increases of 10%. Well, if you have as little as 50 products to update manually, that could take a good amount of time to do. The alternative?... Just execute some SQL to update all of the products in that subcategory.
Here is an example:
*******
UPDATE products INNER JOIN prodcategories ON products.catalogid = prodcategories.intcatalogid SET products.cprice = (cprice+(cprice*0.1))
WHERE ((prodcategories.intcategoryid)=25);
*******
Again, where 25 = the subcategory id of the products you want to update. Notice also that I am updating the price by adding 10% to the current value of the price. Imagine, if you have 1000 products to update with price increases of 10%, you could do it all in one shot just like this. Forget about updating each one manually and all the time that would take.
There are a number of ways to execute this code:
1) Save it to a text file and use the convertsql.asp file to run it. Or,
2) Write a quick little bit of code in shop$db.asp to process it, then call any page in the cart and the information will be updated.
Either way you do it, you will obviously want to remove the code (whether in the form of a file or hard code) from your site once you are done so that it can't be run again. If you use the convertsql.asp route, be absolutley sure you remove that file when you're done. Otherwise your site will be wide open to arbitrary SQL executions run by anyone who pleases.
As I mentioned, there may be something built into the cart that I am missing but I haven't seen anything that handles procuts and subcategories in the way you need it to. I just don't know about it and definitely haven't seen it. This is actually probably the preferred method for updating large quantities of data anwyay since you can do it in just one shot. Nothing like automation!
Hope that helps.
All the best,
Jon Madrid
--------------------
Madrid Communications
Web Design, Development, and Hosting
www.madridcom.com
|
|
|
|
IfOnly
VP-CART New User
United Kingdom
90 Posts |
Posted - February 25 2004 : 04:07:15
|
What can I say Jon - fantastic thanks.
I will keep this for future reference.
I just hope VP-ASP support are on the case for this, I can't imagine many users (nor all web designers, hence us buying the software) being able to produce the SQL statements, you come up with.
Something needs intergrating to the Admin menu for this basic requirement to show/edit all products in a sub cat.
Regards
Phil |
|
|
|
greatphoto
VP-CART Super User
USA
304 Posts |
Posted - April 16 2004 : 21:00:58
|
Suggestion for the moderator: Move or link this thread into the "Suggestions for future versions" topic.
|
|
|
| |
Topic |
|
All Forums
Amending all products in a sub catergory
New Topic
Printer Friendly
TrustGuard - PCI Security Scanner
