Get Array within Json

Question

Get Array within Json

Navigation

#1 by (0 votes)

1

I have two tables on MySql one Blog and another Carouser unit on a foreing key.

How can I get a json like the one I see at the end? An array inside an object. In PHP.

CREATE TABLE 'Blog' (
  'blogId' int(11) NOT NULL,
  'blogTitulo' varchar(100) NOT NULL,
  'blogSubtitulo' varchar(100) NOT NULL,
  'blogDate' datetime NOT NULL,
  'blogText' mediumtext NOT NULL,
  'userId' int(11) NOT NULL,
  'blogImg' varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE 'Carouser' (
  'imgId' int(11) NOT NULL,
  'imgName' varchar(100) NOT NULL,
  'blogId' int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE 'Carouser'
  ADD PRIMARY KEY ('imgId'),
  ADD KEY 'blogId' ('blogId');

JSON

[
  {
    "blogId": "11",
    "blogTitulo": "TITULO",
    "blogSubtitulo": "SUB",
    "blogDate": "0000-00-00 00:00:00",
    "blogText": "TEXTO",
    "userId": "1",
    "blogImg": "imagen1.jpg",
    "carouserImg": {
        "0": "imgane2.jpg",
        "1": "imgane3.jpg",
        "2": "imgane4.jpg",
        "3": "imgane5.jpg"
      }
  }
]

mysql php json

asked by Rodrigo Benito 17.01.2017 в 11:08

source

1 answer

Display date format in "d-m-y" and save to a database in "y-m-d" format Correctly configure routes in angularjs

score 0 · Answer 1

You could make a query to the blog table, store that in an array and then a query to the Carouser table and do iterations to build the array, but if the tables have a lot of data, you could query the Carouser table by every blog item of the first blog query, I leave the pseudocodigo of this last one.

array_blogs = new Array

blogs = Blog.execute_query("select * from BLOG")

blogs.each_one do |blog|
  item_blog = new Array

  item_blog["blogId"] = blog["blogId"]
  .
  .
  . 

  carousers = Carouser.execute_query("select * from CAROUSER where blog_id = " + blog.id)

  item_blog["carouserImg"] = carousers

  blogs.append(item_blog)

end

return json_encode(array_blogs)