Email Users after they subscribe

[esskay]

Hello...Looking to set up a system where the users will be updated by an email when the record is added in the list if they opt to subscribe...It is best explained as under -

1. A list called Events and one of the column is City
2. City column contains City-A, City-B, City-C etc.
3. An Email Subscription Form where the user can subscribe for any update/addition in the list .
4. For example User-A subscribe to get event details in City-B
5. In this case any record which is added in Events List with Column as City-B, User-A will receive an email
6. User can subscribe/unsubscribe to any column

Can you please advise what is the best setup in this scenario and how do I do it...? What are the plugins needed, php code, sql, cron etc...?

Please advise...

 

[cheesegrits]

You'd probably just need an "Email to (eval)" on an email plugin on the events form. Do a lookup on the subscription list for all users with that city ID.

If you want me to write the PHP for you, that's a bit outside the scope of standard support, that's a Pro support kind of thing.

-- hugh

 

[esskay]

Thanks for your response Hugh....Without PHP Code I will never ever be able to get this to work...I did a quick search and I could not find any similar thread in this forum from where I could get some pointers. Any similar topic which can help me in this regard...?

 

[cheesegrits]

$cityId = $emailData['events___city_raw'];
$cityId = is_array($cityId) ? $cityId[0] : $cityId;
if (!empty($cityId)) {
   $myDb = JFactory::getDbo();
   $myQuery = $myDb->getQuery(true);
   $myQuery
      ->select('u.email')
      ->from('subscriptions AS s')
      ->leftJoin('#__users AS u ON u.id = s.userid')
      ->where('s.city = ' . $myDb->quote($cityId));
   $myDb->setQuery($myQuery);
   $myEmails = $myDb->loadColumn('email');
   return implode(',', $myEmails);
}
return '';

Substitute your element / field / table names as appropriate.

And yes, I understand that without code you can't make this work. But please understand that a standard sub simply doesn't cover the cost of me writing custom code for everyone.

-- hugh

 

[esskay]

Thanks a lot Hugh...Let me try the code...will keep you posted...

 

[esskay]

Ok...I have tried but could not make it to work. Your code may be correct but I am not understanding what and where to substitute my element/field/table names. I do not have courage to ask for more help from you. Treat this as closed.

Thanks once again for the code...

 

[cheesegrits]

Tell me your two table names (subscription and events), and the elements you use for the city join on the events list, and the city join and user element on the subscriptions list.

-- hugh

 

[esskay]

Alright...here is my setup -

1. Table called query and db table name is query. This is the main table.
2. Table called query alert and db table name is query_alert. This is the subscription table.
3. query has following elements (apart from two standard fabrik elements) -

a. title which is a field plugin. Full element name is query___title
b. city which is also a field plugin ( Unfortunately I cannot have dropdown). This contains multiple city names. Full element name is query___city

4. query_alert has following elements (apart from two standard fabrik elements) -

a. user which is a hidden user plugin. Full element name is query_alert___user
b. city which is a dropdown plugin. Full element name is query_alert___city. Dropdown values are NULL, citya, cityb, cityc, cityd and so on. Multiple selection set to Yes.
c. alert_email which is a field plugin. Full element name is query_alert___alert_email. This is the email where I want to send the updates.

5. I have added email plugin to "query" list form. Email to works well but email to (eval) does not trigger the mail.

6. I have another list called My Alerts which is pre filtered list of query showing user subscribed query alerts...

Please advise and thanks for extending help to me...

 

[cheesegrits]

So is the "query" list what you called "events" in your original post?

That setup is going to be extremely difficult to manage. Trying to match a multi-select dropdown against a simple field with multiple cit names.

Is there some reason you can have a "city" table, and use multi-select joins to it from your two lists?

-- hugh

 

[esskay]

So is the "query" list what you called "events" in your original post?

- Yes

Is there some reason you can have a "city" table, and use multi-select joins to it from your two lists?

- Yes...In fact I am trying to understand the possible setup with the above example of cities. I cannot have dropdown for joins because there are thousands of records already in the existing table.

If needed I can forego multi select...

 

[cheesegrits]

So the "events" list (query) already has content, and your "city" field already has instances of multiple cities? Are they in any kind of standard format, like comma separated? "City A, City B'?

-- hugh

 

[esskay]

yes...they are always in a fixed pattern...it is [citya, cityb, cityc, cityd] without brackets...

 

[cheesegrits]

That's still going to be a real pain to try and work around. The only real solution to this is to normalize your data, so both tables are using a join to a "cities" table.

I mean, it'd be possible to do it this way, but not simple to code.

-- hugh

 

[esskay]

I know its a bit complicated but to change complete setup with new elements and structure will take time and at present have no plans to do it. Can we try with the existing setup...? If possible give me the code ?

 

[cheesegrits]

I'm not trying to be mean, but essentially what you are saying is "I don't want to take the time to fix my database structures, can you give me your time to work round it instead". And I just can't afford to do that. A Standard sub just doesn't cover it. Not that I don't want to help you, I literally can't afford to, as I'd be working for less than minimum wage.

I would have happily written the half dozen lines of code it would take to do this if the data structures were normalized properly, as that would be easy, I could do it off the top of my head, and I appreciate that you've been a subscriber for a while, so I do go out of my way to help you when I can. But trying to work round the way it's structured at the moment would be a lot more difficult and time consuming to write.

If you'd like to take out a Pro sub for a month or two, I could afford to help.

-- hugh

 

[esskay]

I'm not trying to be mean, but essentially what you are saying is "I don't want to take the time to fix my database structures, can you give me your time to work round it instead". And I just can't afford to do that.

No...No...That was never an intention Hugh...The limited reason I used these words was that the list has about 80000 records and to re-structure everything is not planned. This means that the whole layout, designs, tables, elements, filters will have to be reworked. This functionality I needed as an additional feature to the visitors so that the new records are there in their emails right when they are added to the list instead of waiting for the newsletter.

But yes...I got your point. In the present structure it is difficult to set this to work and I should have realised it. Sorry and lets forget about it. You are always quite liberal in extending your support not only to me but to all subscribers and I appreciate it. Thanks a lot...

 

[cheesegrits]

That probably sounded rude, and I really didn't mean it that way.

It would be possible to do with your site as-is, just not trivial, so if it's something you decide you can't live without, a month of Pro sub would cover my time.

-- hugh

 

[esskay]

Alright...I will think on it...