How in symfony2 put query to json data?

Question

I have request:

SELECT Bank_ID, Status, COUNT(Bank_ID) FROM int_client_bank WHERE status = 30 or status = 50 or status = 35 or status = 37 GROUP BY Bank_ID, Status;

And see data:

"Bank_ID"   "Status"    "COUNT(Bank_ID)"
"1"         "30"        "772"
"1"         "35"        "58"
"1"         "50"        "151"
"2"         "30"        "124"
"2"         "35"        "27"
"2"         "50"        "25"
"3"         "30"        "227"
"3"         "35"        "16"
"3"         "37"        "1"
"3"         "50"        "143"
"4"         "30"        "337"
"4"         "35"        "23"
"4"         "37"        "1"
"4"         "50"        "98"
"5"         "30"        "72"
"5"         "35"        "7"
"5"         "50"        "9"
"6"         "30"        "113"
"6"         "35"        "3"
"6"         "50"        "68"
"7"         "30"        "16"
"7"         "50"        "10"
"8"         "30"        "13"
"8"         "35"        "1"
"8"         "50"        "6"
"9"         "30"        "16"
"9"         "35"        "2"
"9"         "50"        "6"
"10"        "30"        "4"
"10"        "35"        "2"
"11"        "30"        "2"
"11"        "50"        "2"
"12"        "30"        "4"
"12"        "35"        "1"
"12"        "50"        "1"
"13"        "30"        "3"
"13"        "50"        "2"
"14"        "30"        "5"
"15"        "30"        "1"
"15"        "50"        "1"
"16"        "30"        "1"
"17"        "30"        "1"
"18"        "30"        "2"

How i can put this in symfony to make JsonResponse?:

return new JsonResponse(array('data' => $result, 'success' => true));

I need data like:

{
    "data":[
        {"Bank_Id":"1","Status":"30","Count":"772"},
        {"Bank_Id":"1","Status":"35","Count":"58"},
        ...
    ],
    "success":true
}

Numeric php arrays turn to [] in JSON. associative arrays turn to {} in JSON. You dont show us HOW the data is rendered. Besides why don't you use an entity? — Frank B
– Frank B, Commented Sep 3, 2015 at 17:29

Timmetje · Accepted Answer · 2015-09-03 16:16:03Z

1

It's not very clear what you are asking but my guess is to make symfony make a JsonResponse based on your data which is done like this:

use Symfony\Component\HttpFoundation\JsonResponse;
$em = $this->getDoctrine()->getManager();
$query = $em->createQuery('SELECT Bank_ID, Status, COUNT(Bank_ID) FROM int_client_bank WHERE status = 30 or status = 50 or status = 35 or status = 37 GROUP BY Bank_ID, Status');

$bankResult = $query->getResult();
$response = new JsonResponse();
$response->setData(array(
    'data'    => $bankResult,
    'success' => true
));

edited Sep 3, 2015 at 16:16

answered Sep 3, 2015 at 15:50

Timmetje

7,70521 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dariusz Rup Over a year ago

but how i can make doctrine query? to put it in json $bankresult?

Timmetje Over a year ago

$bankResult isn't JSON``JsonResponse()` automatically json encodes your array data and adds an content-type header. So just make sure $bankResult contains your dataset / query response. Added some Doctrine to the example.

Dariusz Rup Over a year ago

Tim Dev i have error: [Semantical Error] line 0, col 44 near 'int_client_bank': Error: Class 'int_client_bank' is not defined. Can u change this query request? $em = $this->getDoctrine()->getManager()->getRepository('OmnisoftIntegBundle:IntClientBank')->getQuery .... ?

Dariusz Rup Over a year ago

If i try change query to SELECT Bank_ID, Status, COUNT(Bank_ID) FROM OmnisoftIntegBundle:IntClientBank WHERE status = 30 or status = 50 or status = 35 or status = 37 GROUP BY Bank_ID, Status [2/2] QueryException: [Syntax Error] line 0, col 84: Error: Expected end of string, got 'status'

Timmetje Over a year ago

That's a simple error in your query, I don't know your DB. so any problems with your query and creating a simple data-array are out of context of your above question. Please read the manual of Doctrine - working with Data

Praveesh · Accepted Answer · 2015-09-03 16:04:50Z

0

You would need to json encode the array and then send it as a json response.

$jsonArray = array(
            'data' => $result,
       'success' => true,
        );

        $response = new Response(json_encode($jsonArray));
        $response->headers->set('Content-Type', 'application/json; charset=utf-8');

        return $response;

edited Sep 3, 2015 at 16:04

answered Sep 3, 2015 at 15:51

Praveesh

1,3371 gold badge10 silver badges25 bronze badges

1 Comment

Frank B Over a year ago

use the JsonResponse object and you dont have to manually set the headers.

Collectives™ on Stack Overflow

How in symfony2 put query to json data?

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related