Getting Top 10 Scores From SQL Leaderboard w/ JSON + PHP

Solar2D Network
#1

Hey, I’m trying to code a leaderboard that is displayed inside my app. I have no physical leaderboard within my mysql database, just a user table. The way I’m planning on filling the leaderboard within the app is by using this sql statement “SELECT * FROM user ORDER BY points DESC LIMIT 10”. I am able to select the users with the top 10 points, however I am not sure how to return 10 different arrays through json back to my app. Can anyone help?

Thanks ahead of time! [import]uid: 111561 topic_id: 28794 reply_id: 328794[/import]

#2

My brain isn’t capable of writing the code right now, but in PHP you would construct a new PHP Array that would hold the 10 records returned from the MySQL query. You would loop over the rows returned, get each of the values returned and shove them into the array. Each array element would have it’s own array for each member value… something like:

$response = Array();$i = 1;while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { printf("ID: %s Name: %s", $row["id"], $row["name"]); $response[$i] = Array(); $response[$i]['name] = $row['name']; $response[$i]['rank] = $i; $response[$i]['score] = $row['score']; $i++;}echo json_encode($response);[/code]then in Corona once you got the response you would just json.decode(event.response) into your leaderboard table, [code]local leaderboard = json.decode(event.response)for i = 1, #leaderboard do print(leaderboard[i].name, leaderboard[i].rank, leaderboard[i].score)end[/code]See my blog post for more details (though it doesn't get into the multi-row data handler)http://omnigeek.robmiracle.com/2012/04/15/using-corona-sdk-with-rest-api-services/ [import]uid: 19626 topic_id: 28794 reply_id: 116103[/import]

#3

Awesome thanks for the response! Last night I came up with this solution with some help from Lerg on the #corona irc channel.

-------PHP

while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
$response[] = array(name’ => $row[‘name’], ‘score’ => $row[‘score’]);
}

echo json_encode($response);

--------LUA

local function networkListener( event )
if ( event.isError ) then
print( “Network error!”)
else
local data = json.decode(event.response)
for i=1, 10 do
name[i] = data[i].name
score[i] = data[i].score
end
ranktext.text = "1 " … data[1].name
ranktext:setReferencePoint(display.CenterLeftReferencePoint)
ranktext.x = _W*.725
end
end

network.request( " yoururl.com/leaderboardsfile.php", “GET”, networkListener )
Hopefully these 2 solutions can be of use to someone in the future who is coming across the same troubles as I was last night! BTW the #corona IRC channel is an amazing place for realtime solutions! [import]uid: 111561 topic_id: 28794 reply_id: 116106[/import]