Joobi

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
• All Acajoom Subscribers not subscribed to any list
• Empty your queue
• Clean up your queue
• Integrate Acajoom with other components

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

This query will add all your subscribers to the list with the YOUR_NEW_LIST_ID if the user is not already subscribed to it :

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`);

This query will add all your registered users to the list with the YOUR_NEW_LIST_ID if the user is not already subscribed to it :

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` WHERE user_id > 0);

This query will add all your non-registered users (simple Acajoom visitor) to the list with the YOUR_NEW_LIST_ID if the user is not already subscribed to it :

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.

Those queries will only work if YOUR_NEW_LIST_ID is a Newsletter list.
If YOUR_NEW_LIST_ID is a SmartNewsletter list, you have to change the type from 1 to 7. Here is an example for the first 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;

All Acajoom Subscribers not subscribed to any list

This query will show you all your Acajoom Subscribers which are unsubscribed from all your lists.

SELECT S.* FROM jos_acajoom_subscribers AS S LEFT JOIN jos_acajoom_queue AS Q on S.id = Q.subscriber_id WHERE Q.qid IS NULL

To see all your subscribers which are not subscribed to a specific list, you can use this query :

SELECT S.* FROM jos_acajoom_subscribers AS S LEFT JOIN jos_acajoom_queue AS Q ON S.id = Q.subscriber_id AND Q.list_id =YOUR_LIST_ID WHERE Q.qid IS NULL

So if you replace YOUR_LIST_ID y the ID of your list, you will see all the subscribers which are not in the list YOUR_LIST_ID

Empty your queue

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

DELETE FROM jos_acajoom_queue WHERE mailing_id > 0;

To know how many e-mails are in the queue depending on the mailing, you can execute this query :

SELECT mailing_id,COUNT(*) FROM jos_acajoom_queue WHERE mailing_id >0 GROUP BY mailing_id;

If you want to resend a scheduled Newsletter (few months after for example) but don't send the e-mail twice to the same user, you can execute this query after scheduling your Newsletter :

DELETE A.* FROM jos_acajoom_stats_details as B, jos_acajoom_queue as A where A.subscriber_id = B.subscriber_id AND A.mailing_id = B.mailing_id;

So any e-mail in the queue already send to the user will be deleted.

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 ;

Integrate Acajoom with other programs

Here is some SQL queries to integrate Acajoom with any other program :

• To insert the user in Acajoom :

INSERT IGNORE INTO `jos_acajoom_subscribers` ( `name`, `email`, `receive_html`, `confirmed`, `blacklist`, `subscribe_date`) VALUES ('[name]', '[email]', 1, 1, 0,NOW());

(replace [name] and [email] by the correct values)

• To subscribe the user to an autoresponder list :

INSERT IGNORE INTO `jos_acajoom_queue` (`type`, `subscriber_id`, `list_id`, `mailing_id`, `issue_nb`, `send_date`, `suspend`, `delay`, `acc_level`, `published`) SELECT A.`list_type`, ( SELECT S.`id` FROM `jos_acajoom_subscribers` as S WHERE S.`email` = '[email]' LIMIT 1), A.`list_id`, A.`id`, A.`issue_nb`, NOW(), 0, 0, A.`acc_level`, 1 FROM `jos_acajoom_mailings` as A LEFT JOIN `jos_acajoom_queue` as B ON B.`list_id` = A.`list_id` AND B.`subscriber_id` = ( SELECT S.`id` FROM `jos_acajoom_subscribers` as S WHERE S.`email` = '[email]' LIMIT 1) WHERE A.list_id = 'YOURAUTORESPONDERLISTID' AND A.`published` = 1 AND B.subscriber_id IS NULL ORDER BY A.`issue_nb` ASC LIMIT 1;

(replace YOURAUTORESPONDERLISTID by the ID of your Autoresponder list and [email] by the email of the user)

• Subscriber the user to a Newsletter list or a SmartNewsletter list :

INSERT IGNORE INTO `jos_acajoom_queue` (`type`, `subscriber_id`, `list_id`, `mailing_id`, `issue_nb`, `send_date`, `suspend`, `delay`, `acc_level`, `published`) SELECT A.`list_type`, ( SELECT S.`id` FROM `jos_acajoom_subscribers` as S WHERE S.`email` = '[email]' LIMIT 1), A.`id`, 0 , 0, NOW(), 0, 0, A.`acc_level`, 1 FROM `jos_acajoom_lists` as A WHERE A.id = 'YOURLISTID' LIMIT 1;

(replace YOURLISTID by the ID of your list and [email] by the email of the user)

• To unsubscribe the user from a list (Newsletter, autoresponder or SmartNewsletter) :

DELETE Q.* FROM `jos_acajoom_subscribers` as S LEFT JOIN `jos_acajoom_queue` as Q ON S.`id` = Q.`subscriber_id` WHERE Q.`list_id` = 'YOURLISTID' AND S.`email` = '[email]';

(replace YOURLISTID by the ID of your list and [email] by the email of the user)

Display # 5101520253050100200

Acc_level or acc_id? By: Nick A. on 2010-Jan-13 14:55:59 In your example SQL statement for adding a subscription to a newsletter or smart-newsletter list, you state that the acc_level column of the added subscription in the acajoom_queue table should be taken from the acc_level value of the acajoom_lists table for the specific list_id. However, I have added a subscriber via the acajoom backend functionality and it appears to take the acc_id value (in my case 29) instead of the acc_level value (in my case 25). Is this a typo above or did i miss something?