www.zeger.nl

We can make an adjustment to the generally accepted use of "fetch row from database and show" by using the GROUP_CONCAT function and JSON function, since we can recursively extend the JSON data this enables us to extend the data transportation mechanism without changing the code.

When building website using any CMS whatsoever we need a database to script connection. Transferring data is usually done by creating a simple query like:

SELECT 
	`cms_text`.`item_text` 
FROM 
	`cms` 
WHERE 
	`cms`.`text_id` = id

This should work out quite fine, we can select any text from the CMS by giving the corresponding text id.

Lets consider the following example:

SELECT
	`cms`.`item_text` AS `item_text`,
	`cms`author AS `author`
FROM
	`cms`
WHERE
	`cms`.`item_date` >  '2007-01-01'

We would like to show both the author and the text for all texts created after 2007-01-01, the following code can be used:

while( $row = get_row_from_query() )
{
	$text = $row['text'];
	$author = $row['author'];
 
	echo '<div class="item">';
	echo '<div class="text">' . $text . '</div>';
	echo '<div class="author">' . $author . '</div>';
	echo '</div>';
}

This code is used anywhere everywhere, but we can adjust it a little by getting more bang for the buck on the MySQL side. By using the GROUP_CONCAT function we can create a valid JSON string on the MySQL side.

Now consider the following query:

SELECT
	CONCAT(
		'{',
			GROUP_CONCAT(
				'"', `cms`.`text_id`, '" : ',   
				'{ "text" : "', `cms_text`.`item_text` AS `item_text`, '" , '
				'"author" : "', `cms_text.`author AS `author`, '" }'
				SEPARATOR ','
			),
		'}'
	)
FROM
	`cms`
WHERE
	`cms`.`item_date` > '2007-01-01'

On the PHP side we only have to decode the JSON string and we can access the data from a regular array.

We can use the following script:

$text = json_decode( get_row_from_query() );
foreach( $text as $text_id => $text_data )
{
	$text = $text_data['text'];
	$author = $text_data['author'];
 
	echo '<div class="item">';
	echo '<div class="text">' . $text . '</div>';
	echo '<div class="author">' . $author . '</div>';
	echo '</div>';
}

What are the advantages and disadvantages?

Firstly it could be faster since the database only has transfer a single block of data instead of a bunch of small pieces; however on the scripting side we have to decode the JSON data which decreases the performance.
Secondly by using stored functions we can recursively use this technique which allows for unlimited extension without any adjustment to the code. Extending the data result is extremely easy.