[thelist] searching and JSON

Nadeem Hosenbokus nadeem at nadeemh.com
Fri Sep 21 11:31:59 CDT 2012

Hi Bob,

May I suggest an alternative table design to accommodate your JSON exports:

ID - primary key for this table
FK - this would be the primary key from the table with the JSON string
Fieldtype - this describes the content of the next column
Data - this is the actual data

So if you wanted DOB and DOD in there you table would look like this:

| id | sourceid | fieldtype | data                | notes |
| 1  | 123      | dob       | 1977-06-25 00:00:00 |       |
| 2  | 456      | dob       | 1988-08-30 00:00:00 |       |

So you now have the date of birth of two different people there. Then if you
now wanted to track another JSON parameter, instead of changing your table
structure you just use a different 'fieldtype'. I actually learnt this from
looking at Drupal's database ;-)

Nadeem Hosenbokus
(230) 766 9169

-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Bob Meetin
Sent: 21 September 2012 19:11
To: thelist at lists.evolt.org
Subject: Re: [thelist] searching and JSON

On 09/21/2012 06:15 AM, Sarah Adams wrote:
>> Now I can use my framework for pretty much anything so I stick with it
because it does the job and I know every single line of code making it
easier to maintain and expand.
> I think this is the crux of the matter. If you were a Drupal expert
> you'd probably find it just as easy to add new features to a Drupal
> site, or maybe even easier since there is a ton of already-built stuff
> out there, so there's no need to reinvent the wheel. So what happens
> if you end up passing of the development/maintenance of a project to
> someone else? Would they find it easy to navigate your framework?
> Sarah
With Joomla, probably Drupal and Wordpress there are thousands of folks out
there already familiar with the framework, enough so to be able to take over
common projects. With very very simple web sites you don't need developer
skills to be able to handle administration. You do need to know how to read,
use google, and find the CMS' extensions directory where you can find
forums, shopping carts, calendars and elsewhere, themes and other
off-the-wall stuff.

Commonly with low-cost licensed extensions you get some level of support and
can rely on the developers for upgrades, new features. If you are developer
you learn the system and figure out how to implement custom code to fill in

You lose some freedom but gain in these other areas. What it really comes
down to is finding the system that meets your business needs.

BTW, regarding the subject line of this thread, I followed Renoir's
suggestion with adding some additional fields extracting the date out of
JSON formatted data to make searching easier. As it turned out we not only
needed to search/match but also to do some searches on date range so I
grabbed things like birthdate and set up a new table with birthdate as a
separate field.  To keep the table updated I have a script that will add a
new entry as necessary or update an existing entry.  The table is easily
extended. I'm sure there will be other JSON formatted fields that will find
a home here too.

mysql> select * from akxzm_k2_extra_fields_extended;
| id   | dob        | dod        | note    |
|   10 | 1887-01-01 | 1938-01-01 | NULL    |
|    5 | 1490-03-21 | 1983-01-01 | NULL    |
2 rows in set (0.02 sec)


* * Please support the community that supports you.  * *

For unsubscribe and other options, including the Tip Harvester
and archives of thelist go to: http://lists.evolt.org
Workers of the Web, evolt ! 

More information about the thelist mailing list