SQL to PHP to JSON

Get help using Construct 2

Post » Fri Jun 14, 2013 1:30 pm

Hi !

I'm trying to read highscore from an SQL database, I need 10 results containing each 3 datas (ID, PSEUDO, SCORE).

I found this on JSON on the forum, but it's only return one line :


     $json_data = array();

          while ($data = $req->fetch())
          {
               $json_data = array(array(array($data['id'])), array(array($data['pseudo'])), array(array($data['score'])));
          }
     $json_data = json_encode($json_data);
     echo '{"c2array":true,"size":[3,1,1],"data":';
     echo $json_data;
     echo '}';


And here is my response :
{"c2array":true,"size":[10,3,1],"data":[[["2"]],[["User"]],[["1000"]]]}

How to have multiple lines ? Can't find clear stuff about JSON ...
Thanks !
B
39
S
15
G
5
Posts: 295
Reputation: 6,089

Post » Fri Jun 14, 2013 7:06 pm

Here is the one I am using in my game, I modified this a bit for your use. Also check out my tutorial,which uses a similiar method for posting to MySQL.
https://www.scirra.com/tutorials/585/how-to-make-an-ingame-costume-made-level-editor

<?php
$con=mysqli_connect("");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT * FROM test");
$json_data = array();
echo '{"c2array":true,"size":[10,3,1],"data":';
while($row = mysqli_fetch_array($result))
{
    $json_data = array(array(array($row['id'])), array(array($row['pseudo'])), array(array($row['score'])));
$json_data = json_encode($json_data);
echo $json_data;
echo ",";
//To separate two rows
}
   echo '}';
mysqli_close($con);
?>trollface2013-06-14 19:11:43
B
14
S
5
G
4
Posts: 107
Reputation: 4,788

Post » Mon Jun 17, 2013 10:53 am

@trollface

Thanks for your help, now I'm getting data, but I can't load them via JSON into an array :

{"c2array":true,"size":[10,3,1],"data":[[["6"]],[["Joski"]],[["3512"]]],[[["4"]],[["Joski"]],[["3392"]]],[[["5"]],[["Joski"]],[["2538"]]],[[["3"]],[["Joski"]],[["1908"]]],[[["1"]],[["Paf"]],[["327"]]],[[["2"]],[["Vlon"]],[["133"]]],}

Is this json wrong ?
B
39
S
15
G
5
Posts: 295
Reputation: 6,089

Post » Mon Jun 17, 2013 11:26 am

There's a trailing coma... the JSON parser might not like it.
B
63
S
22
G
14
Posts: 1,482
Reputation: 16,432

Post » Mon Jun 17, 2013 12:51 pm

Even without the last comma I cant load from JSON into an array. Here's is what my php page returns :

{"c2array":true,"size":[10,3,1],"data":
[[["6"]],[["Joski"]],[["3512"]]],
[[["4"]],[["Joski"]],[["3392"]]],
[[["5"]],[["Joski"]],[["2538"]]],
[[["3"]],[["Joski"]],[["1908"]]],
[[["1"]],[["Joski"]],[["3227"]]],
[[["2"]],[["Joski"]],[["1333"]]]
}


After Load Json into Array, I test if the value at array(0,0) is not egal to 0, if it's not the data are loaded succesfuly else there is an error. The value at 0,0 return 0...Joskin2013-06-17 13:15:36
B
39
S
15
G
5
Posts: 295
Reputation: 6,089

Post » Mon Jun 17, 2013 1:33 pm

Ah ok your format is wrong
it should be:[code]{"c2array":true,"size":[10,3,1],"data":
[
    [["6"],["Joski"],["3512"]],
    [["4"],["Joski"],["3392"]],
    [["5"],["Joski"],["2538"]],
    [["3"],["Joski"],["1908"]],
    [["1"],["Joski"],["3227"]],
    [["2"],["Joski"],["1333"]]
]
}[/code]because data should be an is an Array, of 10 Array of 3 array of length 1

if the dimension where (10,1,1) it would be an array of 10 array of 1 array of length 1[code]{"c2array":true,"size":[10,1,1],"data":
[
    [["6"]],
    [["4"]],
    [["5"]],
    [["3"]],
    [["1"]],
    [["2"]]
]
}[/code]

and if the dimension where something like (10,3,2), it would be an array of 10 array of 3 array of length 2[code]{"c2array":true,"size":[10,3,2],"data":
[
    [["6","7"],["Joski","Smith"],["3512","1337"]],
    [["4","5"],["Joski","Smith"],["3392","1337"]],
    [["5","6"],["Joski","Smith"],["2538","1337"]],
    [["3","4"],["Joski","Smith"],["1908","1337"]],
    [["1","2"],["Joski","Smith"],["3227","1337"]],
    [["2","3"],["Joski","Smith"],["1333","1337"]]
]
}[/code]
B
63
S
22
G
14
Posts: 1,482
Reputation: 16,432

Post » Mon Jun 17, 2013 2:08 pm

@Yann, thanks a lot for this clarification on JSON array.
Even when I use your example on for my php page return, the data are not loaded into the array. If I print MyArray.AsJson I've :

PHP return :
{"c2array":true,"size":[10,3,1],"data":
[
    [["6"],["Joski"],["3512"]],
    [["4"],["Joski"],["3392"]],
    [["5"],["Joski"],["2538"]],
    [["3"],["Joski"],["1908"]],
    [["1"],["Joski"],["3227"]],
    [["2"],["Joski"],["1333"]] (with or without comma ",")
]
}

Array as json return :

{"c2array":true, "size":[0,0,0],"data":[]}

=> Empty array so.

What's wrong with this, using Azure plugin and Json without problem, and now I'm totaly lost.Joskin2013-06-17 14:10:27
B
39
S
15
G
5
Posts: 295
Reputation: 6,089

Post » Mon Jun 17, 2013 2:33 pm

If I load the string I provided directly in C2, it works, though you have to escape " not with \" but with ""

However since you provide it via php, character escaping shouldn't be too much of an issue (at least on c2 side)

So what I wonder is, how do you get the data? You're using the AJAX plugin right?Yann2013-06-17 14:35:05
B
63
S
22
G
14
Posts: 1,482
Reputation: 16,432

Post » Mon Jun 17, 2013 2:38 pm

Yep I'm using the AJAX plugin and the action AJAX.LastData.

And even with escaping string with "" : I've got an error.

I'm trying to output
ID - Pseudo - Score

10 lines, 3 columns.

I think I ll try with token.Joskin2013-06-17 15:28:42
B
39
S
15
G
5
Posts: 295
Reputation: 6,089

Post » Mon Jun 17, 2013 4:18 pm

show the capx and the php file
B
63
S
22
G
14
Posts: 1,482
Reputation: 16,432

Next

Return to How do I....?

Who is online

Users browsing this forum: AekiroStudio and 7 guests