getting rows from table with zend db

Question

I have basically the following table,

Categories

id   name        categories_id_categories
1    Clothes     null
2    Shirts      1
3    Pants       1
4    Electronics null
5    Tv          4

The table stores categories and sub-categories, if the categories_id_categories is null theyre main categories else theyre sub-categories of the category with that id. I want to show this on a page so I created this functions on my Categories model:

public function getAllCategories()
{
    $select = $this->select()
                   ->where('categories_id_categories IS NULL');

    return $this->fetchAll($select);
}

public function getAllSubCategories()
{
    $select = $this->select()
                   ->where('categories_id_categories IS NOT NULL');

    return $this->fetchAll($select);
}

And on my controller:

    $categories = new Model_DbTable_Categories();

    $categoryList = $categories->getAllCategories();

    $categoriesAll = array();

    foreach ($categoryList->toArray() as $category) {
        $subCategories = $categories->getSubCategoriesByCategory($category['id']);
        $category['sub_categories'] = $subCategories->toArray();
        $categoriesAll[] = $category;
    }

    $this->view->categoryList = $categoriesAll;

So categoryList is an array with all the categories and the key sub_categories is another array with all sub-categories. This works but I was wondering if there was a way to do it using objects instead of an array, and maybe using just one query instead of 2?

If I select all from the table I'd get categories and sub-categories but then I'd have to move some logic into the view to select the sub-categories I believe.

Thanks in advance!

bensiu · Accepted Answer · 2011-01-12T14:01:36.967

2

Just put $id to getAllSubcategories and create getSubCategories in your model like this:

public function geSubCategories($id = null) 
{
    $select = $this->select();
    if ( $id == null ) {
      $select->where('categories_id_categories IS NOT NULL');
    }
    else {
      $select->where('id = ?', $id);
    }

    return $this->fetchAll($select);
}

edited Jan 12 '11 at 14:01

answered Dec 26 '10 at 18:14

bensiu

24,660
56
77
117

score 0 · Answer 2 · answered Jul 20 '15 at 15:00

$sql = "SELECT * FROM TABLE_NAME WHERE ID = 1";
$rows = $db->fetchAll($sql);

//One row return array
echo $rows[0]['field_name'];

http://framework.zend.com/manual/1.12/en/zend.db.table.row.html

http://framework.zend.com/manual/1.12/en/zend.db.table.rowset.html

getting rows from table with zend db

2 Answers2