Placeholders in scheduled email

[Tekkishodan]

I am trying to use another control in the message body of a scheduled email. I am using the following code:

My scheduled task connects and processes against the directors table. I am trying to get a list of tasks for each director and send them each a mail.

{artsqltable
query="select taskname from tasks where director = {directors___id_raw}"} {/artsqltable}

When this processes, I get the error:

Error executing query select taskname from tasks where director=1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '}' at line 1 SQL=select taskname from tasks where director=1

If i replace {directors___id_raw} with a number then it displays correctly.

Any ideas guys?

 

[rob]

odd! with artsqltable is there a way to quote the value? Something that would output.

query="select taskname from tasks where director = '{directors___id_raw}' "

I'm just thinking that if the id is not numeric the query would fail.

 

[Tekkishodan]

I have tried all sorts of combinations. Everything points me to the {directors___id_raw} not been either a string or an int.

I have also truied using PHP within the message body. It also fails.

<?php
$userid = "{directors___id_raw}";
$user=strval($userid);
$query="select taskname from tasks where director = " . $user;
echo $query;
$db =& JFactory::getDBO();
$db->setQuery($query);
$results = $db->loadObjectList();
print_r($results);
?>

This code sends me an email with the following text in it:

select taskname from tasks where director = 1

yet the print_r command gives me nothing.

If I replace the ". $user" with a numeric 1, the email comes back with (which is what i expect):

select taskname from tasks where director = 1Array ( [0] => stdClass Object ( [taskname] => Warrington DR site ) [1] => stdClass Object ( [taskname] => Exchange - DR solution Warrington ) [2] => stdClass Object ( [taskname] => Customer Payment Portal ) [3] => stdClass Object ( [taskname] => Report Automation ) [4] => stdClass Object ( [taskname] => Under Recovery ) [5] => stdClass Object ( [taskname] => Exchange - mailbox access review ) [6] => stdClass Object ( [taskname] => BCP Warrington ) [7] => stdClass Object ( [taskname] => WAN Refresh ) [8] => stdClass Object ( [taskname] => DHCP Resilience ) [9] => stdClass Object ( [taskname] => Your Claim portal re-design and build ) [10] => stdClass Object ( [taskname] => Legacy Voice Data Migration ) [11] => stdClass Object ( [taskname] => Update VPN Client on all remote users ) [12] => stdClass Object ( [taskname] => Web Proxy ) [13] => stdClass Object ( [taskname] => IPAC Air Con issue ) [14] => stdClass Object ( [taskname] => FTP server ) [15] => stdClass Object ( [taskname] => Internal web server ) [16] => stdClass Object ( [taskname] => System Centre Config Manager ) [17] => stdClass Object ( [taskname] => Upgrade IOS on VSS Core ) [18] => stdClass Object ( [taskname] => Contract Profitability ) [19] => stdClass Object ( [taskname] => Remove erroneous user account permissions from Public Folder ACL's ) )
For some reason, using the placeholder is screwing it up ( i.e. its not artsqltable).

Not sure what else to try Rob.

 

[troester]

Can you try
var_dump($userid);
to check if it's really only "1" (i.e. string(1)) and not containing any unprintable characters?

 

[Tekkishodan]

<?php
$userid = "{directors___id_raw}";
$user=strval($userid);
var_dump($userid);
$query="select taskname from tasks where director =" . $userid;
echo $query;
$db =& JFactory::getDBO();
$db->setQuery($query);
$results = $db->loadObjectList();
print_r($results);
?>
results in an email with the following content:


string(20) "1" select taskname from tasks where director =1

 

[troester]

so $userid contains a string with length 20, maybe some formatting

 

[Tekkishodan]

Doesnt help.

It must be an ordering thing.

The placeholder in its test form is 20chars long which means at the point $userid is initialised, the placeholder hasnt yet been replaced with its value.

What plugin does the email placeholder replacement and where should it be in comparison to DirectPHP plugin?

it doesnt make any sense. When the $userid variable is initialised it is still the placeholder text yet when I do the echo, the placeholder has been replaced by the value (1).

 

[Tekkishodan]

I think its building up the PHP, therefore setting the $userid variable to string(20). The content code then processes and changes the placeholder to its value of 1.

At this point I have a string with a length of 20 chars holding "1".

I do not know how to format it from this point so it is valid in MYSQL.

If I try to use the placeholder direct in the MYSQL, i get an parse error from DirectPHP.

I am stuck. The only want I can think of doing this is to process the placeholders before i do the DirectPHP. To do this, I need to know which content plugin does it. I am afraid that its the cron - email plugin which does it in which case I am buggered.

 

[rob]

perhaps try casting to an integer?

$userid = (int)"{directors___id_raw}";

 

[Tekkishodan]

Thanks for the reply Rob.

I am now convinced its nothing to do with the variable.

It is processing the PHP using the placeholder as text (as it hasnt been replaced yet resuling in a varchar(20)). When the PHP processes, the SQL contains the placeholder, not the value.

Once the PHP has been compiled it is running the code to replace the placeholders. (too late).

Its an ordering problem.

I am assuming the code to replace the placeholder is performed in the cron-email plugin?.

As this not a content plugin, I am unable to adjust its order to put it above DirectPHP.