getting JSON data-tree from MySQL

Question

I have a newsfeed similar to Facebook's newsfeed.

It means that I need to get not "straight" rows & cols datasets from MySQL but more like "trees" of data with different length of branches (and different contents).

If I go straight I will end up with lots of nested cycles in PHP - because each type of news needs different data sets.

example of newsfeed: 1) User "A" has added 2 new images (here I have to get user_name, user_avatar, image_path_1, image_path_2 etc..) 2) User "B" has commented Users's "A" image (here I need User's "B" name, User's "B" avatar, image_path, comment_text, User's "A" name etc...)

See what I mean?

So what I want to do is to get data from MySQL Stored Procedure converted to JSON format.

{"news": 
            [
               {news_type: "new_images", user_name: "john", user_avatar: "0002.jpg", image_path: "/images/00123.jpg"}
               {news_type: "comment", user_name: "john", user_avatar: "0002.jpg", comment: "hello!"}
            ]
      }

I can do this conversion in MySQL by "concat" and "group_concat"... I just want to ask your opinion if it's a good idea or not - and if not then what is a better way.

Thanks for your help!

UPDATE so finally I switched to MongoDB :)

Joseph Lust · Accepted Answer · 2011-01-05 18:20:52Z

4

This would not be wise. You should only use your database server for database work.

PHP will automatically do all the JSON encoding for you with json_encode() if you just pass it an associative array. No point reinventing the wheel or bogging down your database server.

answered Jan 5, 2011 at 18:20

Joseph Lust

20.1k8 gold badges91 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ChrisP · Accepted Answer · 2011-03-28 14:03:44Z

0

It would be handy to have a stream output type though: less unmarshaling/marshaling. And if you need to support php and java consumers its more data handling. It'd seem like less work to hook up an output stream and call a stored procedure.

answered Mar 28, 2011 at 14:03

ChrisP

1

Collectives™ on Stack Overflow

getting JSON data-tree from MySQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related