Php Json_encode issue

[joshmrtin9]

I have a database created with data im trying to retreive via php and then encode into json for an iphone/andriod application.

The connection to my database works fine and i am able to retreive data and "encode" it so it seems. The format however is not correct. I can manually tweek the result and view the data on the phone like i need to but maybe someone can point out what im overlooking.

 

Here is the code i am working with:

 

$sql = "SELECT * FROM BUSA";
$results = sqlsrv_query($conn, $sql);
if (sqlsrv_has_rows($results)) {
while ($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC))
{
echo json_encode($row);
}
 
} else {
echo "No Results were Found";
}
 
/* Close the connection. */
sqlsrv_close( $conn);

 

As i said i do get results from this and it looks like this:

{"Jersey_Number":"12 ","Position":" ","Name":" 01. Brannon, Melanie ","Games_Played":" 10 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"}
{"Jersey_Number":"91 ","Position":" ","Name":" 02. Campbell, Claudia ","Games_Played":" 2 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"}
{"Jersey_Number":"15 ","Position":" ","Name":" 03. Carpenter, Alyssa ","Games_Played":" 2 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"}
{"Jersey_Number":"76 ","Position":" ","Name":" 04. Casey, Amy ","Games_Played":" 17 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"}
{"Jersey_Number":"14 ","Position":" F ","Name":" 05. Degenaar, Lia ","Games_Played":" 5 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"}
{"Jersey_Number":"25 ","Position":" M ","Name":" 06. Dumas, Natalie ","Games_Played":" 17 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"}
{"Jersey_Number":"7 ","Position":" ","Name":" 07. Eubanks, Anna ","Games_Played":" 11 ","Goals":" 2 ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"}
{"Jersey_Number":"28 ","Position":" ","Name":" 08. Greene, Jasmine ","Games_Played":" 2 ","Goals":" 2 ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"}
{"Jersey_Number":"3 ","Position":" F ","Name":" 09. Hamilton, August ","Games_Played":" 17 ","Goals":" 3 ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"}
{"Jersey_Number":"17 ","Position":" ","Name":" 10. Hoaglund, Lauren ","Games_Played":" 17 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"}

 

Now where i'm having the issue is this encodes each query as individual objects but there is no comma seperation between objects. So what i did to manually make this work was place commas between all objects and then [ results.. ] place all the results in bracket to make it an array of objects. Any suggestions would be greatley appreciated!

Edited April 22, 2014 by Zane

[Jacques1]

The reason why you get each array encoded individually is because that's exactly what you've told PHP to do: You have a loop, and in this loop, you encode and echo each $row.

 

If you want to collect all rows in an array and then encode the whole thing, well, do that.

[joshmrtin9]

Thanks, i knew it was a rather trivial question.

 

Fixed it with this:

 

$sql = "SELECT * FROM BUSA";
$results = sqlsrv_query($conn, $sql);
if (sqlsrv_has_rows($results)) {
while ($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC))
$roster[] = $row;
{
echo json_encode($roster);
}

} else {
echo "No Results were Found";
}

/* Close the connection. */
sqlsrv_close( $conn);

[Jacques1]

What are those weird braces after the $roster[] = $row supposed to do?

[joshmrtin9]

That put all my results from $row into one big array. When i run my code using the above fix i get these results from my json_encode.

 

 

[{"Jersey_Number":"# ","Position":" P ","Name":" Name ","Games_Played":" GP ","Goals":" G ","Assists":" A ","Yellow_Cards":" Y ","Red_Cards":" R"},{"Jersey_Number":"12 ","Position":" ","Name":" 01. Brannon, Melanie ","Games_Played":" 10 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"91 ","Position":" ","Name":" 02. Campbell, Claudia ","Games_Played":" 2 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"15 ","Position":" ","Name":" 03. Carpenter, Alyssa ","Games_Played":" 2 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"76 ","Position":" ","Name":" 04. Casey, Amy ","Games_Played":" 17 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"14 ","Position":" F ","Name":" 05. Degenaar, Lia ","Games_Played":" 5 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"25 ","Position":" M ","Name":" 06. Dumas, Natalie ","Games_Played":" 17 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"7 ","Position":" ","Name":" 07. Eubanks, Anna ","Games_Played":" 11 ","Goals":" 2 ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"28 ","Position":" ","Name":" 08. Greene, Jasmine ","Games_Played":" 2 ","Goals":" 2 ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"3 ","Position":" F ","Name":" 09. Hamilton, August ","Games_Played":" 17 ","Goals":" 3 ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"17 ","Position":" ","Name":" 10. Hoaglund, Lauren ","Games_Played":" 17 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"2 ","Position":" ","Name":" 11. Hunter, Anaya ","Games_Played":" 17 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"00 ","Position":" GK ","Name":" 12. Marino, McKenna ","Games_Played":" 16 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"1 ","Position":" M ","Name":" 13. Martin, Maddison ","Games_Played":" 16 ","Goals":" 2 ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"24 ","Position":" GK ","Name":" 14. Mathis, Abigail ","Games_Played":" 8 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"10 ","Position":" ","Name":" 15. McFadden, Logan ","Games_Played":" 17 ","Goals":" 1 ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"9 ","Position":" M ","Name":" 16. McFall, Aubrie ","Games_Played":" 17 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":" ","Position":" ","Name":" 17. McGowan, Anna ","Games_Played":" - ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"23 ","Position":" M ","Name":" 18. Moody, Hannah ","Games_Played":" 10 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"20 ","Position":" ","Name":" 19. O'Hare, Margaret ","Games_Played":" 10 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"6 ","Position":" ","Name":" 20. Poe, Ann ","Games_Played":" - ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":" ","Position":" ","Name":" 21. Schoenrock, Eve ","Games_Played":" 6 ","Goals":" 2 ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"8 ","Position":" ","Name":" 22. Tessmann, Ashley ","Games_Played":" 17 ","Goals":" 5 ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"5 ","Position":" ","Name":" 23. Vega, Vanessa ","Games_Played":" 14 ","Goals":" - ","Assists":" - ","Yellow_Cards":" - ","Red_Cards":" -"},{"Jersey_Number":"19 ","Position":" ","Name":" 24. Young, Alyssa ","Games_Played":" 15 ","Goals":" 3 ","Assists":" 1 ","Yellow_Cards":" - ","Red_Cards":" -"}]

 

 

 

This being the correct format i need in order to display my results on the application.

[Jacques1]

I'm talking about the braces right after the $roster[] = $row. They don't do anything, because they're not attached to any control structure. Remove them.

[joshmrtin9]

They are certainly doing something it seems.

I removed them and this is now my results:

 

{"Jersey_Number":"19 ","Position":" ","Name":" 24. Young, Alyssa ","Games_Played":" 15 ","Goals":" 3 ","Assists":" 1 ","Yellow_Cards":" - ","Red_Cards":" -"}

 

 

 

I only get 1 query in return and this one is not in the correct format as it is when i have $roster[] = $row

[bsmither]

May I suggest:

WAS:
while ($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC))
$roster[] = $row;
{
echo json_encode($roster);
}
 
Now:
while ($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC))
{
$roster[] = $row;
}
echo json_encode($roster);

[Jacques1]

Or simply

while ($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC))
	$roster[] = $row;

echo json_encode($roster);

[joshmrtin9]

Thanks for all the help i have it generating the correct output.

[QuickOldCar]

 

Or simply

while ($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC))
	$roster[] = $row;

echo json_encode($roster);

 

I like bsmither's way with curly braces, helps keep coding consistent and never forget them when needed.

My opinion of course.

 

 

 

while ($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC))
{
$roster[] = $row;
}
echo json_encode($roster);