Accessing database by code
This is an open discussion with 2 replies, filed under General.
Search
-
-
- brendo
- 15 Feb 12, 7:47 pm
- Comment #2
If you purely just need email addresses where their signup date is more than 6 months ago and their plan is set to trial, then you may want to directly access the database via Symphony::Database()->fetch($your_sql_query) or one of the other functions.
This will require some skill with SQL and you to know the relevant databases to find the data in (Entity Diagram is a fantastic extension for this]). You can then iterate over the results with basic PHP :)
-
-
- hdPhoenix
- 15 Feb 12, 9:32 pm
- Comment #3
Hi Brendo,
Just as I finished :) Thanks though at least it verifies that I have done it in a reasonable fashion. For anyone that may want something similar I will post my finished code:
require_once(TOOLKIT . '/class.administrationpage.php');
Class contentExtensionMembership_ExpiredRun extends AdministrationPage {
public function view(){
$qry = 'SELECT e.entry_id, e.value AS email, f.value AS fname, s.value AS sname, t.value AS tel, a.activated, a.timestamp, DATE_FORMAT(a.timestamp, "%d-%m-%Y") AS registered FROM `sym_entries_data_42` e
LEFT JOIN `sym_entries_data_49` f ON e.entry_id = f.entry_id
LEFT JOIN `sym_entries_data_50` s ON e.entry_id = s.entry_id
LEFT JOIN `sym_entries_data_61` t ON e.entry_id = t.entry_id
LEFT JOIN `sym_entries_data_80` a ON e.entry_id = a.entry_id
WHERE
a.activated = "yes"
AND
DATE_FORMAT(a.timestamp, "%Y-%m-%d") = DATE_SUB(CURDATE(), INTERVAL 6 MONTH);';
$res = Symphony::Database()->fetch($qry);
foreach($res as $member){
echo 'Member expired - '.$member['email']."n";
$email = Email::create();
$email->subject = 'Membership Expired';
$email->text_plain = "A member's six month trial has expired. Details:nnEmail: ".$member['email']."nnFirst name: ".$member['fname']."nnSurname: ".$member['sname']."nnTelephone: ".$member['tel']."nnRegistered on: ".$member['registered']."";
$email->recipients = array('mark.e@kissmarketing.co.uk');
$email->send();
}
exit();
}
}
Thanks again
Regards
Create an account or sign in to comment.
Hi,
I am trying to put together a script that selects all members that have reached the end of their six month trial and sends out an email. This script is to be run daily by cron. So far I have managed to get the Shell and Cron extensions working and I have created a membership expired extension that allows for a direct call to a URL in the backend. I am struggling to work out the best way to retrieve this information. At the moment I have added a datasource although I haven't added any filters as yet:
require_once(TOOLKIT . '/class.administrationpage.php'); require_once(TOOLKIT . '/class.datasource.php'); require_once(TOOLKIT . '/class.datasourcemanager.php'); Class contentExtensionMembership_ExpiredRun extends AdministrationPage { public function view(){ $dsm = new DatasourceManager(Symphony::Engine()); $ds = $dsm->create('expired_members', array()); $data = $ds->grab(); exit(); } }The data retrieved from the grab() method appears to be correct although I'm not sure if this is the best way, also I have no idea where to go from here. I would appreciate any help, thanks.
Edit: I am currently on version 2.2.3
Regards