SQL export script

In CMS Made Simple zijn er veel modules die ondersteuning bieden voor custom fields (vrije velden). Op het moment dat de webmaster is ingelogd op /admin heeft deze volledige vrijheid in het specificeren van velden en waardes.

Op een goede dag vraagt jouw klant een .csv dump van alle data die is ingevoerd. Is het mogelijk om dit in een query te doen?

SQL structuur custom field modules

Op het moment dat je de sql structuur van modules met custom field ondersteuning bekijkt, kom je er achter dat vaste velden in 'de hoofdtabel' en customfields in een 'properties, eigenschappen' tabel worden opgeslagen.

Het volgende script can gebruikt worden om een query te genereren die alle data in een keer uit de database haalt.

Als voorbeeld ben ik uitgegaan van de database structuur van de Front End Users module. Uiteraard is deze ook toe te passen op andere modules, datastructuren.

-- Table structure for table `cms_module_feusers_users`
--

CREATE TABLE IF NOT EXISTS `cms_module_feusers_users` (
  `id` int(11) NOT NULL,
  `username` varchar(80) DEFAULT NULL,
  `password` varchar(32) DEFAULT NULL,
  `createdate` datetime DEFAULT NULL,
  `expires` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Table structure for table `cms_module_feusers_properties`
--

CREATE TABLE IF NOT EXISTS `cms_module_feusers_properties` (
  `id` int(11) NOT NULL,
  `userid` int(11) DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  `data` longtext,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Script om query te genereren

Pas het volgende script aan en plaats deze in de root van je CMS Made Simple installatie.

<?php

/**
 * db_pivot
 */
  
##############################################################
# globals
##############################################################

// if you use this script as udt be sure to uncomment the next line!!!!!

//if (!isset($gCms)) exit;

require_once(dirname(__FILE__)."/include.php");

global $gCms;
$db =& $gCms->GetDb();

##############################################################
# program
##############################################################

// custom fields, properties

$query = "SELECT DISTINCT(title) AS title
            FROM cms_module_feusers_properties 
            WHERE title IN ('firstname', 'lastname', 'email', 'status')";

$rows = $db->GetAll($query);

// fixed fields

$cols="t1.id AS userid, \n t1.username AS username";
$joins="";

// glue it

foreach($rows AS $key => $row) {
    $alias="p{$key}";
    $cols.=",\n {$alias}.data AS '".mysql_escape_string($row['title'])."'";   
    $joins.="\n LEFT JOIN cms_module_feusers_properties AS {$alias} ON ".
    "(t1.id = {$alias}.userid AND ".
    "{$alias}.title = '{$row['title']}') ";
}

$pivotsql="SELECT $cols \nFROM cms_module_feusers_users t1 $joins \nGROUP BY ('userid') \nORDER BY 'lastname' DESC";

print "$pivotsql \n";

?>

Toepassen van filters

Als je in de $pivotsql filtering wilt toepassen kan je dit doen als:

/* pivot sql with some filtering

$pivotsql="SELECT $cols \nFROM cms_module_feusers_users t1 $joins \nGROUP BY ('userid') HAVING status = '1' AND lastname LIKE ('B%') \nORDER BY 'email' DESC";

*/