[ Solved ] PrestaShop Cleaner and No Category ID Reset

[amar.prestashop]

Hi,

 

I need to reset the entire Prestashop Database using the Cleaner module provided with version 1.5.4.x

 

When i use the Cleaner Module all Database is reset correctly but the Category ID continue from the last category i've entered before the Cleaning.

 

I need the Category ID is reset to its original value of "3".

 

Is it possible ?

 

( Tested also in a fresh new installation )

 

Thanks

Edited July 31, 2013 by amar.prestashop (see edit history)

[NemoPS]

Try running this query

 

ALTER TABLE ps_category AUTO_INCREMENT=3;

 

 

Of course, use your own prefix

[amar.prestashop]

Thanks Nemo,

 

It works

 

Amar,

[samuel89]

Thanks Nemo,

 

It works

 

Amar,

Did you managed to get it working? I have a similar problem and tried the above method but it doesnt work.

[amar.prestashop]

HI Samuel89,

 

Yes it works form me in PS v1.5.4.1

 

Do you have used phpmyadmin ?

ALTER TABLE ps_category AUTO_INCREMENT=3;

[andenand]

This fix also works on Prestashop 1.6.0.7

[vekia]

This fix also works on Prestashop 1.6.0.7

 

hello

it's not related to prestashop verison

it's related to mysql

 

if you see exactly the same error (with the same number) you can use this sql query.

[nuevo]

hello, i try to do this and now i have a other big problem, prestashop database use the wordpress data base and now i cant add categories.

 

how can i reset this way?.

[NemoPS]

Tables should have a different prefix, don't they? If they got mixed up because they had no prefix.... big deal

[nuevo]

no, now when i add a category no apear in the backoffice and no apear in web in the menu zone.

 

how can restore this?.

 

now, i need a category id in backoffice and web menu.

 

please help!!..

 

i try to delete the complet installation of prestashop and install new one,but always the same problem.

[NemoPS]

Can you show us a screen of the database tables list? We need to know if you installed both wp and ps without prefix

[franek]

Hi guys,

I have the same problem, but I don't no anything about SQL..

So, I go to Advanced Parameters / SQL Manager, then "Add new SQL query". I assign a "SQL query name" (whatever name makes sense to me), and then on "SQL query", I key "ALTER TABLE 6oh_category AUTO_INCREMENT=3;" (my prefix is 6oh).

 

When I try to save, it gives an error "SELECT does not exist".

 

What do I do wrong?  

[NemoPS]

you can't do it from there, you can only select using that tool. FOr that query to run, you need to login to phpmyadmin from your hosting control panel

[franek]

Thanks nemo1! So I went to php my admin (1st time in my life  ), click on "..._pres944" (my DB name) on the expand list on the left. Then, on the categories block on the top I click on "SQL". After I do this, it shows "Run SQL query/queries on database ..._pres944" and I copy "ALTER TABLE 6oh_category AUTO_INCREMENT=3;", then click "Go". What I get is:

"MySQL returned an empty result set (i.e. zero rows). (Query took 0.1862 sec)"

 

After I saw that no effect, I tried this (and probably I messed up even more..  ). I expand the same DB list on the left and I get the name of all the tables in 6oh. I clicked on 6oh_category, and I got the list of all the active categories. I simply clicked on "edit" on all the categories for which I wanted to change the category ID, and simply changed it. The result is that those categories disappeared from my back-end, despite still existing in 6oh_category. How badly did I mess up?   :unsure:    

 

P.S.: you're the genius who made Prestashop 101 videos right? Thanks a ton for those!!!!

[NemoPS]

Oh I am not a genius at all really but yes, that's my series (day 9 coming this week)

Anyway, I am not sure I got what you did, which ID did you change? It's not a good idea if you don't modify

- the category_lang table

- the category_product table as well

 

Otherwise you look names and product connections, you should better take them back

As for the auto increment, it's weird, it should have been taken to 3

What did you want to do exactly, apart from setting the new next id to 3?

[franek]

Oh my, that's what all categories disappeared in the back-end. However, I "solved" the mess I did, and I reassigned to the previous categories IDs. This way all the previous categories appeared again and I recovered..

 

I have to start with the fact that it's the very first time to me that I'm working on DBs and Prestashop, so I really appreciate all your effort in educating newbies like me. So the story went like this: I install prestashop and start customizing the default-bootstrap template.

 

I was reading in a PS tutorial (a different one than yours) that in order to wipe-out the current data, the best thing to do was to use the Prestashop cleaner module. That in my opinion was not a good idea since:

 

1. The categories were wiped out but still the categories IDs were not reset, which is imo like making the module pointless. That's how I got into this thread and the reason of my first comment in this. Trying to use the query command as advised but it doesn't work to me. On phpMyAdmin I tried to input the command "ALTER TABLE 6oh_category AUTO_INCREMENT=3;" in these 3 different ways:
   1. selecting .._pres944, then tab "SQL", then "Go" --> gives me "MySQL returned an empty result set (i.e. zero rows). (Query took 0.0477 sec)"
   2. selecting .._pres944, then tab "Query", then "Submit Query" --> gives me "loading" for couple of second, then nothing
   3. selecting 6oh_category, then tab "SQL", I see on line 1 "SELECT * FROM `6oh_category` WHERE 1;", I add on line 2 the command, then "Go" --> gives me "# 7 rows affected.", but this doesn't change the existing categories ID number, and doesn't impact any newly created category IDs (they will start from 21, then 22..). I actually don't know what to do on this.. However, probably I should focus on other more important points, since having categories ID ordered without gaps, is just a nice to have imo
2. Another bad effect of using the Prestashop cleaner is that the categories block disappeared from the front-end. This could make sense as long as categories were wiped out and no more categories were in place, but persisted even now that I added categories: still I cannot find a way to restore the block..

Regarding PS 101, still way to go for me, I'll start day 5 tonight

 

Thanks for any guidance you may give me!!! And sorry for probably not using the right terms/not being enough technical and making silly questions.. I'm just starting from scratch, and I would have actually gladly delegated this to some expert, if only any different freelancer was not shooting crazy fees.. :/

[NemoPS]

I am one of the fiercest enemies of that "Cleaner"

Anyway I personally prefer to always learn something than relying on others myself, so we're on the same boat Good luck!

And of course feel free to ask anything else you might need, no worries

[franek]

Thanks Nemo! What about the last point of my previous message?

 

Another bad effect of using the Prestashop cleaner is that the categories block disappeared from the front-end. This could make sense as long as categories were wiped out and no more categories were in place, but persisted even now that I added categories: still I cannot find a way to restore the block..

 

How can I make the categories block appear again?

[NemoPS]

I think it screwed the root category id. Try checking if there is a valid root category id for the ps_shop_url table

[franek]

Solved! Actually I found out that what I meant was rather "Top horizontal menu" rather than "Categories block"

So I went on its module and found out no items were selected.. Found out thanks to your Prestashop 101 series (just watched Day 6 - modules)

 

Thanks!!!!!

[AndyNauman]

Try running this query

 

ALTER TABLE ps_category AUTO_INCREMENT=3;

 

 

Of course, use your own prefix

 

Hey NEMO - I used this and it worked the first time.  I needed to change it a second time and it is not working.  Any suggestions?

[[email protected]]

Hey NEMO - I used this and it worked the first time.  I needed to change it a second time and it is not working.  Any suggestions?

 

will try to help...

but first, I need to know what sort of situation do you have there?

do you face another category issue?

how many categories you already have now?

Edited December 27, 2014 by [email protected] (see edit history)

[dioptaze]

Ok i recently had the same problem with this site http://www.cryptos-revolution.com

But have a proble with code3 please help me too thanks

[NemoPS]

@AndyNauman: you mean it's not resetting the auto increment value? Did you erase all other ids higher than the one you want to set, for all category-related tables?

[AndyNauman]

@AndyNauman: you mean it's not resetting the auto increment value? Did you erase all other ids higher than the one you want to set, for all category-related tables?

Yes Nemo, that is what I mean and No, I did not erase the older IDs.  From what I have found on the Forum, deleting old orders is not possible.  Do you have a procedure for this?  We only have test orders so far so all can be deleted.

[[email protected]]

Yes Nemo, that is what I mean and No, I did not erase the older IDs.  From what I have found on the Forum, deleting old orders is not possible.  Do you have a procedure for this?  We only have test orders so far so all can be deleted.

 

ALTER TABLE ps_category AUTO_INCREMENT=3;

That query meant for adding "the very first new category" under 'Home' as parent category.

If you already have other subcategories, you cannot use auto_increment=3

 

Make sure you delete all categories before you set auto_increment=3

 

something I still don't understand about what you mean by 'old orders'. are you talking about deleting and resetting 'order' data? If so, it's going to be a different table, other than we talked about category table here.

Edited December 29, 2014 by [email protected] (see edit history)

[NemoPS]

Orders are not related to categories, so I am not sure what you mean by that myself 

As for the auto increment, aryheru is correct, you need to erase all categories with id >=3 if you  want to set the auto increment value to that number. 

If you really need to erase orders, in any case, you can try out my free module, although it's not perfect with 1.6

[mplnck]

Hey,

 

i just reset my order id to 1 and it worked very well. 

a problem, which occured now is, that you don't get any confirmation emails for your order :/

 

anyone an idea?

 

[NemoPS]

Do you get any error if you turn on error reporting?

[Caleydon]

Open the module file pscleaner.php and add following line

$db->execute('ALTER TABLE `'._DB_PREFIX_.'category` AUTO_INCREMENT=3');

after the line

$db->execute('DELETE FROM `'._DB_PREFIX_.'category` WHERE id_category NOT IN ('.implode(',', array_map('intval', $id_home)).', '.implode(',', array_map('intval', $id_root)).')');

 

[selectshop.at]

@ Caleydon - we appreciate your contribution, but besides this topic is marked as solved, seems to missed this one:

https://www.prestashop.com/forums/topic/642866-stop-posting-in-this-forum-it-is-closed-to-replies/

Please do not answer to topics added to this forum section. It is not active and only an archive. You are free to add valuable tips to a problem on the General forum Section, if you want.

Thank you for your cooperation.