PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC] ); $ces = $pdo->query( "SELECT uid, pid, header, hidden, pi_flexform FROM tt_content WHERE CType = 'vitec_productlist' AND deleted = 0 ORDER BY pid, sorting" )->fetchAll(); $flexValue = function (string $xml, string $field): string { if (preg_match('/.*?(.*?)<\/value>/s', $xml, $m)) { return trim($m[1]); } return ''; }; foreach ($ces as $ce) { printf("CE %d (pid %d)%s | header: %s\n", $ce['uid'], $ce['pid'], $ce['hidden'] ? ' HIDDEN' : '', $ce['header'] ?: '-'); $cats = $flexValue((string)$ce['pi_flexform'], 'categories'); printf(" categories: %s | allproducts: %s | layout: %s\n", $cats === '' ? '(leer)' : $cats, $flexValue((string)$ce['pi_flexform'], 'allproducts') ?: '0', $flexValue((string)$ce['pi_flexform'], 'layout') ?: '-' ); foreach (array_filter(array_map('intval', explode(',', $cats))) as $catUid) { $cat = $pdo->query("SELECT title, deleted, hidden FROM sys_category WHERE uid = " . $catUid)->fetch(); $count = (int)$pdo->query( "SELECT COUNT(DISTINCT p.uid) FROM sys_category_record_mm mm JOIN tx_vitec_domain_model_product p ON p.uid = mm.uid_foreign AND p.deleted = 0 WHERE mm.uid_local = $catUid AND mm.tablenames = 'tx_vitec_domain_model_product' AND mm.fieldname = 'categories'" )->fetchColumn(); printf(" cat %d: %s | %d products\n", $catUid, $cat ? ($cat['title'] . ($cat['deleted'] ? ' [DELETED]' : '') . ($cat['hidden'] ? ' [HIDDEN]' : '')) : 'EXISTIERT NICHT', $count ); } } echo count($ces) . " productlist elements total\n";