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

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

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

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

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

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.

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

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

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", "");

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

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.

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

$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"];

Error:
0 syntax error, unexpected '$myData' (T_VARIABLE)

Well you obviously forgot the semi colon or something

​
Hey, I just copied your code, tried it and I'm getting the error "0 syntax error, unexpected '$myData' (T_VARIABLE) "

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.

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:
​

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?

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"];

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.

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

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

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}