EDIT got this working by using a more involved sql query...
Hi all, I probably shouldn’t be posting this but I’ve gone in circles all morning without a result..
I’ve got a query which works perfectly in phpmyadmin but when I try and "modx" it (EVO 1.0.5) I can’t get it to work properly!.. I must be messing up the single/double quotes or something really silly no doubt.
I first get the ids within this (folder), then I do a query to sort which ones have certain tv values based on the GET value provided - problem is I just get ALL the ids in the folder and it ignores the where tv value part.
here’s the snippet:
<?php
global $modx;
$thisparent = $modx->documentObject['id'];
$output = '';
//First get the full list of ids inside this folder (parent)
$res = $modx->db->select("group_concat(id)", $modx->getFullTableName('site_content'), "published = '1' AND isfolder = '0' AND parent = '".$thisparent."'", "menuindex ASC");
$row = $modx->db->getRow( $res, 0,0 );
$fullistarray = implode(',',$row);
//TV selection determined by GET value
if(isset($_GET['mykind'])) {
$kind = $_GET['mykind'];
switch ($kind)
{
case '1':
$where = 'tmplvarid = 123 AND value != "" AND contentid IN('.$fullistarray.')';
break;
case '2':
$where = 'tmplvarid = 12 AND value LIKE "%tv_text_value1%" AND contentid IN('.$fullistarray.')';
break;
case '3':
$where = 'tmplvarid = 12 AND value LIKE "%tv_text_value2%" AND contentid IN('.$fullistarray.')';
break;
case '4':
$where = 'tmplvarid = 12 AND value LIKE "%tv_text_value1%" OR value LIKE "%tv_text_value2%" AND contentid IN('.$fullistarray.')';
break;
default:
$where = '';
}
//Next check against the tv values
$res1 = $modx->db->select("contentid", $modx->getFullTableName('site_tmplvar_contentvalues'), $where);
$row1 = $modx->db->getRow( $res1, 0,0 );
$fullistarray1 = implode(',',$row);
print $fullistarray1;
}
?>
Here’s the working version which I had to do with a more involved sql query instead
<?php
global $modx;
$thisparent = $modx->documentObject['id'];
//get the value from the GET vars
if(isset($_GET['mykind'])) {
$kind = $_GET['mykind'];
switch ($kind)
{
case '1':
$where = " !=''";
$tvid = 1;
break;
case '2':
$where = " LIKE '% tv_text_value1%'";
$tvid = 12;
break;
case '3':
$where = " LIKE '% tv_text_value2%'";
$tvid = 12;
break;
case '4':
$where = " LIKE '% tv_text_value1%' OR value LIKE '% tv_text_value2%'";
$tvid = 12;
break;
default:
$where = " !=''";
$tvid = 1;
}
//Now check against the tv values and get the ids list
$res1 = $modx->db->query("SELECT modx_site_content.id
FROM modx_site_content
LEFT JOIN modx_site_tmplvar_contentvalues ON modx_site_content.id = modx_site_tmplvar_contentvalues.contentid
WHERE ((`modx_site_content`.`parent` =".$thisparent.") AND (`modx_site_tmplvar_contentvalues`.`tmplvarid` =".$tvid.") AND (`modx_site_tmplvar_contentvalues`.`value` ".$where." ))
ORDER BY `modx_site_tmplvar_contentvalues`.`contentid` ASC
");
while( $row1 = $modx->db->getRow( $res1 ) ) {
$output .= $row1['id'].',';
}
if($output != '') {
return $output ;
}
}
?>
any ideas of what I’m doing wrong greatly appreciated
many thanks modx gurus..
