Building a more multidimensional JSON object with PHP/MySQL

So, first off - my goal is to build an SQL query that spits out the following JSON object. As you can see, it spits out some basic competition details and then each user associated with that competition. In addition, it also, for each user - shows the activities that they have performed.

{
        organisationId: 1,
        competitionId: "52eabcf0f3672",
        title: "Sales Hood Q1 Challenge",
        end_date: "2014-03-01 00:00:00",
        description: "This is it guys, challenge time!",
        prizeImage: "placeholder.jpg",
        prizeDescription: "Dinner for 2!",
        users: [{
            id: 2,
            name: "Jane Wilson",
            isAdmin: true,
            direction: "down",
            profilePic: "fighter-1.jpg",
            tagline: "My shit is consistently on fire",
            totalPoints: 40,
            isOnStreak: false,
            activities: [{ 
                id: 6431,
                time: (57).minutes().ago(),
                points: 20
            }, {
                id: 6431,
                time: new Date(),
                points: 20
            }]
        }, {
            id: 3,
            name: "Caroline Wilson",
            isAdmin: false,
            direction: "up",
            profilePic: "fighter-3.jpg",
            tagline: "I am the best motherfuckers",
            totalPoints: 60,
            isOnStreak: false,
            activities: [{ 
                id: 6431,
                time: (1).days().ago,
                points: 20
            }, {
                id: 6431,
                time: (2).days().ago,
                points: 20
            }, {
                id: 6431,
                time: new Date(),
                points: 20
            }]
        }, {
            id: 1,
            name: "Matthew Lloyd",
            isAdmin: false,
            direction: "down",
            profilePic: "placeholder.jpg",
            tagline: "Aref to the rescue!",
            totalPoints: 140,
            isOnStreak: false,
            activities: [{ 
                id: 6431,
                time: new Date(),
                points: 20
            }, {
                id: 6431,
                time: new Date(),
                points: 20
            }, {
                id: 6432,
                time: new Date(),
                points: 100
            }]
        }]
    };

I have the following SQL schema (available at this fiddle http://sqlfiddle.com/#!2/82d6e/1), and I can't seem to build a query that gives me what I want.

To clarify, in the database there are the following tables

  1. competitions
  2. users
  3. competitionmembers -> this is users invited to a competition
  4. activity_types -> the activities associated with that competition
  5. activity_entries -> the actual "performed" activities by a user

In terms of the SQL/PHP query, this is where I am at so far - but can't seem to get it right.

 $get = mysql_query("SELECT c.organisationId, c.competitionId, c.name, c.end_date, c.about, c.prizeImage, c.prize, u.name AS userName, u.id AS userId, u.profilePic, u.tagline
FROM competitions c
INNER JOIN users1 u ON c.organisationId = u.organisationId 
INNER JOIN competitionmembers m ON m.userid = u.id
WHERE c.competitionId = '52a99783c5d6f'") or die("Couldn't select competition details");


$arr = array();

while ($row = mysql_fetch_array($get)) {

    $arr = array(
        array(
            "competitionId" => $row["competitionId"],
            "title" => $row["name"],
        ),
        array(
            "id" => $row["userId"],
            "name" => $row["userName"],
        )
    );
}

echo json_encode($arr);

?>

Would love some help with this, if you could even closer to that json object that would be great!

Answers


You should make more queries:

  • Query1 = First get just competition data
  • Query2 = Get all users related to competition
  • Query3 = Get user related activities (with user keys collected)

I'm quite sure this will be faster and easier to manage. If you trying to use only one query, there will be much duplicate data and query will get really slow.

When you have the data, keep it in arrays and use functions like in_array, array_key_exists etc. to put all together.

PSEUDO CODE:

$competition = query1;

$users = query(get users in competition);

$competition["users"] = $users;

$user_ids = array();

foreach($users as $user)
  $user_ids[] = $user['id'];

$activities = query("select * from activities where id_user in (" . implode(",", $user_ids)) . ")";

for ($i = 0; $i < count($competition["users"]); $i++) {

  $competition["users"][$i]["activities"] = array(); 

  foreach($activities as $act) {

    if ($competition["users"][$i]['id'] == $act["id_user"]) {

       // Add activity..
       $competition["users"][$i]["activities"][] = $act;

    }
  }
}

echo json_encode($competition);

Need Your Help

Resolve dependencies when calling constructor using DI with ASP.NET 5

c# asp.net asp.net-5

The web seems flooded with examples on how to use DI with ASP.NET 5 but not one of the examples shows how to call a constructor and resolve dependencies.

Casting to List(Of T) in VB.NET

vb.net generics casting

In C# it's possible to cast to List&lt;T&gt; - so if you have:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.