1

Let's say that I have created two tables in my database: 

`INSERT INTO `months` (`month_id`, `month_name`) VALUES ('1', 'January');`

and 

`INSERT INTO `weeks_and_days` (`week_id`, `week_nr`, `day_nr`) VALUES ('1', '1', '1'), ('2', '1', '2'), ('3', '1', '3'), ('4', '1', '4'), ('5', '1', '5'), ('6', '1', '6'), ('7', '1', '7');`

What should be the correct PHP and SQL code to get exactly the following nested JSON:

{ "month_name": "Yanuary", "week_nr": { "1": [{ "day_nr": [1, 2, 3, 4, 5, 6, 7] }] } }

I read a couple of answers and tried to apply them in my code, but I faild. I need some simple formula as I am quite new in programming.

Rico11112016
  • 75
  • 8

1 Answers1

0

Instead of directly generating the json off MySQL, you should write the queries to get different values and put them together to generate json, e.g.:

SELECT month_name
FROM months
WHERE month_id = 1;

SELECT week_nr, GROUP_CONCAT(day_nr)
FROM weeks_and_days
GROUP BY week_nr;

First query gives you the month name and second query gives you the week/day info.

Please note that weeks_and_days table does not have any column for month_id, it needs one to map to months table (via foreign key).

Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
  • Can I use `week_id` to map with `month_id`? What about the PHP code for the querries you showed? – Rico11112016 Apr 10 '17 at 22:26
  • @Rico11112016 you can't use `week_id` to map with `month_id` as one month will have muliple weeks. You need to have a separate column for this. For php code, have a look at this (http://stackoverflow.com/questions/383631/json-encode-mysql-results) and this (http://stackoverflow.com/questions/3281354/create-json-object-the-correct-way) link. – Darshan Mehta Apr 10 '17 at 22:33
  • how would the querries change if I want each `[1, 2, 3, 4, 5, 6, 7]` as a **separate** objects, so "1" would be a separate object, then "2" and so on? – Rico11112016 Apr 11 '17 at 09:42
  • `SELECT week_nr, day_nr FROM weeks_and_days GROUP BY week_nr, day_nr;` – Darshan Mehta Apr 11 '17 at 10:11