Category access for new customers group

[Pepozzo]

Hello,

 

i'm using PS 1.4.7.0 and i've created a new customer group.

 

I have about 300 categories and i want to grant the access to all these categories for this new customers group.

 

I've seen that i have to go into all categories and select the checkbox, but i would like to know if is it possibile to do this automatically.

 

How can I do?

 

Thanks

[bellini13]

you can do this by adding the correct entries to the ps_category_group database table. each row in the table is the category_id and the group_id

 

so assuming you have categories 1, 2 and 3, and your group_id is 2, then you would have 3 entries

1, 2

2, 2

3, 2

[Pepozzo]

So, nothing using PS back-end.

 

Ok, i've done a really simple PHP script, and it works!

 

Thanks!

[Pepozzo]

Here is the script!

customer_group_access.zip

[pegoman]

Thank you Pepozzo for your script! I tryed it on my PS 1.4.7.0 and it works great. It saved me a lot of time.

[leemyongpakvn]

Here is the script!

Thank you so much. Your script saved me a lot of time.

[halenaa]

Hi.

 

Thanks a lot for the script..sorry but need to know in which file to put /upload this script? or need to upload to database?

 

Thanks in advance

Edited March 23, 2013 by halenaa (see edit history)

[ets-espinosa]

Hi, thanks a lot for your script.

 

Can you just help us a little more to know in which file this .php must be...

[maximo88]

Hi,

 

It is necessary to create a file in PrestaShop root (updCategoryGroup.php), and call it, it can be done even from browser line -myStore.com/updCategoryGroup.php

Then add the following code:

 

<?php

 

require(dirname(__FILE__).'/config/config.inc.php');

 

$new_group = 4; // there is ID of new group

$categories = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('

SELECT DISTINCT c.*

FROM `'._DB_PREFIX_.'category` c

LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (c.`id_category` = cl.`id_category` AND cl.`id_lang` = '.(int)Context::getContext()->language->id.')

');

 

foreach($categories as $category){

$categoryObj = new Category($category['id_category'], (int)Context::getContext()->language->id);

$categoryObj->addGroups(array($new_group));

}

 

Best regards.

Unlike

Valérie Assetskaya/ Valerie Assetskaya

Responsable du Soutien Clientèle à BelVG/Support manager at BelVG

module-presta.com - modules Prestashop de BelVG/ BelVG PrestaShop modules

 

 

 

------

 

Close it

[tdc666]

any solution for 1.5x? 

[Y Talansky]

This should work even for 1.6

[gfstudio]

I've got a lot of group more than 4000 since i need to set up customer discount on category and the only way is creating 1 group for each customer.

 

I've got 4000 group and 40 categories i will obtain 160000 row .... just a bit weird ...

 

Is there a way to disable group access ?

 

thank you in advance for any good reply ;-)

[Cerhan]

Just execute this SQL comman via your phpmyadmin or download adminer. Super fast and easy all in one SQL command no complicated cycles.
 

INSERT IGNORE INTO `ps_category_group` (`id_category`,`id_group`)
SELECT DISTINCT `id_category`, 8 /*this number is your id group*/ FROM `ps_category`

Dont forget to change your DB_PREFIX

Edited January 26, 2015 by Cerhan (see edit history)

[swiggy]

This SQL command rocks !

Thanks Cerhan

Edited October 9, 2015 by swiggy (see edit history)

[intandem]

Perfect SQL command. Just used it on 1.6.1.2 and works perfectly. You've just saved me and my client a ton of work. Thanks!!

[timonandries]

Thanks for the SQL command, worked like a charm and saved me a lot of work! Used it in 1.6.1.4.

Edited July 14, 2016 by timonandries (see edit history)

[inspectaclueso]

On 4/13/2012 at 12:39 AM, Pepozzo said:

Here is the script!

customer_group_access.zip

Hi Pepozzo,

Do you think this would work with 1.6.1.18?

Also, I understand all the fields except 'host'.  Where can I get 'host' information?

Thank you

[inspectaclueso]

On 1/27/2015 at 4:53 AM, Cerhan said:

Just execute this SQL comman via your phpmyadmin or download adminer. Super fast and easy all in one SQL command no complicated cycles.
 

INSERT IGNORE INTO `ps_category_group` (`id_category`,`id_group`)
SELECT DISTINCT `id_category`, 8 /*this number is your id group*/ FROM `ps_category`

Dont forget to change your DB_PREFIX

Hi @Cerhan

There have been a couple of different questions in this thread so can I please confirm before I run the SQL command...

The purpose of this command is to assign ALL categories to the specified customer group as per original poster question?

Thanks

[Cerhan]

Hi,
yes it does. Although i didn't work with prestashop for a while, so it's up to you to test if it still works (it should). I strongly recommend to run it first on some test environment, or at least backup your database.

[inspectaclueso]

18 hours ago, Cerhan said:

Hi,
yes it does. Although i didn't work with prestashop for a while, so it's up to you to test if it still works (it should). I strongly recommend to run it first on some test environment, or at least backup your database.

Thanks for the reply and for sharing the command.  I ran the command on a test store and it worked well on v1.6.1.18

Thanks again

[Antakarana]

@inspectaclueso @Cerhan

Hi

I want to add a new user group to all the categories on a PS 1.7.6.5...

In my case the new group ID is "9" and my DB_PREFIX is the following one:

So can you tell me if the script should be like this?

INSERT IGNORE INTO `prstshp_category_group` (`id_category`,`id_group`)

SELECT DISTINCT `id_category`, 9 FROM `prstshp_category`

😅

Thanks in advance!

[Antakarana]

hace 1 hora, Antakarana dijo:

@inspectaclueso @Cerhan

Hi

I want to add a new user group to all the categories on a PS 1.7.6.5...

In my case the new group ID is "9" and my DB_PREFIX is the following one:

So can you tell me if the script should be like this?

INSERT IGNORE INTO `prstshp_category_group` (`id_category`,`id_group`)

SELECT DISTINCT `id_category`, 9 FROM `prstshp_category`

😅

Thanks in advance!

I'm gonna answer myself....

 

Yes... it works!!!

 

[Eutanasio]

Hi all, this is a weird problem of Prestashop, there should be a better way of doing this by now that doesn't require us to run scripts.

Here's a better way of achieving this with error handling functionalities:

DELIMITER //
CREATE PROCEDURE GrantAccessToAllCategories()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    -- An error has occurred, rollback the transaction
    ROLLBACK;
  END;

  START TRANSACTION;

  INSERT INTO ps_category_group (id_category, id_group)
  SELECT ps_category.id_category, XX
  FROM ps_category
  WHERE NOT EXISTS (
    SELECT 1 
    FROM ps_category_group 
    WHERE ps_category_group.id_category = ps_category.id_category 
    AND ps_category_group.id_group = XX
  );

  COMMIT;
END//
DELIMITER ;

This script works works database prefixes PS_ (if yours is different you should change this value). Also change the XX with the actual Group ID you want to grant permissions to all the categories.

NB: I haven't personally tested it, so please be cautious and make you backups.