Symfony2 MySql: Fetch array and get JSON array without column names

Question

I am trying to figure out how to get JSON array with following format

  [
     ["2016-02-26","5190","1253","425","123"],
     ["2016-02-27","5209","1114","521","214"],
     ["2016-02-28","5142","1425","412","156"],
     ["2016-02-29","5523","1365","632","198"],
     ["2016-03-01","5125","1452","523","152"],
     ["2016-03-02","5000","1245","741","286"]
   ]

But currently I am getting

  [{"date":"2016-02-26","visitors":"5190","registered":"1253","downloaded":"425","sticky_activity":"123"},
   {"date":"2016-02-27","visitors":"5209","registered":"1114","downloaded":"521","sticky_activity":"214"},
   {"date":"2016-02-28","visitors":"5142","registered":"1425","downloaded":"412","sticky_activity":"156"},{"date":"2016-02-29","visitors":"5523","registered":"1365","downloaded":"632","sticky_activity":"198"},
   {"date":"2016-03-01","visitors":"5125","registered":"1452","downloaded":"523","sticky_activity":"152"},
   {"date":"2016-03-02","visitors":"5000","registered":"1245","downloaded":"741","sticky_activity":"286"}]

Here is my function, if it's gonna help

  public function dataAction(Request $request){
    $em = $this->getDoctrine()->getEntityManager();
    $connection = $em->getConnection();

    $sqlQuery = "SELECT DATE_FORMAT(date, \"%Y-%m-%d\") as date,visitors, registered, downloaded, sticky_activity
                  FROM engagement";

    $statement = $connection->prepare($sqlQuery);

    $statement->execute();
    $queryResult = $statement->fetchAll();

    return new JsonResponse($queryResult);
}

i guess GROUP_CONCAT() is what you are looking for, unfortunatly i cant go any further — john Smith
– john Smith, Commented Mar 3, 2016 at 14:33
@malcolm no, it didn't help. I am still getting column names in array — user4666065
– user4666065, Commented Mar 3, 2016 at 14:33

Zephyr · Accepted Answer · 2016-03-03 15:07:57Z

1

Return this:

return new JsonResponse(array_map('array_values', $queryResult));

By the way you should also avoid direct SQL calls in favor of DQL or query builders. These are good practices while using Doctrine. Its goal is to avoid SQL as much as possible.

edited Mar 3, 2016 at 15:07

answered Mar 3, 2016 at 15:01

Zephyr

1,59812 silver badges22 bronze badges

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

1 Comment

user4666065 Over a year ago

Yeah, I would use DQL, but 'engagement' is not a class, it's just a table

Collectives™ on Stack Overflow

Symfony2 MySql: Fetch array and get JSON array without column names

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related