7kXj5atB

1        <?php
2        /**
3	 * Function to populate a custom field cache that can then be easily queried instead
4	 * of the unqueryable JSON in the items row
5	 * 
6 	 * @copyright	Copyright (C) 2012 Blue Flame IT Ltd / Phil Taylor. All rights reserved.
7	 * @author 		Phil Taylor <phil@phil-taylor.com>
8	 * @since		3rd July 2012
9 	 */
10	 function onAfterK2Save($row, $isNew){
11		$db = JFactory::getDbo();
12
13		// If table is empty - populate it the first time!
14		$db->setQuery('SELECT COUNT(*) from #__k2_cfcache');
15		$res = $db->loadResult();
16		
17		if (NULL===$res){ // No table - need to create it the first time
18			$db->setQuery('CREATE TABLE IF NOT EXISTS `jos_k2_cfcache` (
19				  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
20				  `item_id` int(11) unsigned NOT NULL,
21				  `field_id` int(11) DEFAULT NULL,
22				  `field_name` varchar(255) DEFAULT NULL,
23				  `field_value` varchar(255) DEFAULT NULL,
24				  PRIMARY KEY (`id`)
25				) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
26			$db->query();
27		}
28		
29		if ($res===NULL|| $res==="0"){ // Just created the table or not yet populated
30			$db->setQuery('SELECT * from #__k2_items');
31			$rows = $db->loadObjectList();
32			foreach ($rows as $item){
33			$extrafields = json_decode($item->extra_fields);
34				foreach ($extrafields as $field){
35					if (is_array($field->value)) $field->value = json_encode($field->value);
36					$sql = sprintf('INSERT INTO #__k2_cfcache VALUES (NULL, %s, %s, (SELECT NAME FROM `jos_k2_extra_fields` WHERE id = %s),"%s")',
37					$item->id, $field->id, $field->id, $field->value);
38					$db->setQuery($sql);
39					$db->query();
40				}
41			}
42		}
43		
44		// clear this items details
45		$db->setQuery('DELETE FROM #__k2_cfcache where item_id = '.$row->id);
46		$db->query();
47		
48		// repopulate this items custom field cache
49		$extrafields = json_decode($row->extra_fields);
50		foreach ($extrafields as $field){
51			if (is_array($field->value)) $field->value = json_encode($field->value);
52			$sql = sprintf('INSERT INTO #__k2_cfcache VALUES (NULL, %s, %s, (SELECT NAME FROM `jos_k2_extra_fields` WHERE id = %s),"%s")',
53			$row->id, $field->id, $field->id, $field->value);
54			$db->setQuery($sql);
55			$db->query();
56		}
57	}