Array ( [returned] => [var] => ) [read_data4] => Array ( [log] => Array ( [0] => SELECT `Data1`.`id` AS `Data1.id`, `Data1`.`application_id` AS `Data1.application_id`, `Data1`.`alias` AS `Data1.alias`, `Data1`.`elements` AS `Data1.elements` FROM `dwctk_zoo_item` AS `Data1` LIMIT 100; ) [var] => Array (​[13] => Array ( [Data1] => Array ( [id] => 31 [application_id] => 7 [alias] => !!URL_HERE!! [elements] => { "2d93833a-d1f3-4b51-8cdd-3cf01f2f4981": { },"29766503-95eb-4d4f-8ecd-0e61ab3968d8": { "0": { "value": "!!eMAIL HERE!!", "text": "", "subject": "", "body": "" } },​​How can i add a where condition, to get only the entries with application_id = 7 and alias=$url_cut?What has changed concerning DB Read in the Chronoforms v6 version?​Thank you very much in advance and best regards,Marc"> Chronoforms v6: DB Read with where conditions, Error 1054 Unknown column - Forums

Forums

Chronoforms v6: DB Read with where conditions, Error 1054 Unknown column

gatekeepa123 30 Mar, 2018
Hi there,

I'm looking for a solution for this matter (I had this solution working with a Chronoforms v5 installation on another site)

1. Get the current url, which works so far:
$actual_link = "http://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";
$url_cut = preg_replace('#.*/#', '', $actual_link);

2. Get an mysql entry with specific where conditions from one specific table
WHERE COLUMN application_id = 7 and COLUMN alias=$url_cut
GET field elements from this one specific mysql entry

3. Find an email adress in the field "elements" and send the form to that adress

If I add a return array condition (which worked in Chronoforms 5), I'm getting the error "1054 Unknown column 'Data1.1' in 'where clause'



Debugger (cutted)
Array
(
    [Aktuelle URL] => Array
        (
            [returned] => 
            [var] => 
        )

    [read_data4] => Array
        (
            [log] => Array
                (
                    [0] => SELECT `Data1`.`id` AS `Data1.id`, `Data1`.`application_id` AS `Data1.application_id`, `Data1`.`alias` AS `Data1.alias`, `Data1`.`elements` AS `Data1.elements` FROM `dwctk_zoo_item` AS `Data1` LIMIT 100;
                )

            [var] => Array
                (
[13] => Array
                        (
                            [Data1] => Array
                                (
                                    [id] => 31
                                    [application_id] => 7
                                    [alias] => !!URL_HERE!!
                                    [elements] => {
	"2d93833a-d1f3-4b51-8cdd-3cf01f2f4981": {

	},
"29766503-95eb-4d4f-8ecd-0e61ab3968d8": {
		"0": {
			"value": "!!eMAIL HERE!!",
			"text": "",
			"subject": "",
			"body": ""
		}
	},


How can i add a where condition, to get only the entries with application_id = 7 and alias=$url_cut?
What has changed concerning DB Read in the Chronoforms v6 version?

Thank you very much in advance and best regards,
Marc
GreyHead 08 Apr, 2018
Hi Marc,

Please see page 20/21 in the CFv6 manual - the syntax is different.

Note that the URL query parameters are captured by CFv6 - see page 10 of the manual - but it doesn't look as though the URL itself is captured.

Bob
Max_admin 09 Apr, 2018
Hi,

For the url you need to return it from a PHP action first then use {var:php_name} in the condistions section to capture the value.

Best regards
Max
ChronoForms developer...
Did you try ChronoMyAdmin for managing your Joomla database tables ?
gatekeepa123 09 Apr, 2018
I'm afraid I really dont understand the syntax that is used in Chronoforms v6.

Custom php LOAD:
$actual_link = "http://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";
$url_cut = preg_replace('#.*/#', '', $actual_link);
return $url_cut;
Read Data WHERE CONDITIONS:

- I need the value from 29766503-95eb-4d4f-8ecd-0e61ab3968d8 (THE EMAIL) from field ELEMENTS
- WHERE application_id = 7
- WHERE alias = $url_cut




And then send an email via chronoforms to that email.

Can you help me with the WHERE CONDITIONS and how to make the variable from ELEMENTS available so that I can send an email to that adress via Cf6?

Best regards,

Marc
healyhatman 10 Apr, 2018
Try..... ummmmmmmmm,.........

ok, let's say that in your url parameters you have &id=7

Put a PHP action in, called "url_cut", with your php code


In your WHERE conditions, try

model.id:{data:id}
model.alias:{var:url_cut}

And then to get that value you're after try maybe {var.jsonde:read_data_name.model.elements.29766503-95eb-4d4f-8ecd-0e61ab3968d8.value}

Or something like that.
Max_admin 11 Apr, 2018
Hi Marc,

What is the name of the "PHP" action ? you will need to use in the where conditions:
alias:{var:php_name}
Best regards
Max
ChronoForms developer...
Did you try ChronoMyAdmin for managing your Joomla database tables ?
gatekeepa123 11 Apr, 2018
Hey guys,

got to the point where the WHERE condition works, I only get the entry with the matched url_cut = alias.

How can I work with the returned array now: How can I extract the email? print_r ($read_data4); is empty when I use it in "Read Data" / "Found":



Debugger (cutted):
Array
(
[url_cut] => Array
(
[returned] => xyxy
[var] => xyxy
)

[read_data4] => Array
(
[log] => Array
(
[0] => SELECT `Data1`.`elements` AS `Data1.elements` FROM `dwctk_zoo_item` AS `Data1` WHERE `Data1`.`alias` = 'wolfgang-reinpold' LIMIT 100;
)

[var] => Array
(
[0] => Array
(
[Data1] => Array
(
[elements] => {
"2d93833a-d1f3-4b51-8cdd-3cf01f2f4981": {

},
MORE STUFF HERE
"29766503-95eb-4d4f-8ecd-0e61ab3968d8": {
"0": {
"value": "email_here@email.de",
"text": "",
"subject": "",
"body": ""
}
},
MORE STUFF HERE

Thank you and best regards,

Marc
healyhatman 11 Apr, 2018
print_r($read_data4)
is empty because that's not how it works. If you want the value of a {var:} in PHP you need
$this->get("var_name", default value);
So for you that would return an array of results, because your Read Data action is set to return All matching records.

To get to the value you're after, you'd need
$this->get("read_data4.0.Data1.elements.29766503-95eb-4d4f-8ecd-0e61ab3968d8.value", "");
GreyHead 12 Apr, 2018
Hi,

I'm not sure that healyhatman's code will work as what you have saved in the table is a long text string which includes the value you need. It will not be returned as an array but as a string. I suspect that you will need to write some clever regexp code to extract the value you need.

Or maybe better to change the saved code so that it is more easily accessible, You could json encode the array before saving; or save each of the array data sets in a second table linked to the first.

Bob
healyhatman 12 Apr, 2018
GreyHead it looks like JSON to me, in which case should be able to get it using {var/jsonde:blahblahblah}

or in php
$myArray = json_decode($this->get("read_data4.0.Data1.elements", ""));
$value = $myArray['29766503-95eb-4d4f-8ecd-0e61ab3968d8']['value'];

EDIT: Put it through a JSON validator and it's valid JSON, so that shouldn't be a problem.
gatekeepa123 22 Apr, 2018
Hey guys,

Or maybe better to change the saved code so that it is more easily accessible, You could json encode the array before saving; or save each of the array data sets in a second table linked to the first.


I'm afraid thats not an option, since I use a third party software (yootheme zoo) here.

$this->get("read_data4.0.Data1.elements.29766503-95eb-4d4f-8ecd-0e61ab3968d8.value", "");


Getting the following error:
Recoverable fatal error: Object of class G2\L\View could not be converted to string in/kunden/337751_33098/gross-sand-joomla3/libraries/cegcore2/admin/extensions/chronofc/functions/php/php_output.php(6) : eval()'d codeon line7

$myArray = json_decode($this->get("read_data4.0.Data1.elements", ""));$value = $myArray['29766503-95eb-4d4f-8ecd-0e61ab3968d8']['value'];


And getting this error here:
syntax error, unexpected '' (T_STRING)

Any other ideas or help would be much appreciated.

Best regards,

Marc
healyhatman 22 Apr, 2018
$this->get("read_data4.0.Data1.elements.29766503-95eb-4d4f-8ecd-0e61ab3968d8.value", "");

Change this to

$elements = $this->get("read_data4.0.Data1.elements", "");

$myData = json_decode($elements);

Now you should be able to do

$theValue = $myData["29766503-95eb-4d4f-8ecd-0e61ab3968d8"]["value"];
gatekeepa123 22 Apr, 2018
Error:
0 syntax error, unexpected '$myData' (T_VARIABLE)
healyhatman 22 Apr, 2018
Well you obviously forgot the semi colon or something
gatekeepa123 22 Apr, 2018


Hey, I just copied your code, tried it and I'm getting the error "0 syntax error, unexpected '$myData' (T_VARIABLE) "
healyhatman 22 Apr, 2018
Well your first line I doubt will work, pretty sure it should be

$actual_link = "http://{$_SERVER[HTTP_HOST]}{$_SERVER[REQUEST_URI]}";

As for the rest, I put it in a syntax checker and everything is fine so either something has gone wrong when copying it or.... well there is no "or". Try retyping it out I don't know.
gatekeepa123 22 Apr, 2018
First let me thank you for your continuing help!

The $actual_link works fine; I can echo the URL. For testing purposes I cut it, but getting the same result.


Getting the same error, when using phpcodechecker.net:

gatekeepa123 22 Apr, 2018
Okay, I manually wrote line for line again - seems like there is also a problem with using empty lines in the Chronoforms code field - and I get to this point:
$actual_link = "http://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";
$url_cut = preg_replace('#.*/#', '', $actual_link);
echo "URL: ";
echo $url_cut;
echo "<br><br>";
$elements = $this->get("read_data4.0.Data1.elements", "");
print_r ($elements);
$myData = json_decode($elements);
$theValue = $myData["29766503-95eb-4d4f-8ecd-0e61ab3968d8"]["value"];

Error:

Cannot use object of type stdClass as array

Update:

I added true to jscon_decode, but $theValue seems empty:
$actual_link = "http://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";
$url_cut = preg_replace('#.*/#', '', $actual_link);
echo "URL: ";
echo $url_cut;
echo "<br><br>";
$elements = $this->get("read_data4.0.Data1.elements", "");
print_r ($elements);
$myData = json_decode($elements, true);
$theValue = $myData["29766503-95eb-4d4f-8ecd-0e61ab3968d8"]["value"];
echo "eMail: ";
echo $theValue;

Output:

URL: url-here

{ "2d93833a-d1f3-4b51-8cdd-3cf01f2f4981": { }, "e05bddb0-ed9b-4da5-9abd-1d13955c6ad2": { "file": "images\/personen\/verwaltung\/sarah_sieweke_portrait.jpg", "title": "", "link": "", "target": "0", "rel": "", "lightbox_image": "", "spotlight_effect": "", "caption": "", "width": 620, "height": 338 }, "007d9ea7-3bf6-4ae6-88f7-95f6607c4cda": { "file": "images\/personen\/verwaltung\/sarah_sieweke_banner.jpg", "title": "", "link": "", "target": "0", "rel": "", "lightbox_image": "", "spotlight_effect": "", "caption": "", "width": 1560, "height": 550 }, "aa38dd03-0baa-4b20-9dcd-5525bc4ce39b": { "0": { "value": "" } }, "956e0888-c9d4-48f5-a48b-05ea130c58c2": { "0": { "value": "Strategische" } }, "7ade261f-96b6-48e8-8a65-84231fd12973": { "0": { "value": "Unternehmenskommunikation" } }, "32a03c7c-eecf-47f7-97f9-d10bf1b3fb84": { "0": { "value": "" } }, "7ae3d585-ff8c-46ce-9163-2b2496b2a425": { "0": { "value": "" } }, "dae1663f-9ef6-4239-adfa-49f430bd22d9": { "0": { "value": "" } }, "817dce1e-7116-464e-8ef8-fda7d888faed": { "0": { "value": "" } }, "c46df587-2d27-4e50-be9f-13acac47879a": { "0": { "value": "" } }, "7ecb8e22-5825-4719-b447-59729c84b558": { "0": { "value": "" } }, "b6c47ce2-773e-467a-a2fa-b66f53d1ff1b": { "0": { "value": "" } }, "30d9a12a-0c29-4e1b-8315-79bda2f8f277": { "0": { "value": "+49 (0)40 75 205 - 284" } }, "82a24b82-f5e3-43cc-ab80-4a15b5427c3e": { "0": { "value": "+49 (0)40 75 205 - 98284" } }, "29766503-95eb-4d4f-8ecd-0e61ab3968d8": { "0": { "value": "s.sieweke@gross-sand.de", "text": "", "subject": "", "body": "" } }, "6ae6ae51-1889-4dca-a8bd-0326194c6969": { "file": "", "hits": "0", "download_limit": "", "size": 0 }, "38131937-bce3-4ff4-afa8-9a0992b8e98e": { "0": { "value": "" } }, "a6fccdb5-71fe-4106-a339-9cfe2ace9ba8": { }, "53285b7f-0f6f-4344-babf-42c207d3e7e1": { "0": { "value": "" } }, "fadb90dc-dcfb-4d66-926a-cf1c59cd57b4": { }, "64ddce40-2006-4c5b-9e35-74f7b57a6fcb": { }, "056071ed-3d0b-4ded-8822-3f8548773851": { "0": { "value": "" } }, "910112e0-1553-4276-ae0f-df153eec295b": { "0": { "value": "" } } }eMail:

Update #2 (continuing to post here, maybe my problem is useful for other people...)

Got it working now with the following code:
$actual_link = "http://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";
$url_cut = preg_replace('#.*/#', '', $actual_link);
echo "URL: ";
echo $url_cut;
echo "<br><br>";
$elements = $this->get("read_data4.0.Data1.elements", "");
$myData = json_decode($elements, true);
$theValue = $myData["29766503-95eb-4d4f-8ecd-0e61ab3968d8"]["value"];
$pattern = '/[a-z0-9_\-\+]+@[a-z0-9\-]+\.([a-z]{2,3})(?:\.[a-z]{2})?/i';
preg_match_all($pattern, $elements, $matches);
$email_adress = $matches[0][0];
echo "eMail: ";
echo $email_adress;
Follow up question:
How can I use the value in $email_adress now to send a mail to it via Chronoforms?
healyhatman 22 Apr, 2018
preg_match seems a bit complicated?
Try this, I ran the text through a json_decoder and I see there's an extra array bracket doovey in there.
$theValue = $myData["29766503-95eb-4d4f-8ecd-0e61ab3968d8"]["0"]["value"];
gatekeepa123 22 Apr, 2018
Perfect, thank you.

How can I use the value in $email_adress now to send a mail to it via Chronoforms?

{var:email_adress} or {data:email_adress} doesnt work when I add them to the Recipients list.

It seems the value in $email_adress "gets lost" between LOAD and SUBMIT.
GreyHead 23 Apr, 2018
Hi gatekeepa123 ,

It's a bit hard to tell exactly what you are doing here. If you are looking up the email address in the On Load event then you can use a Hidden input to pass the value to the On Submit event with the rest of the form data.

Bob
gatekeepa123 25 Apr, 2018
Hey Greyhead,

I want to send an eMail to the adress captured with DB Read (LOAD) in variable $email_adress via Chronoforms now.

I can echo the $email_adress in LOAD now, but in SUBMIT its empty.

How exactly can I add a hidden input to LOAD to pass the value to SUBMIT?
I'm sorry, but I'm afraid at this point I need an exact step-by-step-guide and code.

For the last step, I then need a hint how to handle the variable in the "Recipient List". Is it {data:email_adress} ?

Best regards,

Marc
healyhatman 25 Apr, 2018
Everything you need is in the demo forms and the FAQs. But here you go anyway.

Hidden field: value: {var:how you get the email above}
gatekeepa123 01 May, 2018
Answer
1 Likes
Alright, I think I got it:

1. In SETUP tab I changed php action name to "phpemail" with return $email_adress;
2. In Designer I added custom html with <input type="hidden" name="email-person" value="{var:phpemail}">
3. In Setup I added {data:email-person} to the Recipients list

Thank you very much for your help guys! Case closed...😉
This topic is locked and no more replies can be posted.