Creating JSON in php

Question

It seems like I would be jumping through hoops to move data from MySQL into json via PHP json_encode - the encoding is taken care of, it's the pulling of the related data from the database and ensuring that you have array within array within array setup correctly (code snip below)...is there a json framework or something where you feed it the sql with joined tables and it does all the redundant/confusing work for you?

        function people_list() {
        $data['jdata'] =
            array(
                'groups' => 
                    array(
                        array(
                            'name' => 'DeveloperList',
                            'people' => array(
                                            array('name' => 'sam sneed'),
                                            array('name' => 'sue summer')
                                        )
                        ),
                        array(
                            'name' => 'PMList',
                            'people' => array(
                                            array('name' => 'tim pm'),
                                            array('name' => 'sara pm')
                                        )
                        ),
                        array(
                            'name' => 'ClientList',
                            'people' => array(
                                            array('name' => 'Mr Smith'),
                                            array('name' => 'Ms Jones')
                                        )
                        )
                    )
            );

        $this->load->view('json', $data);            
    }

Answer 1

If you are using PDO, you could do

$pdo_stmt = PDO::prepare($your_sql);
$pdo_stmt->execute();
$json_obj = json_encode($pdo_stmt->fetchAll(PDO::FETCH_ASSOC));

That doesn't do the querying for you, but it will give you a JSON object with all of the rows and columns from your query without you having to specify everything.

Answer 2

If you were to use an ORM like Doctrine, you could get your data back as a more structured array, which would be preserved when you call json_encode().

Here is some relevant documentation.

Answer 3

Unless I'm not understanding your question/problem correctly, it sounds like you're going about fixing it the wrong way. If you use a proper degree of abstraction, then you should be able to easily validate data structure going into the database and then reasonably expect that same structure to come back out again.

It's one of those "garbage in, garbage out" principles - deal with garbage earlier to save yourself time later.