DB query returns only first matching result

Question

// Get all categories
$query = "SELECT name FROM bolt_taxonomy WHERE taxonomytype = :taxonomytype AND contenttype = :contenttype";
$map = array(
    ':taxonomytype'  => 'categories',
    ':contenttype' => 'news',
);
$categories = $this->app['db']->fetchAssoc($query, $map);

$response = $this->app->json(array('categories' => $categories));
return $response;

Returns:

{
    "categories": {
        "name": "life"
    }
}

Which is just the first entry that matches the above condition on the bolt_taxonomy table. How can I get it to return the whole list of categories?

@Ranjith yes, there are around 20 records that satisfy the conditions. — babbaggeii, Dec 23 '14 at 12:59
@babbaggeii Looking at this quick i would try `while($response = $this->app->json(array('categories'=>$categories))) { return $response;}` — Jim, Dec 23 '14 at 13:01

score 1 · Answer 1 · answered Dec 23 '14 at 13:15

This is now solved by using fetchAll:

// Get the category
    $query = "SELECT name FROM bolt_taxonomy WHERE taxonomytype = 'categories' AND contenttype = 'news'";

    $categories = $this->app['db']->query($query);
    $result = $categories->fetchAll();

    $response = $this->app->json(array('categories' => $result));
    return $response;

Ranjith · Answer 2 · 2014-12-23T13:08:48.300

0

You need to populate using while or foreach loop.

$categories = $this->app['db']->fetchAssoc($query, $map);

foreach($categories as $category) {
   $result[] = $category;
}
$response = $this->app->json($result);
echo $response;
return $response;

edited Dec 23 '14 at 13:08

answered Dec 23 '14 at 13:03

Ranjith

2,779
3
22
41

That just returns the first result again `{ "categories": "life" }` – babbaggeii Dec 23 '14 at 13:08
Sorry. Check my updated answer with `$result` to `$result[]` array. – Ranjith Dec 23 '14 at 13:09
If nothing matches, you would end up with `$result` being undefined. – Rob Baillie Dec 23 '14 at 13:30

DB query returns only first matching result

2 Answers2