SQL tips #1

With all of the talk about NoSQL going around, it is clear that people want better ways of storing large
amounts of data in their databases. However, before you go running off to implement one of these new
systems, don’t forget that SQL has a few tricks of its own. Specifically, I am speaking of the BLOB
(binary large object) data type, which allows you to store a variety of data within a single column, and
the TEXT data type, which allows for extremely large pieces of character data to be stored. While it is
clear that you can just store a single large piece of data per cell, you can also, pack and unpack small
pieces. For example, if you happen to have a large array (associative or not) of data in php that you
would like so store in a database. One example of a way to implement this (at least the way that I like
to do things) is to first json_encode the array to make sure it is in a format the TEXT or BLOB field
can accept, which is done like so:

$blah = json_encode($blah);

That array can contain anything that php will allow, including nested arrays, and this will still work.
Next, all you have to do is insert the database with a query like you would with any other data. When
you want that data back, you query the database with a select statement that then json_decode that data.

Doing things this way can allow you to pack tables full of data and still only have two columns to
worry about: the BLOB/TEXT column, and a some arbitrary id column that will allow you to query
data by specific identifiers. Now go and try it out!

Categorized as Database

By Qushawn Clark

Qushawn is a staff writer for the iEntry Network

Leave a comment