This article contains some queries that you can execute with PhpMyAdmin.

This section is for information only to experimented users and it's considered as a hack of Acajoom but it can also allow you to do whatever you want with Acajoom.

Before executing any query, please make sure you understand what the query will do and export your database before doing anything so you will be able to re-import it again if the result is not what you expected. 

• Confirm all your subscribers
• Set all your subscribers so they will receive HTML
• Subscribe all your subscribers to an existing list
• Subscribe all your subscribers from one list to an other
  
• Empty your queue
• Clean up your queue

Confirm all your subscribers

 If you imported your list with a wrong format for the confirmation field, you will probably want to confirm all your users without doing it one by one : 

UPDATE `jos_acajoom_subscribers` SET `confirmed` = '1';

Set all your subscribers so they will receive Html e-mails

 If you imported your list with a wrong format for receive_html or if you didn't configure properly the Acajoom subscription since the beginning, you may want to set all your users so they will receive Html e-mails : 

UPDATE `jos_acajoom_subscribers` SET `receive_html` = '1';

Subscribe all your subscribers to an existing LIST 

INSERT IGNORE INTO `jos_acajoom_queue` ( `published`,`type` , `acc_level`, `subscriber_id` , `list_id` )
(SELECT 1,1,29,id,YOUR_NEW_LIST_ID FROM `jos_acajoom_subscribers`);

You have to replace YOUR_NEW_LIST_ID by the id of your List. Be careful, this query will add absolutely all your subscribers to this Mailing list even if they unsubscribed few days ago. So only execute this query if you just created the list.

This query will only work if YOUR_NEW_LIST_ID is a Newsletter list.
If YOUR_NEW_LIST_ID is a SmartNewsletter list, you can execute this query :

INSERT IGNORE INTO `jos_acajoom_queue` ( `published`,`type` , `acc_level`, `subscriber_id` , `list_id` )
(SELECT 1,7,29,id,YOUR_NEW_LIST_ID FROM `jos_acajoom_subscribers`);

Subscribe all your subscribers to list YOUR_NEW_LIST_ID if they are subscribed to YOUR_OLD_LIST_ID

INSERT IGNORE INTO jos_acajoom_queue (type,subscriber_id,list_id,mailing_id,acc_level,published)
SELECT 1,subscriber_id,YOUR_NEW_LIST_ID, 0, acc_level, 1 FROM jos_acajoom_queue where list_id = YOUR_OLD_LIST_ID AND mailing_id = 0;

This query will only work if YOUR_NEW_LIST_ID is a Newsletter list.
If YOUR_NEW_LIST_ID is a SmartNewsletter list, you can execute this query:

INSERT IGNORE INTO jos_acajoom_queue (type,subscriber_id,list_id,mailing_id,acc_level,published)
SELECT 7,subscriber_id,YOUR_NEW_LIST_ID, 0, acc_level, 1 FROM jos_acajoom_queue where list_id = YOUR_OLD_LIST_ID AND mailing_id = 0;

Empty your queue

This query will delete all e-mails from the queue :

DELETE FROM jos_acajoom_queue WHERE send_date != 0;

Clean up your queue

This query will clean up your queue in Acajoom to make sure the data in your queue are still relevant. This query will not delete any e-mail from the queue, you can execute it safely as it should not have any effect if the data in your queue are still consistent.

DELETE Q.* FROM `jos_acajoom_queue` AS Q LEFT JOIN jos_acajoom_subscribers AS S on S.id = Q.subscriber_id WHERE S.id IS NULL ;