Get JSON from mysql result

  • baltakid

    (@baltakid)


    4 years ago

    Hello,
    i’m using this line of code ->$result = $wpdb->get_results("SELECT * FROM Table1");
    and its returning this

    {
  "status": "ok",
  "count": 1,
  "result": [
    {
      "id": "1",
      "field1": "Name"
    }
  ]
}
but i want it to return like this
{
  "status": "ok",
  "count": 1,
  "result": [
    {
      "id": "1",
      "field1": "Team",
      "Player": [
                   {
                     "other_table_id": "1",
                     "other_table_name1": "PlayerName",
                   }
                ]
    }
  ]
}

    is this possible using wordpress?

Viewing 4 replies - 1 through 4 (of 4 total)
  • Moderator bcworkz

    (@bcworkz)

    4 years ago

    Anything PHP can do is possible in WP. Your question appears to be more about querying for data from multiple tables. You can use JOIN syntax to relate one table to the other by some common element.
    https://dev.mysql.com/doc/refman/8.0/en/join.html

    Thread Starter baltakid

    (@baltakid)

    4 years ago

    With the INNER JOIN solution, if there was more than one player it will return a lot of repeat data.
    What i’m trying to get its that in the Player field it returns a Array/query of all players from that team

    something like this:

    {
  "status": "ok",
  "count": 1,
  "result": [
    {
      "id": "1",
      "field1": "Team",
      "Player": [
                   {
                     "other_table_id": "1",
                     "other_table_name1": "PlayerName",
                   },
                   {
                     "other_table_id": "2",
                     "other_table_name1": "PlayerName2",
                   },
                   {
                     "other_table_id": "3",
                     "other_table_name1": "PlayerName3",
                   }
                ]
    }
  ]
}
    Moderator bcworkz

    (@bcworkz)

    4 years ago

    You can use SELECT DISTINCT to avoid repeat results.
    https://www.w3schools.com/sql/sql_distinct.asp

    Thread Starter baltakid

    (@baltakid)

    4 years ago

    I have found a solution

    $result = $wpdb->get_results("SELECT Team.id AS id, Team.name AS name, '' AS Players
  FROM Team");

for ($i = 0; $i < count($result); $i++)
{
  $Team_id = $result[$i]->id;

  $result[$i]->Players = $wpdb->get_results("SELECT Player.id AS id, Player.name As Name
    FROM Player
    WHERE Player.team_Id = '$Team_id'");
}

$result = array("count" => count($result), "result" => $result);
if(count($result) > 0) { return $result; }
else { $json_api->error("Some error"); }

    don’t know if this is the best solution possible?

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Get JSON from mysql result’ is closed to new replies.