How to produce a valid json output from multiple MySQL tables

Question

I created a mySQL database with phpMyAdmin in my local server. In this database I store the names and the favourite NBA teams of my friends.This is obviously a many-to-many relationship. For this a reason, I created three tables: one with the id & name of my friends, one with the id & name of the teams and one with friends_id and teams_id (which is the relations table). This is more clearly shown by the following MySQL script:

CREATE TABLE `friends` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `teams` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `relations` (
  `friends_id` int(4) NOT NULL AUTO_INCREMENT,
  `teams_id` int(4) NOT NULL AUTO_INCREMENT,
)

I want to give an json output with these data and for this reason I run the following PHP script:

<?php

$dbServername = 'localhost';
$dbUsername = 'root';
$dbPassword = '';
$dbName = 'Friends';

$conn = mysqli_connect($dbServername, $dbUsername, $dbPassword, $dbName);

header('Content-Type: application/json');

$sql = 'SELECT * FROM friends;';
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
$arr = [];
if ($resultCheck > 0) {
   while ($row = mysqli_fetch_assoc($result)) {
        $arr[] = $row; 
   }
} 
echo json_encode($arr, JSON_PRETTY_PRINT);

$sql = 'SELECT * FROM teams;';
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
$arr = [];
if ($resultCheck > 0) {
   while ($row = mysqli_fetch_assoc($result)) {
        $arr[] = $row; 
   }
} 
echo json_encode($arr, JSON_PRETTY_PRINT);

$sql = 'SELECT * FROM relations;';
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
$arr = [];
if ($resultCheck > 0) {
   while ($row = mysqli_fetch_assoc($result)) {
        $arr[] = $row; 
   }
} 
echo json_encode($arr, JSON_PRETTY_PRINT);
?>

However this does not give a valid json output overall but three distinct json arrays. For example, something like this:

[..., 
{
        "id": "3",
        "name": "David Belton",
    },
...]

[..., 
{
        "id": "4",
        "name": "Boston Celtics",
    },
...]

[..., 
{
        "friends_id": "3",
        "teams_id": "4"
    },
...]

How can I print all of my tables with having a valid json output?

this question is related to https://stackoverflow.com/questions/49279952/how-to-join-arrays-with-mysql-from-3-tables-of-many-to-many-relationship — Raymond Nijland, Mar 14 '18 at 15:07

score 1 · Answer 1 · answered Mar 14 '18 at 15:24

Because this question relates to How to join arrays with MySQL from 3 tables of many-to-many relationship iám posting a MySQL only answer.

Query

SELECT
  CONCAT(
      "["
    , GROUP_CONCAT(json_records.json)
    , "]"
  )  AS json
FROM (

  SELECT 
     CONCAT(
       "{"
     ,     '"id"' , ":" , '"' , friends.id , '"' , ","
     ,     '"name"' , ":" , '"' , friends.name , '"' , ","
     ,     '"team"' , ":" , "["
                              , GROUP_CONCAT('"', teams.name, '"')
                          , "]"
     , "}"
     ) AS json 
  FROM 
    friends 
  INNER JOIN 
    relations 
  ON 
    friends.id = relations.friends_id
  INNER JOIN
    teams 
  ON
    relations.teams_id = teams.id
  WHERE 
    friends.id IN(SELECT id FROM friends) #select the friends you need
  GROUP BY
     friends.id
) 
 AS json_records

Result

|                                                                                                                                             json |
|--------------------------------------------------------------------------------------------------------------------------------------------------|
| [{"id":"1","name":"David Belton","team":["Cleveland Cavaliers"]},{"id":"2","name":"Alex James","team":["Boston Celtics","Cleveland Cavaliers"]}] |

demo

http://www.sqlfiddle.com/#!9/4cd244/61

Do not be offended @Raymond but I do not think that this directly related to my precious post. Because finally I want to print the joined table but also a table of the teams separately. So again I want to print more than one tables. — Outcast, Mar 14 '18 at 15:36

NIKHIL NEDIYODATH · Answer 2 · 2018-03-14T15:46:42.450

0

You need not to write muliple sql queries for retrieve this. You can do the following

<?php
$dbServername = 'localhost';
$dbUsername = 'root';
$dbPassword = '';
$dbName = 'Friends';

$conn = mysqli_connect($dbServername, $dbUsername, $dbPassword, $dbName);

header('Content-Type: application/json');

$sql = 'SELECT * FROM friends INNER JOIN relations ON friends.id=relations.friends_id
INNER JOIN teams ON relations.teams_id=teams.id';
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
$arr = [];
if ($resultCheck > 0) {
   while ($row = mysqli_fetch_assoc($result)) {
        $arr[] = $row; 
   }
} 
echo json_encode($arr, JSON_PRETTY_PRINT);
?>

edited Mar 14 '18 at 15:46

answered Mar 14 '18 at 15:10

NIKHIL NEDIYODATH

2,703
5
24
30

I get the following error for some reason `
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /opt/lampp/htdocs/index.php on line 17
` – Outcast Mar 14 '18 at 15:16
for this line `$resultCheck = mysqli_num_rows($result);` – Outcast Mar 14 '18 at 15:17
Can you share your table structure, please? – NIKHIL NEDIYODATH Mar 14 '18 at 15:24
OK. Are you sure about `friends_id` in `SELECT CONVERT(varchar(30), friends_id)`? – Outcast Mar 14 '18 at 15:46
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/166830/discussion-between-nikhil-nediyodath-and-poete-maudit). – NIKHIL NEDIYODATH Mar 14 '18 at 15:47

How to produce a valid json output from multiple MySQL tables

2 Answers2

Linked