retrieving data from two tables into multidimensional array api json response

Question

I am learning Api development where i want to retrieve data from two tables

First table (items) contains:

id, feed_id, title, author, content

Second table (feeds):

feed_id,title,url

I want to get Json response like:

 {
      - stories :
            [
               id
               feed_id {
                          title,
                          url
                       }
               title
               author
            ]
}

My PHP function to retrieve story biased on id is like:

$app->get('/stories/:id', 'authenticate', function($story_id) { 
    $response = array();
    $result = Database::get('db')->table('items')
    ->eq('rowid', $story_id)
    ->findOne();
    //$response["error"] = false;
    if ($result != NULL) {
    $response['stories'] = array(
        'id' => $result["id"],
        'feed_id' => $result["feed_id"],
        'title' =>  $result["title"],
        'isBreaking' => $result['isBreaking'],
        'author' => $result['author'],
        'updated' => $result["updated"],
        'url' => $result['url'],
        'content' => $result["content"]);
         echoRespnse(200, $response);
    }
    else {
        $response["error"] = true;
        $response["message"] = "The requested resource doesn't exists";
        echoRespnse(404, $response);
    } 
});

The result i get is like:

 {
   - "stories": [
             {
                id
                feed_id
                title
                url
             }
           ]
}

How can i retrieve data from the second tablet (feeds) biased on feed_id and get the data into sub array?

you have mentioned two table names stories and feeds but in your code all I can see is one table name like items . what is your query for fetching data from db? — EhsanT
– EhsanT, Commented Mar 25, 2015 at 18:12
Sorry .. first table name is items not stories ... i have corrected it — Fshamri
– Fshamri, Commented Mar 25, 2015 at 18:54
Query across a join, load data into desired data structure, JSON-encode the data structure. Hard to give any advice beyond that, as it appers that whatever is really happening in your code here is hidden behind framework functionality. — Mike Brant
– Mike Brant, Commented Mar 25, 2015 at 18:59

EhsanT · Accepted Answer · 2015-03-25 19:48:44Z

1

as @MikeBrant mentioned you have to use join between two tables to retrieve correct data. the plain query will be something like this:

select items.id, items.title, items.author, items.content, feeds., feeds.title as feed_title, feeds.url from items left join feeds on items.feed_id = feeds.feed_id

and then change your $response['stories'] like this:

$response['stories'] = array(
'id' => $result["id"],
'feed_id' => array(
  'title' => $result["feed_title"],
  'url' => $result["url"]
),
'title' =>  $result["title"],
.
.
.
);

you get the idea :)

answered Mar 25, 2015 at 19:48

EhsanT

2,0843 gold badges27 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

retrieving data from two tables into multidimensional array api json response

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related