As you can see in the modx_decisions_posts table I store the multiple violation types ID. In some snippet I want to get the name of violation types for showing in the frontend. The problem is getting only the first value of the row. For this example is showing only Type 1 GR. The multilingual values its ok, do not pay attention.
This is my snippet:
<?php
$output = '';
if($cultureKey == 'el'){
$cultureKey = 'gr';
}
$placeholder = $field.'_'.$cultureKey;
$output = $modx->getPlaceholder($placeholder);
$sql = "SELECT i.* FROM `modx_decisions_posts` AS q LEFT JOIN `modx_violation_types_lists` AS i ON (q.violation_type = i.id);";
$result = $modx->query($sql);
$res = $result->fetch(PDO::FETCH_ASSOC);
$violation_types = $res["name_$cultureKey"];
$output .= '<p>'.$violation_types.'</p>';
return $output;
If these tables may grow into thousands of records (or more) of which you want to render a bunch of them at once, and you’re still in the development phase where you can change your database schema, then it may be worthwhile looking into a many-to-many table that lets you join directly. Rather than a violation_type field with pipe-separated values, you’d have a join table that links “decision_posts” with “violation types”. For large datasets being able of joining that directly is definitely a benefit.
An alternative suggestion: if you’re going to be iterating over a large number of decision_posts while the number of violation types is limited (eg < 100)… you can keep your database schema as-is, but before iterating over decision_posts you fetch all violation types into memory and simply load the matching types from an array.
$sql = "SELECT violation_type FROM modx_decisions_posts";
together with one call to fetch returns the first row of the table modx_decisions_posts. So you probably want to select the right row with a WHERE clause.
$sql = "SELECT violation_type FROM modx_decisions_posts WHERE id = 1";
Then try replacing the line $eNum = implode(',',$res); with code like this
@halftrainedharry its working but only for the first row of the table modx_decisions_posts. If I insert another rows in the table modx_decisions_posts what I can do for showing the names of the ids?
If you want to output all the rows in the table modx_decisions_posts, then call fetch in a loop.
$sql = "SELECT violation_type FROM modx_decisions_posts";
$result = $modx->query($sql);
while($res = $result->fetch(PDO::FETCH_ASSOC)){
// put the code here that makes the sql query to modx_violation_types_lists
}
I don’t know, where your cultureKey is coming from, so I replace it with a custom - snippet, which you need to write yourself, to determine your cultureKey. If it is the MODX cultureKey, you could replace that snippet with the placeholder [[++cultureKey]]
and usually, you put all related classes/table into one xpdo - schema. Not one schema for each table
@bruno17 Are displayed all violation types for every posts. Ok I will do it another way. I will change the type of column violation_type from the posts table and I will store the names of violation types directly, not the ids.
There is something wrong in the syntax here (&where={"id:IN":[ [[+violation_type:replace=||==,]] ]} ) because the color of my code in the editor is all white
