Form Email Plugin

[stevelis]

Hi
Experiencing several PHP messages, one being - only variables should be assigned by reference in the script below for the Email to (Eval) field of the plugin

$db =& JFactory::getDBO();
$query = "SELECT `Email` FROM `steward` WHERE `id` IN ('{calendar___chairsteward_raw}','{calendar___steward2_raw}','{calendar___steward3_raw}')";
$db->setQuery($query);
$results = $db->loadColumn();
$email_list = implode(',',$results);
return $email_list;

Receiving such messages following a recent PHP upgrade to 7.3.27
Current versions J 3.9.6 and F3.9.2 with a full github update 24th April 2021
Any suggestions appreciated
Steve

 

[troester]

$db =& JFactory::getDBO(); is outdated long ago.
https://fabrikar.com/forums/index.php?wiki/php-common-tasks/#select

 

[stevelis]

thanks troester for pointing me this way.
appreciate anyone's php assistance as I am stuck with syntax error(s) on the WHERE and IN line below

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->SELECT(`Email`)
->FROM(`steward`)
->WHERE(`id`)IN ('{calendar___chairsteward_raw}','{calendar___steward2_raw}','{calendar___steward3_raw}','{calendar___steward4_raw}'.$myDb->quote('value'));

$myDb->setQuery($myQuery);
$results = $myDb->loadColumn();
$email_list = implode(',',$results);
return $email_list;

Steve

 

[achartier]

try:

->WHERE("id IN ('{calendar___chairsteward_raw}','{calendar___steward2_raw}','{calendar___steward3_raw}','{calendar___steward4_raw}',".$myDb->quote('value').")";

 

[stevelis]

@ achartier thanks for the Where IN code.
now dealing with this syntax error, unexpected ';', expecting ')'

what I am trying to workout is there a error in the following coding, after the Where In line?
$myDb->setQuery($myQuery);
$results = $myDb->loadColumn();
$email_list = implode(',',$results);
return $email_list;

again, appreciate anyone's assistance with php
Steve

 

[achartier]

Bookmark this link: PHP Code Checker

And run your full code through it. It will tell you what line the issue is on. I ran those lines through it and no error so the error must be somewhere above in your code.

 

[stevelis]

@achartier, thanks for the link and used it with the full eval code with no issues.
copied that code into the Email to (eval) and ran with the following message
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM WHERE id IN ('500','614','353','',' at line 2
it is producing the correct results - ('500','614','353', but looking at it there's a missing closing bracket being )
i will play with the code some more
Steve

 

[achartier]

It also appears you have a null string after 353. The SQL errors don't always show the entire line.

 

[stevelis]

hi achartier
I noticed that, and as a test I removed the last element placeholder etc. from the string.
still comes up with the same error message
I will keep trying as my PHP skills are basic, but I will keep on trying.

 

[stevelis]

Hi and still trying with this eval script.
Running the following and receiving this message, - Error in your SQL syntax to use near 'FROM WHERE id IN '353','353','353','353'.('value')' at line 2

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->SELECT(`Email`)
->FROM(`steward`)
->WHERE("id IN '{calendar___chairsteward_raw}','{calendar___steward2_raw}','{calendar___steward3_raw}','{calendar___steward4_raw}'.$myDb->quote('value')");
$myDb->setQuery($myQuery);
$results = $myDb->loadColumn();
$email_list = implode(',',$results);
return $email_list;

Any ideas welcomed
Steve

 

[lousyfool]

For correct syntax see e.g. https://docs.joomla.org/Selecting_data_using_JDatabase

 

[stevelis]

thanks lousyfool
l

 

[henk]

I find the way to enter a query in php not so simple. For me it is not so clear to use all the different tics, single quote, double quote (`,',") and quote() function. It makes it more complicated.
I always use a simple query string with the exact sql syntax like I would use in phpmyadmin. "select ... from ... where ... "

 

[achartier]

You are missing some brackets around the IN values:

->WHERE("id IN (
    '{calendar___chairsteward_raw}',
    '{calendar___steward2_raw}',
    '{calendar___steward3_raw}',
    '{calendar___steward4_raw}',
    $myDb->quote('value') )"
);

I also note you have a period rather than a comma before the quote of value. This quote seems wrong though, did you want the word value quoted and a member of the IN clause?

May I also suggest you use the code insert so the formatting is retained, makes it much easier to read as you can see. Click on the Insert icon just left of the disk icons and select code. Paste the code into the window.

 

[stevelis]

hi achartier
thanks again and have done as suggested (full code below) and still receiving;
1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM WHERE id IN '385','350','340','310',('value') )' at line 2
The script is selecting the correct Id's (385, 350 etc.) for the 4 elements ({calendar___chairsteward_raw} {calendar___steward2_raw} etc.), but is failing to select the email addresses {steward___email} associated with these elements in the steward List

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
->SELECT(`Email`)
->FROM(`steward`)
->WHERE("id IN
(
'{calendar___chairsteward_raw}',
'{calendar___steward2_raw}',
'{calendar___steward3_raw}',
'{calendar___steward4_raw}',
$myDb->quote('value') )"
);
$myDb->setQuery($myQuery);
$results = $myDb->loadColumn();
$email_list = implode(',',$results);
return $email_list;

Steve

 

[juuser]

What is "$myDb->quote('value') " in the IN clause? This seems messing things up.

 

[achartier]

I agree with juuser, you are missing an ending double quote and concatenation before and after the $myDb. I think you want:

'{calendar___steward4_raw}',".$myDb->quote('value')." )");

Again, please use the insert code button so the code is formatted properly.

 

[juuser]

I don't mean quotes. What is this "value" and where is it coming from?

 

[stevelis]

@achartier - had the following code sending emails without error messages, before I saw your recent posting - '{calendar___steward4_raw}',".$myDb->quote('value')." )");
however, will insert and test for comparison

$mydb = JFactory::getDBO();
$myquery = "SELECT `Email` FROM `steward` WHERE `id` IN ('{calendar___chairsteward_raw}','{calendar___steward2_raw}','{calendar___steward3_raw}','{calendar___steward4_raw}')";
$mydb->setQuery($myquery);
$results = $mydb->loadColumn();
$email_list = implode(',',$results);
return $email_list;

Steve