How do I change Categories for multiple products easily?

[dave204]

Hi there!
I have imported an Excel file with 2,500 products.
Everything has gone where it should - except the all the products are in the Home Category, whereas I wanted them in "mobile phone accessories".
I've moved a few hundred manually, buts its a long, laborious task - does anyone know a quick method to move all of them more easily? Is there maybe a module out there which lets me tick all the boxes at one go, such as a complete list of products in excel style?
Any help would be much appreciated!
David Fergus

www.electronicsshopdirect.com

[tomerg3]

You can do it directly in the database (if you want to move all of the products).
Find out the ID of the category you want to make as default.
In phpMyadmin, open a SQL query page (in the PS database) and run the following query

"UPDATE ps_product SET id_category_default = 'x'" replace x with the new category id.

[dave204]

Thanks for the information.
However, I just want to move all the products in the "Home" Category to the "mobile phone accessories" Category - will this method work for that, or will it put EVERY product (ie from all other Categories) into the mobile one?

As I've said, I just want all the products in the Home Category to move..

I don't want to make the SQL query UNTIL I've clarified this point, as you will appreciate!!

Awaiting your reply,

David Fergus

www.electronicsshopdirect.com

[tomerg3]

Then use this one :

"UPDATE ps_product SET id_category_default = ‘x’ where id_category_default”.

P.S
please no questions in PM, that's the purpose of the forum

[dave204]

Thanks for reply - and I won't use PM with you again - but what's the difference? The 2 statements look identical..

Or did you intend to write something else for the "X" value?

David

[dave204]

Hi again!
I think you intended to write something about "id_category_default", but didn't complete the sentence??

[tomerg3]

yeah, sorry about that, it should read

“UPDATE ps_product SET id_category_default = ‘x’ where id_category_default = 1”

[dave204]

So just to be absolutely sure, I query using the full string (except for the "x", of course!)

UPDATE ps_product SET id_category_default = ‘x’ where id_category_default = 1

or in my case,

UPDATE ps_product SET id_category_default = ‘88’ where id_category_default = 1

Do I use the comma's around the value 88, or just 88??
Thanks very much!
David

[tomerg3]

both will work

[dave204]

Strange - it came back with 2379 records updated - yet in the shop nothing has changed!!
Puzzled..!
David

[dave204]

Does anyone else know of a method to change the category of a large number of products at one go?

I've tried the method given above, but it had no effect. In fact, I don't know what its done,but it certainly didn't do what it was supposed to, even though no error occurred.

Does anyone know of any 3rd party module out there which could make the job easier, instead of changing all the records individually?

[tomerg3]

I thought they were in both categories, but by default were in the home, here's another query to actually move them to the right category...
“UPDATE ps_category_product SET id_category = ‘x’ where id_category = 1”

[dave204]

Wow! That's worked! Thanks very much for your help!
You've saved me an enormous amount of leg-work!!
Now all I have to do is transfer back the handful of products which I want in Home category (Featured)!

Great bit of PHP/MySQL coding!

Thanks once again!!

David ps I'll have a look at your store..

[dave204]

Just one last thing - should I not "alter back" the previous statement, otherwise won't the database "see" the new "Home" category as being "88", instead of "1"?
I ask this, as I'm getting timed out when I try to send back the handful of products I wanted to keep in the "Home" category, and I think its related to the previous Update?

Thanks,
David

[tomerg3]

the last staement simple moved products that were in the home category to another one, the actuall categories and their ids were not changed in any way.

[dave204]

So you think the timing out is just a co-incidence? I know it can happen, of course, on servers!!
As I said, all I was trying to do was update the products now in mobile phone accessories, by checking/unchecking their Category appearance (there is only about 12 products to do this with), but I'm getting timed-out each time, so I just wondered if the first operation I did had changed Home Category to "88", instead of "1" (the second operation worked perfectly), so any "checking" on the Home just sent the product around in a circle, if you see what I mean, and hence the time-out?

Thanks!
David

[dave204]

Hi again!
I've just tried "re-categorizing" one of the products back out of Mobile Phone Accessories, into its own Category (ie one of the cameras), without checking HOME category, and the database was updated immediately, but when I tried the same thing, WITH the HOME category checked, it timed out. So definitely something has happened to the HOME category, I think. The change to category PHP statement you gave wasn't at fault - that worked brilliantly - it was the first UPDATE which seems to have caused the problem. Can that statement be reversed at all?
Thanks,
David

[tomerg3]

The first update simply said which should be the default category, if you switch the id's you'll get the reverse effect (undo)

[eGzyl.pl]

hmm how copy all product on one category to second - i want have this product in category_1 and category_2

INSERT INTO ps_category_product VALUES('2', '22', '1')

only how i can add second category

p. ofc foreach to all produkt in category_1 ( for example for 5 to 45 )

[BUTTONBADGE]

hi, i am having the same problem that you experienced. Can anyone explain in simple terms how i submit this particular sql query in phpmyadmin. I understand the gist of it but could just do with a step by step guide for once i am actually in the database. Any help would be fantastic.

[mytheory.]

hi, i am having the same problem that you experienced. Can anyone explain in simple terms how i submit this particular sql query in phpmyadmin. I understand the gist of it but could just do with a step by step guide for once i am actually in the database. Any help would be fantastic.

Hopefully I can be of some help, but more importantly you can learn from my mistakes.

So all props for to the two queries mentioned in this thread goes out to Tomer. I will simply try to walk you through the steps of what you need to do (or at least what I did).

So I don't know how many products you have but we had over 5000 so this method came in handy. And initially when we were building the site some of the subcategories were unknown so we placed them in their "parent" category; thus, the reason we had to move a lot of products to their respective categories (subcategories).

The one thing you have to keep in mind is that it is absolutely vital that your categories ids are correct before running any queries, especially if you have products already in those categories. By running these queries and later finding out that you moved a bunch of products to a (sub)category that it was not meant for then going back would be very difficult, requiring a more complex query. Obviously, if you have no products in a subcategory than you can change the category numbers on the query and move them again, but if you already have products in those categories by running the query again it will move all the products for that specific category, and not just the ones you moved by mistake.

Preparation is key! Double and triple check your id numbers!

What I did was go through my categories and wrote down on a good old piece of paper... category 1 moves to (new) category 10 (for example). I made sure to take my time and go through my categories essentially writing out each of the queries I needed to run. I double checked them after each line and also at the end. Once you have this list you can move on.

Log in to your phpmyadmin account, and select your PS database from the left column (if you logged in as root). Then in the middle of the page you will see tabs across the top for that database. Click on the "SQL" tab. You should see a blank box almost like a text editor box. This is where you will past your queries and then click on "Go" to run/execute them.

Obviously, here you would use Tomer's queries modifying them for you appropriate category ids.

A quick note about the two queries mentioned in this thread:

1) UPDATE ps_product SET id_category_default = ‘x’ where id_category_default = y

This one will change all your default category ids; however, you will not see any products physically being moved from one category to another. How prestashop uses categories is quite complex and that is outside of my scope of knowledge. However, since a product can be assigned to more than 1 category at a given time, it needs to belong to a specified "default" category. I made a mistake of not realizing this until much later, so I had to manually go in and update the default category for individual products that were out of place.

I highly recommend also running this query... as far as how PS works it won't break anything. But there are some modules that use the default category instead of the "actual" category id, so sometimes (although minor) things may seem out of place and simply just not clean. I'm kind of OCD when it comes to keeping these kind of things clean and in order.

2) UPDATE ps_category_product SET id_category = ‘x’ where id_category = y

Now this query is the one that is actually going to move your products, having them appear in their "new" categories. I think this is self explainatory... just run this query replacing the x and the y (from both queries) with the appropriate values from that piece of paper you constructed in the beginning.

It should be noted that if you don't run query #1 but run query#2, you will see your products moved into another category, but what you might not see (that may cause minor problems down the road) is that the products will still actually default to the original category it was moved from or created in.

I'm sure there is a way to run multiple queries instead of having to run each move one by one. However, I like to make sure everything is working when working with databases (its like the brain of the system and you can't be too careful). I ran each one double checking my id numbers and also checked my BO after each move to make sure the products were emptied from one category and updated/moved to another (the right one).

Long post. But I hope it helps!

[BUTTONBADGE]

Firstly can I thank you for your very long and considered reply, I am really appreciative. I have uploaded a copy of my test site to a different location, so i won't do any damage to the proper site. I understand what the scripts are trying to do, but my problem lies in the actual execution in myphpadmin itself. I click on the database name, select the sql tab, having only previously clicked on the database name and none of the individual tables.(see attached image =1.jpg)

I enter the code in the resulting box i.e. UPDATE ps_category_product SET id_category = ‘1’ WHERE id_category =4 (see attached image =2.jpg) with 4 being the category I wish to appear in the home category i.e. id 1

and on clicking go get :MySQL said: #1054 - Unknown column '‘1’' in 'field list' (see attached image =3.jpg)

Am I following the correct proceedure or am I failing to do something I ought?

Once again my deep thanks for your time and trouble taken. I am so very grateful

[mytheory.]

Hi...

Everything looks good... I don't know if this is just a typo from your images, but try to enter the numbers (category ids) WITHOUT any quotes (or they both need to be enclosed in quotes). I'm not sure if you can have one with quotes and one without them, but for when I was executing these queries I inputted the cat. ids without quotes. So the format I used (which is slightly different from my initial post) is:

UPDATE ps_category_product SET id_category = x where id_category = y

Try that... if it still gives you the same errors you might have to look through some of your tables to make sure those category id's and their respective fields exists. But first...

Another way is to run these commands using SSH/Terminal/Command Line... I don't know what system you are using, but I believe the process is the same for both a linux machine and windows (or at least the two I ran were). If phpmyadmin doesn't work I advise you to try and run the commands through shell, before messing with any table data. Sometimes there could be some issues with your phpmyadmin config or installation. But the sure way to find out there is nothing wrong with mysql (and your databases) is to directly access mysql using the command line method.

Open your SSH, type the following:

1) mysql -u root(or your PS DB username) -p
2) Enter your password when prompted. If correct, it should connect you to mysql.
3) Type: use databasename(the name of your PS DB);
4) Run the appropriate queries.

HTH!

[BUTTONBADGE]

Hello and my many thanks once more...it Worked!!!

those old scripts must have gone stale, but your fresh one worked just fine:-)

BUT....what can I say....the script worked fine, but then I realised I had moved the contents of one category into the new category, whereas what I wanted to achieve was for them to appear in both. Can this script be adapted from

UPDATE ps_category_product SET id_category = x where id_category = y

to enable the contents of a category to be duplicated in the home category, instead of moving them there leaving their previous location empty.

I hope this makes sense......

[mytheory.]

Oh I see... what you were trying to do.

Unfortunately, my knowledge of mysql is limited and I do not know how PS handles the extra categories a product can belong to. I suppose if you can find out what table and field those values are stored in you could make a copy or update those fields.

Without messing around with it I wouldn't feel comfortable giving you anymore advice. However, there are a lot of people, in particular the moderators, who are very knowledgeable about PS and mysql queries. Hopefully, they can throw in some comments here.

Best.

[rocky]

My post here might help.

[Lenc]

There is no module to make that simple?

[anson.c]

I'm screwed! I moved everything to HOME as the default category.
I realized that using UPDATE ps_category_product.... may actually mess up all categories so I did not touch it!
Since my HOME category id is 1 all other category ids are higher than 1
is there anyway to make SQL to look up the highest number that set within each product's category id (like when importing categories x,y,z with CSV) and then set them as the default categories?
Please help!!!!!

[anson.c]

I found that the "id_category" from "ps_category_lang " is the root category of the product.
Now how do I write this correctly in SQL : "UPDATE ps_product SET id_category_default =5 FROM ps_category_lang WHERE id_category =5" ?

[anson.c]

I did some research and thought it might work:

UPDATE ps_product
SET id_category_default = (SELECT id_category FROM ps_category_lang
WHERE ps_product.id_category_default = ps_category_lang.id_category)


but no..... whats wrong?