Posts tagged MySQL

JSON has – over the past few months – start working its way more and more into my daily life as a web developer. I find myself using it for all sorts of solutions, whether i’m using ActionScript or PHP. For those of you who don’t know, JSON stands for JavaScript Object Notation. JSON is an extremely lightweight, human-readable and highly compacted data-interchange format. JSON works on the principle of name/value pairing, and it’s very easy to read, write and parse.

A typical JSON representation of the following PHP object would be:

{"title":"Using MySQL and JSON","date":"25 April 2009","timeOfWriting":"15h34"}

1
2
3
4
5
6

<?php
      	$blogPost = new stdClass();
	$blogPost->title = "Using MySQL and JSON";
	$blogPost->date = "25 April 2009";
	$blogPost->timeOfWriting = "15h34";
?>

and the following ActionScript object would also be represented as it was above:

1
2
3
4
5
6
7

var blogPost:Object = new Object();
blogPost.title = "Using MySQL and JSON";
blogPost.date = "25 April 2009";
blogPost.timeOfWriting = "15h34";
 
// -----or-----
//var blogPost:Object = {title:"Using MySQL and JSON", date:"25 April 2009", timeOfWriting:"15h34"};

JSON can be extremely useful when you want to pass data from any platform to any other platform as the format is – essentially – just plain ol’ text and computers can parse JSON objects very easily.

In PHP5, this is supported natively, using the json_encode and json_decode functions.
However, in ActionScript 3.0, the parsing of JSON strings is not supported in the native ActionScript 3.0 API, so you can either write one yourself or use the JSONEncoder or JSONDecoder classes found in the as3corelib package.

Here’s how to encode/decode a JSON string in PHP:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

<?php
	$blogPost = new stdClass();
	$blogPost->title = "Using MySQL and JSON";
	$blogPost->date = "25 April 2009";
	$blogPost->timeOfWriting = "15h34";
 
	// -----or-----
	// $blogPost = array("title" => "Using MySQL and JSON",
	//					"date" => "25 April 2009",
	//					"timeOfWriting" => "15h34");
 
	$json = json_encode($blogPost);
	echo "Encoded JSON: ".$json;
	echo "<br/>Decoded JSON (as associative array):<br/>";
	print_r(json_decode($json, true));
	echo "<br/>Decoded JSON (as stdClass object):<br/>";
	print_r(json_decode($json));
?>

…and in ActionScript 3.0 using the as3corelib package:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

import mx.utils.ObjectUtil;
import com.adobe.serialization.json.JSONDecoder;
import com.adobe.serialization.json.JSONEncoder;
 
var blogPost:Object = new Object();
blogPost.title = "Using MySQL and JSON";
blogPost.date = "25 April 2009";
blogPost.timeOfWriting = "15h34";
 
// -----or-----
//var blogPost:Object = {title:"Using MySQL and JSON", date:"25 April 2009", timeOfWriting:"15h34"};
 
var json:String = new JSONEncoder(blogPost).getString()
trace("Encoded JSON: " + json);
trace("Decoded JSON: " + ObjectUtil.toString(new JSONDecoder(json).getValue()));

Now that we’ve gotten that out of the way, let’s have a look at how we can use JSON inside of MySQL. First off, let me say that i’m a big fan of structuring one’s databases correctly and not taking hideous shortcuts like i’m about to show you, but sometimes doing this hideous shortcut might actually help you develop your appplications faster, and keep things extensible.

I’ve found that i often have to keep changing my database structure when clients have changes of heart (and man, do they change them often!). It becomes extremely frustrating to have to restructure an entire database (and all associated PHP/AS3 code) every time a client remembers that he forgot about needing to store something as ridiculous the big-toe girdths of his future website’s users.

Now, i’m certainly not a patient person, and – to be honest – i don’t have time to fuck about and keep changes logic just to accomodate menial extra pieces of info. What i do therefore is usually keep a settings column in the users table, set it’s type to Text and store JSON strings in there to keep track of all the silly idiosyncratic (and sometimes idiotic) requirements of the client. Then all i have to do is parse out that string and apply some business logic to the values i find in that row under the settings column.

I find that it makes things a lot simpler and with orders of magnitude less ballaches.

What do you think?