Hi, I've got a question about joining 2 tables, where the fields that I want to join on are not exactly the same, but the one field contains the value from the other field.
It's for the addon Formz. I'm working on the CSV export. For some reason, the fields of the forms that are submitted and saved to the database are in a random order. In the export, the fields have to have the order that is set in the form itself.
The order of the fields is set in the DB table modx_formz_forms_fields. This table has a field 'settings' with values like: {"label":"Email","default":"Your email"} and a field order with a number.
The data of the forms itself is saved in a table modx_formz_forms_data_fields and it has a field 'label' which holds the label that is also present in the field settings in the other table.
My goal is to get the form data for one form in the order that the labels are set in modx_formz_forms_fields, and put this in the CSV file.
I tried this:
$c = $this->modx->newQuery('fmzFormsDataFields');
$c->select(array(
$this->modx->getSelectColumns('fmzFormsDataFields', 'fmzFormsDataFields'),
$this->modx->getSelectColumns('fmzFormsFields', 'fmzFormsFields')
));
$c->leftJoin('fmzFormsFields', 'fmzFormsFields', array('%fmzFormsFields.settings% LIKE fmzFormsDataFields.label'));
$whereArray = array(
'fmzFormsDataFields.data_id' => $item['id']
);
$c->where($whereArray);
$c->sortby('fmzFormsFields.order','ASC');
//$c->prepare();
//$this->modx->log(modX::LOG_LEVEL_ERROR,'SQL: '.$c->toSQL());
$fieldsData = $this->modx->getCollection('fmzFormsDataFields',$c);
Unfortunately it's not working... the SQL looks like this:
SELECT `fmzFormsDataFields`.`id`, `fmzFormsDataFields`.`data_id`, `fmzFormsDataFields`.`label`, `fmzFormsDataFields`.`value`, `fmzFormsFields`.`id`, `fmzFormsFields`.`form_id`, `fmzFormsFields`.`type`, `fmzFormsFields`.`settings`, `fmzFormsFields`.`order` FROM `modx_formz_forms_data_fields` AS `fmzFormsDataFields` LEFT JOIN `modx_formz_forms_fields` `fmzFormsFields` ON %fmzFormsFields.settings% LIKE fmzFormsDataFields.label WHERE `fmzFormsDataFields`.`data_id` = 63 ORDER BY fmzFormsFields.order ASC
And the error I'm getting is:
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 '%fmzFormsFields.settings% LIKE fmzFormsDataFields.label WHERE `fmzFormsDataField' at line 1
So I'm not really sure how to do a leftjoin and even not sure if it's the right join to use for this. I hope someone can help me with this!
☆ A M B ☆