5 Replies - 3948 Views - Last Post: 27 April 2012 - 04:46 PM Rate Topic: -----

#1 hiddenghost  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 39
  • View blog
  • Posts: 621
  • Joined: 15-December 09

JSON for mysql hierarchy

Posted 10 April 2012 - 07:11 AM

I'm wondering would it be copacetic to use JSON to create a tree in MYSQL using php?

Some how making a tree out of the branches of the JSON.

I know it's more of a database question, but it's kinda a funky thing that would take some programming to implement.
Is This A Good Question/Topic? 0
  • +

Replies To: JSON for mysql hierarchy

#2 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3515
  • View blog
  • Posts: 10,144
  • Joined: 08-June 10

Re: JSON for mysql hierarchy

Posted 10 April 2012 - 07:18 AM

IIRC, MySQL (unlike some others) is not able to store data trees directly.
Was This Post Helpful? 0
  • +
  • -

#3 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1003
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: JSON for mysql hierarchy

Posted 10 April 2012 - 07:59 AM

What exactly do you mean by this?

Of course you could take some JSON and use PHP to translate it into SQL for insertion into the DB but I wouldn't see the point. Indirectly this is exactly what you'd normally do in a communication scenario such as AJAX etc but for general purpose on your application I don't see the point.
Was This Post Helpful? 0
  • +
  • -

#4 hiddenghost  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 39
  • View blog
  • Posts: 621
  • Joined: 15-December 09

Re: JSON for mysql hierarchy

Posted 10 April 2012 - 01:46 PM

I was trying to think of a good example, but I was have a hard time.

I suppose it would be JSON used directly in a column of a database to represent
key value pairs.

The top of the JSON:
Simply
{

The first and second member:
firstmember :
second :

First member has some members:
firstmember : { f1 :
firstmember : { f2 :

f1 has some data members:
f1 : { the : [ enterprise is going..., ... ]
f1 : { tribbles : [ everywhere, ... ]
f1 : { control : [ your tribbles, ... ]
f1 : { your : [ tribbles are out of control, ... ]
f1 : { mind : [ #*($, ... ]

So one column for data and hierarchy.
The [] here whould represent seperate rows with the appropriate name to the left of the : like:
tribbles : [ everywhere
tribbles : [ on the side

Data would be retrieved based on positions of characters.
left_title : { subtitle_right
subtitle_left : [ data_right

Sorry it's a horrible example.
It might be better emplemented with a many to many relationship.
Left table = titles; Right table = data
Haven't thought it all the way through, but the idea is to put json formatting directly in the db rows for hierarchal structure.

One row is possible too, but some strange wild card usage, and/or regex would have to be used.

Seems complicated, but could be an interesting experiment with dividends.
Was This Post Helpful? 0
  • +
  • -

#5 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: JSON for mysql hierarchy

Posted 11 April 2012 - 08:43 AM

Reading that made my brain bleed a little. Anyhow, using MySQL to store hierarchical data is not ideal. PostgreSQL and MSSQL are about a million times better for it. I say about, because a million is in the vicinity of infinity.

I've recently migrated my application over to PostgreSQL at the expense of 3 weeks coding time, just so that I could get hierarchical data working on DB server-side, rather than putting PHP in an electronic gulag and telling it to mine for the remainder of it's natural life.

Also, storing array data, objects, whatever you want to call them, in a database can be exdtremely bad. I say can be because sometimes you won't get hammered by future needs, but if you do, then bam you're in a world of pain. I speak from experience - I've made that mistake myself. It's horrible. You cannot query the data with any sort of efficiency, and fixing faulty data often involves getting in the DB and doing it by hand, which can be a nightmare.

If you need hierarchical data, anbd you're bound to MySQL, try using XML files instead. For things such as menus, it will be fine. For rapid dynamic data, it won't be good, but if you're looking at that sort of thing you probably have a large application, and you're probably going to want to migrate to PostgreSQL/MSSQL at some stage anyhow.
Was This Post Helpful? 1
  • +
  • -

#6 hiddenghost  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 39
  • View blog
  • Posts: 621
  • Joined: 15-December 09

Re: JSON for mysql hierarchy

Posted 27 April 2012 - 04:46 PM

View Poste_i_pi, on 11 April 2012 - 10:43 AM, said:

Reading that made my brain bleed a little. Anyhow, using MySQL to store hierarchical data is not ideal. PostgreSQL and MSSQL are about a million times better for it. I say about, because a million is in the vicinity of infinity.

I can see that making you brain bleed. It made mine bleed writing it. :)

View Poste_i_pi, on 11 April 2012 - 10:43 AM, said:

I've recently migrated my application over to PostgreSQL at the expense of 3 weeks coding time, just so that I could get hierarchical data working on DB server-side, rather than putting PHP in an electronic gulag and telling it to mine for the remainder of it's natural life.

Also, storing array data, objects, whatever you want to call them, in a database can be exdtremely bad. I say can be because sometimes you won't get hammered by future needs, but if you do, then bam you're in a world of pain. I speak from experience - I've made that mistake myself. It's horrible. You cannot query the data with any sort of efficiency, and fixing faulty data often involves getting in the DB and doing it by hand, which can be a nightmare.

If you need hierarchical data, anbd you're bound to MySQL, try using XML files instead. For things such as menus, it will be fine. For rapid dynamic data, it won't be good, but if you're looking at that sort of thing you probably have a large application, and you're probably going to want to migrate to PostgreSQL/MSSQL at some stage anyhow.

I have consciously avoided using structures in the database fields up until now. I thought, you never know there might be way I haven't though of. There is something to say about using a tool for what it's meant for that's for sure.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1